-- Modern Tuckshop & Produce POS schema
-- New installations can import this file after mavela_db.sql.
-- Existing installations are upgraded automatically by pos_core.php when a POS page is opened.

CREATE TABLE IF NOT EXISTS `tp_users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `full_name` VARCHAR(120) NOT NULL,
  `username` VARCHAR(80) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `workspace` ENUM('tuckshop','produce') NOT NULL,
  `role` ENUM('manager','cashier') NOT NULL DEFAULT 'manager',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_tp_users_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tp_products` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `workspace` ENUM('tuckshop','produce') NOT NULL,
  `category` VARCHAR(80) NOT NULL,
  `product_name` VARCHAR(120) NOT NULL,
  `sku` VARCHAR(64) DEFAULT NULL,
  `barcode` VARCHAR(80) DEFAULT NULL,
  `unit_label` VARCHAR(40) NOT NULL DEFAULT 'unit',
  `buying_price` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `selling_price` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `stock_qty` DECIMAL(12,3) NOT NULL DEFAULT 0,
  `track_stock` TINYINT(1) NOT NULL DEFAULT 1,
  `allow_fractional` TINYINT(1) NOT NULL DEFAULT 0,
  `reorder_level` DECIMAL(12,3) NOT NULL DEFAULT 5,
  `supplier_name` VARCHAR(120) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tp_products_workspace` (`workspace`),
  KEY `idx_tp_products_category` (`category`),
  KEY `idx_tp_products_workspace_sku` (`workspace`,`sku`),
  KEY `idx_tp_products_workspace_barcode` (`workspace`,`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tp_sales` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `sale_ref` VARCHAR(48) DEFAULT NULL,
  `transaction_id` BIGINT DEFAULT NULL,
  `workspace` ENUM('tuckshop','produce') NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` DECIMAL(12,3) NOT NULL,
  `unit_price` DECIMAL(12,2) NOT NULL,
  `total_amount` DECIMAL(12,2) NOT NULL,
  `payment_method` VARCHAR(30) NOT NULL DEFAULT 'cash',
  `amount_tendered` DECIMAL(12,2) DEFAULT NULL,
  `currency_code` VARCHAR(8) NOT NULL DEFAULT 'USD',
  `fx_rate_to_usd` DECIMAL(18,8) NOT NULL DEFAULT 1,
  `line_subtotal_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `line_discount_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `line_tax_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `total_amount_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `amount_tendered_usd` DECIMAL(12,2) DEFAULT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'completed',
  `voided_at` DATETIME DEFAULT NULL,
  `voided_by` INT DEFAULT NULL,
  `void_reason` VARCHAR(255) DEFAULT NULL,
  `sold_by` INT NOT NULL,
  `notes` VARCHAR(255) DEFAULT NULL,
  `sold_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tp_sales_workspace_date` (`workspace`,`sold_at`),
  KEY `idx_tp_sales_sale_ref` (`sale_ref`),
  KEY `idx_tp_sales_transaction` (`transaction_id`),
  KEY `idx_tp_sales_status` (`workspace`,`status`,`sold_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tp_transactions` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `workspace` ENUM('tuckshop','produce') NOT NULL,
  `sale_ref` VARCHAR(48) NOT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'completed',
  `customer_name` VARCHAR(120) DEFAULT NULL,
  `customer_phone` VARCHAR(40) DEFAULT NULL,
  `currency_code` VARCHAR(8) NOT NULL DEFAULT 'USD',
  `fx_rate_to_usd` DECIMAL(18,8) NOT NULL DEFAULT 1,
  `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `discount_type` VARCHAR(12) NOT NULL DEFAULT 'none',
  `discount_value` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `subtotal_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `discount_amount_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `tax_amount_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `total_amount_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `payment_method` VARCHAR(30) NOT NULL DEFAULT 'cash',
  `amount_tendered` DECIMAL(12,2) DEFAULT NULL,
  `change_due` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `payment_reference` VARCHAR(100) DEFAULT NULL,
  `notes` VARCHAR(500) DEFAULT NULL,
  `sold_by` INT NOT NULL,
  `shift_id` BIGINT DEFAULT NULL,
  `sold_at` DATETIME NOT NULL,
  `voided_at` DATETIME DEFAULT NULL,
  `voided_by` INT DEFAULT NULL,
  `void_reason` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_tp_transactions_sale_ref` (`sale_ref`),
  KEY `idx_tp_tx_workspace_date` (`workspace`,`sold_at`),
  KEY `idx_tp_tx_workspace_status` (`workspace`,`status`,`sold_at`),
  KEY `idx_tp_tx_sold_by` (`sold_by`,`sold_at`), KEY `idx_tp_tx_shift` (`shift_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tp_sale_payments` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `transaction_id` BIGINT NOT NULL,
  `payment_method` VARCHAR(30) NOT NULL, `currency_code` VARCHAR(8) NOT NULL DEFAULT 'USD',
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0, `amount_usd` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `reference_no` VARCHAR(100) DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), KEY `idx_tp_payment_tx` (`transaction_id`), KEY `idx_tp_payment_method` (`payment_method`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tp_held_orders` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `workspace` ENUM('tuckshop','produce') NOT NULL,
  `hold_ref` VARCHAR(48) NOT NULL, `cart_json` LONGTEXT NOT NULL,
  `customer_name` VARCHAR(120) DEFAULT NULL, `customer_phone` VARCHAR(40) DEFAULT NULL,
  `currency_code` VARCHAR(8) NOT NULL DEFAULT 'USD', `discount_type` VARCHAR(12) NOT NULL DEFAULT 'none',
  `discount_value` DECIMAL(12,2) NOT NULL DEFAULT 0, `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `notes` VARCHAR(500) DEFAULT NULL, `held_by` INT NOT NULL, `status` VARCHAR(20) NOT NULL DEFAULT 'active',
  `resumed_at` DATETIME DEFAULT NULL, `cancelled_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `uq_tp_hold_ref` (`hold_ref`), KEY `idx_tp_hold_workspace_status` (`workspace`,`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tp_stock_movements` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `workspace` ENUM('tuckshop','produce') NOT NULL,
  `product_id` INT NOT NULL, `movement_type` VARCHAR(30) NOT NULL,
  `quantity_change` DECIMAL(12,3) NOT NULL, `balance_after` DECIMAL(12,3) NOT NULL,
  `unit_cost_usd` DECIMAL(12,2) DEFAULT NULL, `reference_no` VARCHAR(80) DEFAULT NULL,
  `reason` VARCHAR(255) DEFAULT NULL, `created_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), KEY `idx_tp_stock_product_date` (`product_id`,`created_at`), KEY `idx_tp_stock_workspace_date` (`workspace`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `tp_shifts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `workspace` ENUM('tuckshop','produce') NOT NULL,
  `user_id` INT NOT NULL, `status` VARCHAR(16) NOT NULL DEFAULT 'open',
  `opening_float` DECIMAL(12,2) NOT NULL DEFAULT 0, `currency_code` VARCHAR(8) NOT NULL DEFAULT 'USD',
  `expected_cash` DECIMAL(12,2) DEFAULT NULL, `closing_cash` DECIMAL(12,2) DEFAULT NULL,
  `variance` DECIMAL(12,2) DEFAULT NULL, `opened_at` DATETIME NOT NULL, `closed_at` DATETIME DEFAULT NULL,
  `notes` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `idx_tp_shift_user_status` (`user_id`,`status`,`opened_at`), KEY `idx_tp_shift_workspace_date` (`workspace`,`opened_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
