-- Migration: Add pos_id column to transaction tables
-- Date: 2025-11-13
-- Purpose: Add pos_id column to sales_receipts and lineitems_payments tables
--          to enable simple POS-based transaction queries and reporting

-- Add pos_id column to sales_receipts table
ALTER TABLE sales_receipts 
ADD COLUMN IF NOT EXISTS pos_id VARCHAR(20);

-- Add pos_id column to lineitems_payments table  
ALTER TABLE lineitems_payments 
ADD COLUMN IF NOT EXISTS pos_id VARCHAR(20);

-- Create indexes for pos_id for efficient querying
CREATE INDEX IF NOT EXISTS idx_sales_receipts_pos_id ON sales_receipts(pos_id);
CREATE INDEX IF NOT EXISTS idx_lineitems_payments_pos_id ON lineitems_payments(pos_id);

-- Create composite index for pos_id + transaction_number for efficient counter queries
CREATE INDEX IF NOT EXISTS idx_sales_receipts_pos_transaction ON sales_receipts(pos_id, transaction_number);

-- Update existing records to set pos_id based on transaction_number format
-- Extract POS-01 from transaction numbers like "POS-01-20251113-0004"
UPDATE sales_receipts 
SET pos_id = CASE 
  WHEN transaction_number ~ '^POS-\d+-\d{8}-\d{4}$' THEN 
    split_part(transaction_number, '-', 1) || '-' || split_part(transaction_number, '-', 2)
  ELSE 'POS-01'  -- Default fallback
END
WHERE transaction_number IS NOT NULL AND pos_id IS NULL;

UPDATE lineitems_payments 
SET pos_id = CASE 
  WHEN transaction_number ~ '^POS-\d+-\d{8}-\d{4}$' THEN 
    split_part(transaction_number, '-', 1) || '-' || split_part(transaction_number, '-', 2)
  ELSE 'POS-01'  -- Default fallback
END
WHERE transaction_number IS NOT NULL AND pos_id IS NULL;

-- Add comments for documentation
COMMENT ON COLUMN sales_receipts.pos_id IS 'POS terminal identifier (e.g., POS-01, POS-02) for transaction grouping and reporting';
COMMENT ON COLUMN lineitems_payments.pos_id IS 'POS terminal identifier (e.g., POS-01, POS-02) for transaction grouping and reporting';