-- Database triggers to ensure account_id is always present even if application logic misses it
-- This provides robust infrastructure-level RLS compliance

CREATE OR REPLACE FUNCTION sync_account_id() RETURNS TRIGGER AS $$
BEGIN
    -- Only attempt to sync if website_id is present and account_id is null
    IF NEW.account_id IS NULL AND NEW.website_id IS NOT NULL THEN
        SELECT account_id INTO NEW.account_id FROM websites WHERE website_id = NEW.website_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to tables that depend on website_id
DO $$
DECLARE
    t TEXT;
BEGIN
    FOR t IN SELECT unnest(ARRAY['ai_audience_fit_scores', 'pages', 'prompts', 'ai_visibility_scores', 'citations', 'visibility_gaps', 'notifications'])
    LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS trg_sync_account_id ON %I', t);
        EXECUTE format('CREATE TRIGGER trg_sync_account_id BEFORE INSERT ON %I FOR EACH ROW EXECUTE FUNCTION sync_account_id()', t);
    END LOOP;
END $$;
