-- astrodb_fix_all_permissions.sql
-- Target DB: techamvcpanel_rp_astrodb
-- Application user: techamvcpanel_rp_astro_user
-- PostgreSQL 10 compatible.
--
-- IMPORTANT:
-- Run this file as the PostgreSQL superuser/owner (postgres), not from the app user.
-- It fixes BOTH privileges and ownership so Sequelize can SELECT/INSERT/UPDATE/DELETE
-- and can ALTER existing tables during sequelize.sync().

BEGIN;

-- Database-level access
GRANT ALL PRIVILEGES ON DATABASE techamvcpanel_rp_astrodb TO techamvcpanel_rp_astro_user;

-- Schema-level access
GRANT ALL PRIVILEGES ON SCHEMA public TO techamvcpanel_rp_astro_user;

-- Existing objects: full privileges
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO techamvcpanel_rp_astro_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO techamvcpanel_rp_astro_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO techamvcpanel_rp_astro_user;

-- Transfer ownership of every normal/partitioned table in public schema.
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT c.relname
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public'
          AND c.relkind IN ('r', 'p')
    LOOP
        EXECUTE format(
            'ALTER TABLE public.%I OWNER TO techamvcpanel_rp_astro_user',
            r.relname
        );
    END LOOP;
END
$$;

-- Transfer ownership of every sequence.
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT c.relname
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public'
          AND c.relkind = 'S'
    LOOP
        EXECUTE format(
            'ALTER SEQUENCE public.%I OWNER TO techamvcpanel_rp_astro_user',
            r.relname
        );
    END LOOP;
END
$$;

-- Transfer ownership of every view.
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT c.relname
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public'
          AND c.relkind = 'v'
    LOOP
        EXECUTE format(
            'ALTER VIEW public.%I OWNER TO techamvcpanel_rp_astro_user',
            r.relname
        );
    END LOOP;
END
$$;

-- Transfer ownership of every materialized view.
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT c.relname
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname = 'public'
          AND c.relkind = 'm'
    LOOP
        EXECUTE format(
            'ALTER MATERIALIZED VIEW public.%I OWNER TO techamvcpanel_rp_astro_user',
            r.relname
        );
    END LOOP;
END
$$;

-- Transfer ownership of all ENUM types.
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT t.typname
        FROM pg_type t
        JOIN pg_namespace n ON n.oid = t.typnamespace
        WHERE n.nspname = 'public'
          AND t.typtype = 'e'
    LOOP
        EXECUTE format(
            'ALTER TYPE public.%I OWNER TO techamvcpanel_rp_astro_user',
            r.typname
        );
    END LOOP;
END
$$;

-- Transfer ownership of all functions in public schema.
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT p.proname, oidvectortypes(p.proargtypes) AS args
        FROM pg_proc p
        JOIN pg_namespace n ON n.oid = p.pronamespace
        WHERE n.nspname = 'public'
    LOOP
        EXECUTE format(
            'ALTER FUNCTION public.%I(%s) OWNER TO techamvcpanel_rp_astro_user',
            r.proname,
            r.args
        );
    END LOOP;
END
$$;

-- Re-apply full privileges after ownership transfer.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO techamvcpanel_rp_astro_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO techamvcpanel_rp_astro_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO techamvcpanel_rp_astro_user;

-- Future objects created by postgres during later imports.
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO techamvcpanel_rp_astro_user;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT ALL PRIVILEGES ON SEQUENCES TO techamvcpanel_rp_astro_user;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT ALL PRIVILEGES ON FUNCTIONS TO techamvcpanel_rp_astro_user;

COMMIT;

-- Verification: ideally all public tables should show the application user as owner.
SELECT tableowner, COUNT(*) AS table_count
FROM pg_tables
WHERE schemaname = 'public'
GROUP BY tableowner
ORDER BY tableowner;

SELECT
    has_table_privilege(
        'techamvcpanel_rp_astro_user',
        'public.categories',
        'SELECT,INSERT,UPDATE,DELETE'
    ) AS categories_dml_access,
    has_table_privilege(
        'techamvcpanel_rp_astro_user',
        'public.customer_groups',
        'SELECT,INSERT,UPDATE,DELETE'
    ) AS customer_groups_dml_access,
    has_schema_privilege(
        'techamvcpanel_rp_astro_user',
        'public',
        'USAGE,CREATE'
    ) AS public_schema_access;