-- Migration: Add transaction counters table and transaction_number fields
-- Date: 2024-11-13
-- Description: Creates transaction_counters table for daily sequential numbering per POS terminal
--              and adds transaction_number field to sales_receipts and lineitems_payments tables

-- Create transaction_counters table for daily sequential numbering per POS terminal
CREATE TABLE IF NOT EXISTS transaction_counters (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
    terminal_id VARCHAR(50) NOT NULL,
    date INTEGER NOT NULL, -- YYYYMMDD format (e.g., 20251113) for better performance
    counter INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Ensure one counter per org/terminal/date combination
    UNIQUE(org_id, terminal_id, date)
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_transaction_counters_org_terminal_date 
    ON transaction_counters(org_id, terminal_id, date);
CREATE INDEX IF NOT EXISTS idx_transaction_counters_date 
    ON transaction_counters(date);

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

-- Create index for transaction_number lookups in sales_receipts
CREATE INDEX IF NOT EXISTS idx_sales_receipts_transaction_number 
    ON sales_receipts(transaction_number);

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

-- Create index for transaction_number lookups in lineitems_payments
CREATE INDEX IF NOT EXISTS idx_lineitems_payments_transaction_number 
    ON lineitems_payments(transaction_number);

-- Add comments for documentation
COMMENT ON TABLE transaction_counters IS 'Daily sequential transaction counters per POS terminal';
COMMENT ON COLUMN transaction_counters.date IS 'Date in YYYYMMDD integer format for performance (e.g., 20251113)';
COMMENT ON COLUMN transaction_counters.terminal_id IS 'POS terminal identifier';
COMMENT ON COLUMN transaction_counters.counter IS 'Sequential counter for the day, resets daily per terminal';

COMMENT ON COLUMN sales_receipts.transaction_number IS 'Sequential transaction number per terminal per day';
COMMENT ON COLUMN lineitems_payments.transaction_number IS 'Sequential transaction number per terminal per day';

-- Optional: Update trigger for updated_at timestamp
CREATE OR REPLACE FUNCTION update_transaction_counters_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_transaction_counters_updated_at ON transaction_counters;
CREATE TRIGGER trigger_update_transaction_counters_updated_at
    BEFORE UPDATE ON transaction_counters
    FOR EACH ROW
    EXECUTE FUNCTION update_transaction_counters_updated_at();