-- Realtime schema
CREATE SCHEMA IF NOT EXISTS madbase_realtime;

-- Generic Trigger Function
CREATE OR REPLACE FUNCTION madbase_realtime.broadcast_changes()
RETURNS trigger AS $$
DECLARE
    payload jsonb;
    topic text;
BEGIN
    -- Construct payload
    payload = jsonb_build_object(
        'schema', TG_TABLE_SCHEMA,
        'table', TG_TABLE_NAME,
        'type', TG_OP,
        'timestamp', now()
    );

    IF (TG_OP = 'INSERT') THEN
        payload = payload || jsonb_build_object('record', row_to_json(NEW)::jsonb);
    ELSIF (TG_OP = 'UPDATE') THEN
        payload = payload || jsonb_build_object(
            'record', row_to_json(NEW)::jsonb,
            'old_record', row_to_json(OLD)::jsonb
        );
    ELSIF (TG_OP = 'DELETE') THEN
        payload = payload || jsonb_build_object('old_record', row_to_json(OLD)::jsonb);
    END IF;

    -- Send notification
    -- Payload limit is 8000 bytes. Larger payloads will fail or need truncation.
    -- For MVP, we assume it fits.
    PERFORM pg_notify('madbase_realtime', payload::text);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Example: Enable for public.users (if it exists)
-- DO $$
-- BEGIN
--     IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'users') THEN
--         CREATE TRIGGER realtime_users_changes
--         AFTER INSERT OR UPDATE OR DELETE ON public.users
--         FOR EACH ROW EXECUTE FUNCTION madbase_realtime.broadcast_changes();
--     END IF;
-- END
-- $$;