-- Order Payments Table
-- Tracks individual payments (partial or full) against an order.
-- Replaces the single amount_paid + final_payment_amount fields with a proper payment log.

CREATE TABLE IF NOT EXISTS `order_payments` (
  `payment_id` INT(11) NOT NULL AUTO_INCREMENT,
  `order_id` INT(11) NOT NULL,
  `branch_id` INT(11) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `payment_method` VARCHAR(40) NOT NULL DEFAULT 'cash',
  `notes` VARCHAR(255) DEFAULT NULL,
  `is_initial` TINYINT(1) NOT NULL DEFAULT 0,
  `recorded_by` INT(11) NOT NULL,
  `payment_date` DATETIME NOT NULL,
  PRIMARY KEY (`payment_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_branch_id` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migrate existing initial deposits from orders.amount_paid
INSERT INTO `order_payments` (`order_id`, `branch_id`, `amount`, `payment_method`, `notes`, `is_initial`, `recorded_by`, `payment_date`)
SELECT
    o.order_id,
    o.branch_id,
    o.amount_paid,
    COALESCE(NULLIF(o.payment_method, ''), 'cash'),
    'Initial deposit (migrated)',
    1,
    o.created_by,
    o.created_at
FROM `orders` o
WHERE o.amount_paid > 0
  AND NOT EXISTS (
      SELECT 1 FROM `order_payments` op
      WHERE op.order_id = o.order_id AND op.is_initial = 1
  );

-- Migrate final payments for completed orders where final_payment_amount was recorded separately
INSERT INTO `order_payments` (`order_id`, `branch_id`, `amount`, `payment_method`, `notes`, `is_initial`, `recorded_by`, `payment_date`)
SELECT
    o.order_id,
    o.branch_id,
    o.final_payment_amount,
    COALESCE(NULLIF(o.payment_method, ''), 'cash'),
    'Final payment (migrated)',
    0,
    COALESCE(o.completed_by, o.created_by),
    COALESCE(o.completed_at, o.created_at)
FROM `orders` o
WHERE o.status = 'completed'
  AND o.final_payment_amount > 0
  AND NOT EXISTS (
      SELECT 1 FROM `order_payments` op
      WHERE op.order_id = o.order_id AND op.is_initial = 0
  );
