-- Migration: Add journal_entry_id to sales_receipts table
-- Purpose: Link POS sales to their journal entries (for daily summaries)
-- Date: 2026-01-24
-- Note: This links to the DAILY summary journal entry, not individual sale entries

BEGIN;

-- Add journal_entry_id column to sales_receipts (for daily summary linkage)
ALTER TABLE sales_receipts
ADD COLUMN IF NOT EXISTS journal_entry_id UUID,
ADD CONSTRAINT sales_receipts_journal_entry_id_fkey
    FOREIGN KEY (journal_entry_id)
    REFERENCES gl_journal_entries(id)
    ON DELETE SET NULL;

-- Create index for performance
CREATE INDEX IF NOT EXISTS idx_sales_receipts_journal
ON sales_receipts(journal_entry_id);

-- Add comment
COMMENT ON COLUMN sales_receipts.journal_entry_id IS 
'Links to the daily summary journal entry in gl_journal_entries. Updated by cron job after daily aggregation.';

COMMIT;

-- Post-migration notice
DO $$
BEGIN
    RAISE NOTICE '✓ Added journal_entry_id column to sales_receipts';
    RAISE NOTICE '  Note: This links to DAILY summary journal entries, not individual transactions';
    RAISE NOTICE '  Updated by: scripts/cron_daily_pos_journal.php';
END $$;
