-- =====================================================
-- TaxCore Integration for Case Management Module (Fixed)
-- =====================================================
-- This migration adds TaxCore functionality to the Case Management module
-- Adds case_id links to sales_receipts and lineitems_payments tables

-- Add case_id column to sales_receipts table if not exists (UUID type)
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'sales_receipts' AND column_name = 'case_id'
  ) THEN
    ALTER TABLE sales_receipts ADD COLUMN case_id UUID REFERENCES cases(case_id);
    CREATE INDEX idx_sales_receipts_case_id ON sales_receipts(case_id);
    COMMENT ON COLUMN sales_receipts.case_id IS 'Links TaxCore transaction to a case in Case Management';
  END IF;
END $$;

-- invoice_id already exists, just add comment if not present
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'sales_receipts' AND column_name = 'invoice_id'
  ) THEN
    COMMENT ON COLUMN sales_receipts.invoice_id IS 'Links TaxCore transaction to an invoice';
  END IF;
END $$;

-- Add case_id column to lineitems_payments table if not exists (UUID type)
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'lineitems_payments' AND column_name = 'case_id'
  ) THEN
    ALTER TABLE lineitems_payments ADD COLUMN case_id UUID REFERENCES cases(case_id);
    CREATE INDEX idx_lineitems_payments_case_id ON lineitems_payments(case_id);
    COMMENT ON COLUMN lineitems_payments.case_id IS 'Links line items to a case in Case Management';
  END IF;
END $$;

-- Add buyer_id column to sales_receipts if not exists (for customer TIN)
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'sales_receipts' AND column_name = 'buyer_id'
  ) THEN
    ALTER TABLE sales_receipts ADD COLUMN buyer_id TEXT;
    CREATE INDEX idx_sales_receipts_buyer_id ON sales_receipts(buyer_id);
    COMMENT ON COLUMN sales_receipts.buyer_id IS 'Customer TIN for TaxCore transactions';
  END IF;
END $$;

-- Add cashier_name and cashier_id if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'sales_receipts' AND column_name = 'cashier_name'
  ) THEN
    ALTER TABLE sales_receipts ADD COLUMN cashier_name TEXT;
    COMMENT ON COLUMN sales_receipts.cashier_name IS 'Name of cashier who processed the transaction';
  END IF;
  
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'sales_receipts' AND column_name = 'cashier_id'
  ) THEN
    ALTER TABLE sales_receipts ADD COLUMN cashier_id UUID;
    CREATE INDEX idx_sales_receipts_cashier_id ON sales_receipts(cashier_id);
    COMMENT ON COLUMN sales_receipts.cashier_id IS 'User ID of cashier who processed the transaction';
  END IF;
END $$;

-- Add customer_id to lineitems_payments if not exists
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'lineitems_payments' AND column_name = 'customer_id'
  ) THEN
    ALTER TABLE lineitems_payments ADD COLUMN customer_id UUID;
    CREATE INDEX idx_lineitems_payments_customer_id ON lineitems_payments(customer_id);
    COMMENT ON COLUMN lineitems_payments.customer_id IS 'Customer ID for line items';
  END IF;
END $$;
