-- Setup notification system for shopifydb database
-- This ensures taf_queue channel receives notifications when receipts are created/updated

-- Create or replace the notification function for sales_receipts only
CREATE OR REPLACE FUNCTION notify_sales_receipt()
RETURNS trigger AS $$
DECLARE
    notification_payload TEXT;
BEGIN
    -- Build minimal JSON payload with sales_receipt_id
    notification_payload := json_build_object(
        'entity', 'sales_receipts',
        'record_id', NEW.sales_receipt_id::text,
        'event_type', lower(TG_OP),
        'timestamp', CURRENT_TIMESTAMP
    )::text;
    
    -- Send the notification to taf_queue channel
    PERFORM pg_notify('taf_queue', notification_payload);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION notify_sales_receipt() IS 
'Sends sales_receipts notifications to taf_queue channel. Flow: PostgreSQL → pgListener → Redis → WebSocket → Frontend';

-- Drop existing trigger if it exists
DROP TRIGGER IF EXISTS trg_notify_sales_receipts ON sales_receipts;

-- Create trigger on sales_receipts table only
CREATE TRIGGER trg_notify_sales_receipts
AFTER INSERT OR UPDATE OR DELETE ON sales_receipts
FOR EACH ROW
EXECUTE FUNCTION notify_sales_receipt();

-- Verify the trigger was created
SELECT 
    'Trigger created: ' || tgname as status,
    tgenabled as enabled,
    'on table: ' || relname as table_name
FROM pg_trigger 
JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid
WHERE tgname = 'trg_notify_sales_receipts';

-- Test the notification (optional - uncomment to test)
-- SELECT pg_notify('taf_queue', '{"entity":"test","record_id":"1","event_type":"test","timestamp":"2024-12-24"}');
