-- Migration: Minimal notification payload for sales_receipts
-- Date: 2025-12-10
-- Description: Replace notify_notification_queue() to send only primary key instead of full row
--              to avoid PostgreSQL's 8KB NOTIFY payload limit

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

-- Create or replace the notification function to send minimal payload
CREATE OR REPLACE FUNCTION notify_notification_queue()
RETURNS trigger AS $$
DECLARE
    notification_payload TEXT;
    primary_key TEXT;
BEGIN
    -- Determine the primary key based on the table
    CASE TG_TABLE_NAME
        WHEN 'sales_receipts' THEN
            primary_key := NEW.sales_receipt_id::text;
        WHEN 'notification_queue' THEN
            primary_key := NEW.id::text;
        ELSE
            -- Try common primary key column names
            primary_key := COALESCE(
                NEW.id::text,
                NEW.uuid::text,
                TG_TABLE_NAME || '_id'
            );
    END CASE;

    -- Build minimal JSON payload with only essential metadata
    notification_payload := json_build_object(
        'entity', TG_TABLE_NAME,
        'record_id', primary_key,
        '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;

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

-- Verify the trigger was created
SELECT 
    'Trigger created: ' || tgname as status,
    tgenabled as enabled
FROM pg_trigger 
WHERE tgrelid = 'sales_receipts'::regclass 
AND tgname = 'trg_notify_sales_receipts';

COMMENT ON FUNCTION notify_notification_queue() IS 
'Sends minimal notification payload (primary key only) to avoid 8KB NOTIFY limit. Flow: PostgreSQL → pgListener → Redis → Node.js → Frontend/Print Service';
