-- Migration: Split payments into main payment and payment items
-- Date: 2025-09-06
-- Description: Refactor payments table to support split payments with multiple methods

-- Add total_amount column to payments table if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name = 'payments'
                   AND column_name = 'total_amount') THEN
        ALTER TABLE payments ADD COLUMN total_amount DECIMAL(10,2);
    END IF;
END $$;

-- Create payment_items table for individual payment method line items
CREATE TABLE IF NOT EXISTS payment_items (
    payment_item_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    payment_id uuid NOT NULL REFERENCES payments(payment_id) ON DELETE CASCADE,
    method text NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    customer_id uuid,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt timestamptz NOT NULL DEFAULT now()
);

-- Migrate existing payment data to new structure
-- This will create payment_items for existing payments
INSERT INTO payment_items (payment_id, method, amount, customer_id, updatedAt)
SELECT
    payment_id,
    COALESCE(payment_method, 'Unknown') as method,
    COALESCE(payment_amount, 0) as amount,
    NULL as customer_id,
    updatedAt
FROM payments
WHERE payment_id NOT IN (SELECT DISTINCT payment_id FROM payment_items);

-- Update total_amount for existing payments
UPDATE payments
SET total_amount = (
    SELECT SUM(amount)
    FROM payment_items
    WHERE payment_items.payment_id = payments.payment_id
)
WHERE total_amount IS NULL OR total_amount = 0;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS idx_payment_items_payment_id ON payment_items(payment_id);
CREATE INDEX IF NOT EXISTS idx_payment_items_method ON payment_items(method);
CREATE INDEX IF NOT EXISTS idx_payments_total_amount ON payments(total_amount);
CREATE INDEX IF NOT EXISTS idx_payments_reference ON payments(reference_table, reference_id);

-- Add comments
COMMENT ON TABLE payment_items IS 'Individual payment method line items for split payments';
COMMENT ON COLUMN payments.total_amount IS 'Total amount of all payment methods combined';
COMMENT ON COLUMN payment_items.method IS 'Payment method (Cash, Credit, MPaisa, etc.)';
