-- =====================================================
-- Sales Module Migration - Phase 1 COMPLETE (Combined)
-- বধুবরণ জুয়েলার্স ERP
-- Date: 2026-05-16
-- Creates 4 missing tables (sales already exists):
--   - sale_payments
--   - sale_exchanges  
--   - sale_invoice_sequence
--   - sale_items
-- Safe to re-run (CREATE TABLE IF NOT EXISTS)
-- =====================================================

-- ----------------------------------------------------
-- 1) Multi-payment Split (Cash + bKash + Nagad + Bank)
-- ----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale_payments` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sale_id` INT(11) NOT NULL,
  `cash_account_id` INT(11) NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `reference_no` VARCHAR(100) NULL COMMENT 'bKash/Nagad txn ID etc.',
  `cash_transaction_id` INT(11) NULL COMMENT 'FK to cash_transactions.id',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sale_id` (`sale_id`),
  KEY `idx_account` (`cash_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ----------------------------------------------------
-- 2) Old-gold Exchange Items per Sale
-- ----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale_exchanges` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sale_id` INT(11) NOT NULL,
  `item_description` VARCHAR(200) NULL,
  `karat` VARCHAR(20) NOT NULL,
  `purity_percent` DECIMAL(6,3) NOT NULL,
  `gross_weight_gram` DECIMAL(10,4) NOT NULL,
  `pure_gold_gram` DECIMAL(10,4) NOT NULL,
  `rate_per_gram` DECIMAL(15,2) NOT NULL,
  `exchange_value` DECIMAL(15,2) NOT NULL,
  `goes_to_old_gold_stock` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sale_id` (`sale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ----------------------------------------------------
-- 3) Invoice-number Sequence (per year)
-- ----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale_invoice_sequence` (
  `year` INT(4) NOT NULL,
  `last_number` INT(11) DEFAULT 0,
  PRIMARY KEY (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ----------------------------------------------------
-- 4) Sale Items (Unified for Quick Sale + Full Sales)
-- ----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale_items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sale_id` INT(11) NULL COMMENT 'NULL allowed for Quick Sale (no master invoice)',

  -- Source tracking
  `source_type` ENUM('barcode','new_stock','custom') NOT NULL DEFAULT 'barcode',
  `barcode_stock_id` INT(11) NULL,
  `new_stock_id` INT(11) NULL,

  -- Item description (snapshot at sale time)
  `product_name` VARCHAR(200) NOT NULL,
  `category` VARCHAR(100) NULL,
  `barcode` VARCHAR(100) NULL,

  -- Gold details
  `karat` VARCHAR(20) NULL,
  `purity_percent` DECIMAL(6,3) NULL,
  `gross_weight_gram` DECIMAL(10,4) DEFAULT 0.0000,
  `stone_weight_gram` DECIMAL(10,4) DEFAULT 0.0000,
  `net_weight_gram` DECIMAL(10,4) DEFAULT 0.0000,

  -- Pricing breakdown
  `rate_per_gram` DECIMAL(15,2) DEFAULT 0.00,
  `gold_value` DECIMAL(15,2) DEFAULT 0.00,
  `making_charge` DECIMAL(15,2) DEFAULT 0.00,
  `stone_value` DECIMAL(15,2) DEFAULT 0.00,
  `item_total` DECIMAL(15,2) DEFAULT 0.00,

  `qty` INT(11) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_sale_id` (`sale_id`),
  KEY `idx_source` (`source_type`),
  KEY `idx_barcode_stock` (`barcode_stock_id`),
  KEY `idx_new_stock` (`new_stock_id`),
  KEY `idx_barcode` (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ----------------------------------------------------
-- VERIFICATION
-- ----------------------------------------------------
SHOW TABLES LIKE 'sale%';