-- Phase 05: Sales invoices payment modes (cash / credit / partial) linked to cashboxes.
SET @db := DATABASE();

SET @has_payment_mode := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='payment_mode'
);
SET @sql := IF(@has_payment_mode=0,
  "ALTER TABLE `sales_invoices` ADD COLUMN `payment_mode` VARCHAR(12) NOT NULL DEFAULT 'credit' AFTER `notes`",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_paid_amount := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='paid_amount'
);
SET @sql := IF(@has_paid_amount=0,
  "ALTER TABLE `sales_invoices` ADD COLUMN `paid_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER `payment_mode`",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_due_amount := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='due_amount'
);
SET @sql := IF(@has_due_amount=0,
  "ALTER TABLE `sales_invoices` ADD COLUMN `due_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER `paid_amount`",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_cashbox_id := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='cashbox_id'
);
SET @sql := IF(@has_cashbox_id=0,
  "ALTER TABLE `sales_invoices` ADD COLUMN `cashbox_id` BIGINT UNSIGNED NULL AFTER `due_amount`",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_collection_note := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND COLUMN_NAME='collection_note'
);
SET @sql := IF(@has_collection_note=0,
  "ALTER TABLE `sales_invoices` ADD COLUMN `collection_note` VARCHAR(255) NULL AFTER `cashbox_id`",
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_idx_cashbox := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='sales_invoices' AND INDEX_NAME='idx_si_cashbox'
);
SET @sql := IF(@has_idx_cashbox=0,
  'ALTER TABLE `sales_invoices` ADD INDEX `idx_si_cashbox` (`cashbox_id`)',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE sales_invoices
SET payment_mode = CASE
    WHEN COALESCE(paid_amount,0) >= COALESCE(grand_total,total,0) AND COALESCE(grand_total,total,0) > 0 THEN 'cash'
    WHEN COALESCE(paid_amount,0) > 0 THEN 'partial'
    ELSE 'credit'
  END
WHERE payment_mode IS NULL OR payment_mode='';

UPDATE sales_invoices
SET paid_amount = CASE
    WHEN payment_mode='cash' THEN COALESCE(grand_total,total,0)
    WHEN payment_mode='credit' THEN 0
    ELSE LEAST(COALESCE(paid_amount,0), COALESCE(grand_total,total,0))
  END,
  due_amount = GREATEST(COALESCE(grand_total,total,0) - CASE
    WHEN payment_mode='cash' THEN COALESCE(grand_total,total,0)
    WHEN payment_mode='credit' THEN 0
    ELSE LEAST(COALESCE(paid_amount,0), COALESCE(grand_total,total,0))
  END, 0);