-- Add Campay API token to settings table
ALTER TABLE `settings` ADD COLUMN IF NOT EXISTS `campay_api_token` TEXT AFTER `sms_gateway_token`;

-- Update payments table to support invoice payments with Campay tracking
ALTER TABLE `payments` 
  ADD COLUMN IF NOT EXISTS `invoice_id` INT AFTER `related_id`,
  ADD COLUMN IF NOT EXISTS `campay_reference` VARCHAR(255) AFTER `status`,
  ADD COLUMN IF NOT EXISTS `phone_number` VARCHAR(20) AFTER `campay_reference`,
  ADD COLUMN IF NOT EXISTS `operator` VARCHAR(50) AFTER `phone_number`,
  ADD INDEX IF NOT EXISTS `invoice_id` (`invoice_id`),
  ADD INDEX IF NOT EXISTS `campay_reference` (`campay_reference`);

-- Add foreign key constraint separately (if column doesn't already have it)
ALTER TABLE `payments` 
  ADD CONSTRAINT `fk_payments_invoice_id` 
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE;

-- Ensure payment_method enum includes Mobile Money
ALTER TABLE `payments` MODIFY COLUMN `payment_method` 
  ENUM('Cash', 'Bank Transfer', 'Check', 'Cheque', 'Credit Card', 'Mobile Money') 
  DEFAULT 'Bank Transfer';

-- Create invoice_payments view for easier querying
CREATE OR REPLACE VIEW `invoice_payments` AS
SELECT 
    p.id,
    p.invoice_id,
    p.amount,
    p.currency,
    p.payment_method,
    p.status,
    p.payment_date,
    p.campay_reference,
    p.phone_number,
    p.operator,
    p.description,
    i.invoice_number,
    i.total_amount,
    (i.total_amount - COALESCE(SUM(CASE WHEN p.status IN ('Confirmed', 'Cleared') THEN p.amount ELSE 0 END), 0)) as remaining_balance
FROM `payments` p
LEFT JOIN `invoices` i ON p.invoice_id = i.id
GROUP BY p.id;
