-- =============================================================================
-- Hole #2: Partition 7 audit tables by RANGE month
-- Source of truth for columns/triggers/indexes: /tmp/prod-audit-ddl.sql (pg_dump --schema-only from prod)
-- Tested on: liderra_rehearsal (restored from prod.dump)
-- =============================================================================
--
-- Strategy per table:
--   1. RENAME old → _old
--   2. DISABLE TRIGGER ALL on _old
--   3. RENAME old indexes (free prod index names)
--   4. CREATE TABLE partitioned (exact prod columns, no FK refs to avoid cascade issues)
--   5. 6 month partitions 2026_02..2026_07 + DEFAULT
--   6. CREATE indexes (prod-exact names and definitions)
--   7. RLS (exact prod policies named tenant_isolation; saas_admin_audit_log: no RLS)
--   8. chain_hash trigger BEFORE INSERT (6 tables; webhook_log: no triggers)
--   9. INSERT SELECT ORDER BY id (trigger recalculates log_hash per-partition)
--  10. New sequence OWNED BY new table; setval to MAX(id)
--  11. GRANTs (prod-exact)
--  12. block_mutation trigger BEFORE DELETE/UPDATE (after data copy)
--  13. DROP _old
--
-- Retention seeds + migration row.
-- =============================================================================

BEGIN;
SET LOCAL timezone = 'UTC';
SET LOCAL statement_timeout = '0';
-- session_replication_role=replica отключает user-триггеры (chain_hash) на время
-- копирования. Иначе при INSERT под postgres (BYPASSRLS) триггер пересчитал бы
-- log_hash как GLOBAL-within-partition, что НЕ совпадает с per-tenant chain,
-- построенным на проде под crm_app_user (RLS). Сохраняем исходные log_hash as-is:
-- вся история каждого tenant сейчас в партиции 2026_05, поэтому per-partition
-- validator увидит исходную цепочку без разрывов. Сбрасывается в конце транзакции.
SET LOCAL session_replication_role = replica;

-- =============================================================================
-- DROP FK constraints pointing at webhook_log (decision W1)
-- =============================================================================
ALTER TABLE public.failed_webhook_jobs
    DROP CONSTRAINT IF EXISTS failed_webhook_jobs_webhook_log_id_fkey;
ALTER TABLE public.rejected_deals_log
    DROP CONSTRAINT IF EXISTS rejected_deals_log_webhook_log_id_fkey;

-- =============================================================================
-- 1. activity_log
--    Prod columns: id/tenant_id(NOT NULL)/user_id/deal_id(NOT NULL)/event(varchar100)/
--                  old_value/new_value/context(jsonb)/ip_address/user_agent/log_hash/created_at
--    Triggers: trg_audit_chain_hash_activity_log + trg_audit_block_mut_activity_log
--    RLS: tenant_isolation (simple tenant_id)
--    Indexes: idx_activity_tenant_deal_created / idx_activity_tenant_user_created
-- =============================================================================

ALTER TABLE public.activity_log RENAME TO activity_log_old;
ALTER TABLE public.activity_log_old RENAME CONSTRAINT activity_log_pkey TO activity_log_pkey_old;
ALTER SEQUENCE public.activity_log_id_seq RENAME TO activity_log_id_seq_old;
ALTER TABLE public.activity_log_old DISABLE TRIGGER ALL;
ALTER INDEX public.idx_activity_tenant_deal_created RENAME TO idx_activity_tenant_deal_created_old;
ALTER INDEX public.idx_activity_tenant_user_created RENAME TO idx_activity_tenant_user_created_old;

CREATE TABLE public.activity_log (
    id          bigint NOT NULL,
    tenant_id   bigint NOT NULL,
    user_id     bigint,
    deal_id     bigint NOT NULL,
    event       character varying(100) NOT NULL,
    old_value   text,
    new_value   text,
    context     jsonb,
    ip_address  inet,
    user_agent  text,
    log_hash    bytea,
    created_at  timestamp with time zone DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE public.activity_log_y2026_m02 PARTITION OF public.activity_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE public.activity_log_y2026_m03 PARTITION OF public.activity_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE public.activity_log_y2026_m04 PARTITION OF public.activity_log
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE public.activity_log_y2026_m05 PARTITION OF public.activity_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE public.activity_log_y2026_m06 PARTITION OF public.activity_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE public.activity_log_y2026_m07 PARTITION OF public.activity_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE public.activity_log_default PARTITION OF public.activity_log DEFAULT;

ALTER TABLE public.activity_log ADD CONSTRAINT activity_log_pkey PRIMARY KEY (id, created_at);

CREATE INDEX idx_activity_tenant_deal_created ON public.activity_log
    USING btree (tenant_id, deal_id, created_at DESC);
CREATE INDEX idx_activity_tenant_user_created ON public.activity_log
    USING btree (tenant_id, user_id, created_at DESC) WHERE (user_id IS NOT NULL);

ALTER TABLE public.activity_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON public.activity_log
    USING ((tenant_id = (current_setting('app.current_tenant_id'::text))::bigint));

-- chain_hash trigger first, then copy data
CREATE TRIGGER trg_audit_chain_hash_activity_log
    BEFORE INSERT ON public.activity_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_chain_hash();

INSERT INTO public.activity_log
    (id, tenant_id, user_id, deal_id, event, old_value, new_value, context,
     ip_address, user_agent, log_hash, created_at)
SELECT  id, tenant_id, user_id, deal_id, event, old_value, new_value, context,
        ip_address, user_agent, log_hash, created_at
FROM public.activity_log_old
ORDER BY id;

-- New sequence owned by new table
CREATE SEQUENCE public.activity_log_id_seq
    AS bigint START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.activity_log_id_seq OWNED BY public.activity_log.id;
SELECT setval('public.activity_log_id_seq',
    COALESCE((SELECT MAX(id) FROM public.activity_log), 1));
ALTER TABLE public.activity_log ALTER COLUMN id
    SET DEFAULT nextval('public.activity_log_id_seq'::regclass);

GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.activity_log TO crm_app_user;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.activity_log TO crm_admin_user;
GRANT INSERT ON TABLE public.activity_log TO crm_audit_writer;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.activity_log TO crm_supplier_worker;
GRANT SELECT, USAGE ON SEQUENCE public.activity_log_id_seq TO crm_app_user;
GRANT SELECT, USAGE ON SEQUENCE public.activity_log_id_seq TO crm_admin_user;
GRANT USAGE ON SEQUENCE public.activity_log_id_seq TO crm_audit_writer;
GRANT SELECT, USAGE ON SEQUENCE public.activity_log_id_seq TO crm_supplier_worker;

-- block_mut trigger AFTER data copy
CREATE TRIGGER trg_audit_block_mut_activity_log
    BEFORE DELETE OR UPDATE ON public.activity_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_block_mutation();

DROP TABLE public.activity_log_old;

-- =============================================================================
-- 2. auth_log
--    Prod columns: id/actor_type(varchar20,default tenant_user)/tenant_id/user_id/
--                  saas_admin_user_id/email/event(varchar50)/ip_address/user_agent/
--                  failure_reason/log_hash/created_at
--                  + CHECK auth_log_actor_type_check + CHECK chk_auth_log_actor
--    Triggers: trg_audit_chain_hash_auth_log + trg_audit_block_mut_auth_log
--    RLS: tenant_isolation (special: NULL/empty current_setting = pass-through)
--    Indexes: idx_auth_log_admin/email/ip_failed/tenant_user
-- =============================================================================

ALTER TABLE public.auth_log RENAME TO auth_log_old;
ALTER TABLE public.auth_log_old RENAME CONSTRAINT auth_log_pkey TO auth_log_pkey_old;
ALTER TABLE public.auth_log_old RENAME CONSTRAINT auth_log_actor_type_check TO auth_log_actor_type_check_old;
ALTER TABLE public.auth_log_old RENAME CONSTRAINT chk_auth_log_actor TO chk_auth_log_actor_old;
ALTER SEQUENCE public.auth_log_id_seq RENAME TO auth_log_id_seq_old;
ALTER TABLE public.auth_log_old DISABLE TRIGGER ALL;
ALTER INDEX public.idx_auth_log_admin      RENAME TO idx_auth_log_admin_old;
ALTER INDEX public.idx_auth_log_email      RENAME TO idx_auth_log_email_old;
ALTER INDEX public.idx_auth_log_ip_failed  RENAME TO idx_auth_log_ip_failed_old;
ALTER INDEX public.idx_auth_log_tenant_user RENAME TO idx_auth_log_tenant_user_old;

CREATE TABLE public.auth_log (
    id                  bigint NOT NULL,
    actor_type          character varying(20) DEFAULT 'tenant_user'::character varying NOT NULL,
    tenant_id           bigint,
    user_id             bigint,
    saas_admin_user_id  bigint,
    email               character varying(255),
    event               character varying(50) NOT NULL,
    ip_address          inet,
    user_agent          text,
    failure_reason      character varying(100),
    log_hash            bytea,
    created_at          timestamp with time zone DEFAULT now(),
    CONSTRAINT auth_log_actor_type_check CHECK (((actor_type)::text = ANY
        ((ARRAY['tenant_user'::character varying, 'saas_admin'::character varying])::text[]))),
    CONSTRAINT chk_auth_log_actor CHECK (
        ((((actor_type)::text = 'tenant_user'::text) AND (user_id IS NOT NULL) AND (saas_admin_user_id IS NULL))
        OR (((actor_type)::text = 'saas_admin'::text) AND (saas_admin_user_id IS NOT NULL) AND (user_id IS NULL))
        OR (((actor_type)::text = 'tenant_user'::text) AND (user_id IS NULL) AND (saas_admin_user_id IS NULL) AND (email IS NOT NULL))))
) PARTITION BY RANGE (created_at);

CREATE TABLE public.auth_log_y2026_m02 PARTITION OF public.auth_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE public.auth_log_y2026_m03 PARTITION OF public.auth_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE public.auth_log_y2026_m04 PARTITION OF public.auth_log
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE public.auth_log_y2026_m05 PARTITION OF public.auth_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE public.auth_log_y2026_m06 PARTITION OF public.auth_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE public.auth_log_y2026_m07 PARTITION OF public.auth_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE public.auth_log_default PARTITION OF public.auth_log DEFAULT;

ALTER TABLE public.auth_log ADD CONSTRAINT auth_log_pkey PRIMARY KEY (id, created_at);

CREATE INDEX idx_auth_log_admin ON public.auth_log
    USING btree (saas_admin_user_id, created_at DESC) WHERE (saas_admin_user_id IS NOT NULL);
CREATE INDEX idx_auth_log_email ON public.auth_log
    USING btree (email, created_at DESC);
CREATE INDEX idx_auth_log_ip_failed ON public.auth_log
    USING btree (ip_address, created_at DESC) WHERE ((event)::text = 'login_failed'::text);
CREATE INDEX idx_auth_log_tenant_user ON public.auth_log
    USING btree (tenant_id, user_id, created_at DESC);

ALTER TABLE public.auth_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON public.auth_log
    USING (((current_setting('app.current_tenant_id'::text, true) IS NULL)
         OR (current_setting('app.current_tenant_id'::text, true) = ''::text)
         OR (((actor_type)::text = 'tenant_user'::text)
             AND (tenant_id = (current_setting('app.current_tenant_id'::text, true))::bigint))));

CREATE TRIGGER trg_audit_chain_hash_auth_log
    BEFORE INSERT ON public.auth_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_chain_hash();

INSERT INTO public.auth_log
    (id, actor_type, tenant_id, user_id, saas_admin_user_id, email, event,
     ip_address, user_agent, failure_reason, log_hash, created_at)
SELECT  id, actor_type, tenant_id, user_id, saas_admin_user_id, email, event,
        ip_address, user_agent, failure_reason, log_hash, created_at
FROM public.auth_log_old
ORDER BY id;

CREATE SEQUENCE public.auth_log_id_seq
    AS bigint START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.auth_log_id_seq OWNED BY public.auth_log.id;
SELECT setval('public.auth_log_id_seq',
    COALESCE((SELECT MAX(id) FROM public.auth_log), 1));
ALTER TABLE public.auth_log ALTER COLUMN id
    SET DEFAULT nextval('public.auth_log_id_seq'::regclass);

GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.auth_log TO crm_app_user;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.auth_log TO crm_admin_user;
GRANT INSERT ON TABLE public.auth_log TO crm_audit_writer;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.auth_log TO crm_supplier_worker;
GRANT SELECT, USAGE ON SEQUENCE public.auth_log_id_seq TO crm_app_user;
GRANT SELECT, USAGE ON SEQUENCE public.auth_log_id_seq TO crm_admin_user;
GRANT USAGE ON SEQUENCE public.auth_log_id_seq TO crm_audit_writer;
GRANT SELECT, USAGE ON SEQUENCE public.auth_log_id_seq TO crm_supplier_worker;

CREATE TRIGGER trg_audit_block_mut_auth_log
    BEFORE DELETE OR UPDATE ON public.auth_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_block_mutation();

DROP TABLE public.auth_log_old;

-- =============================================================================
-- 3. balance_transactions
--    Prod columns: id/tenant_id(NOT NULL)/type(varchar50)/amount_rub(numeric12.2 default 0)/
--                  amount_leads(int default 0)/balance_rub_after/balance_leads_after/
--                  description/related_type(varchar100)/related_id/user_id/admin_user_id/
--                  log_hash/created_at + CHECK balance_transactions_type_check
--    Triggers: trg_audit_chain_hash_balance_tx + trg_audit_block_mut_balance_tx
--    RLS: tenant_isolation (simple)
--    Indexes: idx_balance_tenant_created / idx_balance_tenant_type
-- =============================================================================

ALTER TABLE public.balance_transactions RENAME TO balance_transactions_old;
ALTER TABLE public.balance_transactions_old RENAME CONSTRAINT balance_transactions_pkey TO balance_transactions_pkey_old;
ALTER TABLE public.balance_transactions_old RENAME CONSTRAINT balance_transactions_type_check TO balance_transactions_type_check_old;
ALTER SEQUENCE public.balance_transactions_id_seq RENAME TO balance_transactions_id_seq_old;
ALTER TABLE public.balance_transactions_old DISABLE TRIGGER ALL;
ALTER INDEX public.idx_balance_tenant_created RENAME TO idx_balance_tenant_created_old;
ALTER INDEX public.idx_balance_tenant_type    RENAME TO idx_balance_tenant_type_old;

CREATE TABLE public.balance_transactions (
    id                  bigint NOT NULL,
    tenant_id           bigint NOT NULL,
    type                character varying(50) NOT NULL,
    amount_rub          numeric(12,2) DEFAULT 0,
    amount_leads        integer DEFAULT 0,
    balance_rub_after   numeric(12,2),
    balance_leads_after integer,
    description         text,
    related_type        character varying(100),
    related_id          bigint,
    user_id             bigint,
    admin_user_id       bigint,
    log_hash            bytea,
    created_at          timestamp with time zone DEFAULT now(),
    CONSTRAINT balance_transactions_type_check CHECK (((type)::text = ANY
        ((ARRAY['trial_bonus'::character varying, 'topup'::character varying,
                'lead_charge'::character varying, 'refund'::character varying,
                'manual_adjustment'::character varying, 'historical_import'::character varying,
                'chargeback_writedown'::character varying,
                'chargeback_repayment'::character varying])::text[])))
) PARTITION BY RANGE (created_at);

CREATE TABLE public.balance_transactions_y2026_m02 PARTITION OF public.balance_transactions
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE public.balance_transactions_y2026_m03 PARTITION OF public.balance_transactions
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE public.balance_transactions_y2026_m04 PARTITION OF public.balance_transactions
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE public.balance_transactions_y2026_m05 PARTITION OF public.balance_transactions
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE public.balance_transactions_y2026_m06 PARTITION OF public.balance_transactions
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE public.balance_transactions_y2026_m07 PARTITION OF public.balance_transactions
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE public.balance_transactions_default PARTITION OF public.balance_transactions DEFAULT;

ALTER TABLE public.balance_transactions ADD CONSTRAINT balance_transactions_pkey PRIMARY KEY (id, created_at);

CREATE INDEX idx_balance_tenant_created ON public.balance_transactions
    USING btree (tenant_id, created_at DESC);
CREATE INDEX idx_balance_tenant_type ON public.balance_transactions
    USING btree (tenant_id, type);

ALTER TABLE public.balance_transactions ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON public.balance_transactions
    USING ((tenant_id = (current_setting('app.current_tenant_id'::text))::bigint));

CREATE TRIGGER trg_audit_chain_hash_balance_tx
    BEFORE INSERT ON public.balance_transactions
    FOR EACH ROW EXECUTE FUNCTION public.audit_chain_hash();

INSERT INTO public.balance_transactions
    (id, tenant_id, type, amount_rub, amount_leads, balance_rub_after,
     balance_leads_after, description, related_type, related_id, user_id,
     admin_user_id, log_hash, created_at)
SELECT  id, tenant_id, type, amount_rub, amount_leads, balance_rub_after,
        balance_leads_after, description, related_type, related_id, user_id,
        admin_user_id, log_hash, created_at
FROM public.balance_transactions_old
ORDER BY id;

CREATE SEQUENCE public.balance_transactions_id_seq
    AS bigint START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.balance_transactions_id_seq OWNED BY public.balance_transactions.id;
SELECT setval('public.balance_transactions_id_seq',
    COALESCE((SELECT MAX(id) FROM public.balance_transactions), 1));
ALTER TABLE public.balance_transactions ALTER COLUMN id
    SET DEFAULT nextval('public.balance_transactions_id_seq'::regclass);

GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.balance_transactions TO crm_app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE public.balance_transactions TO crm_admin_user;
GRANT INSERT ON TABLE public.balance_transactions TO crm_audit_writer;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.balance_transactions TO crm_supplier_worker;
GRANT SELECT, USAGE ON SEQUENCE public.balance_transactions_id_seq TO crm_app_user;
GRANT SELECT, USAGE ON SEQUENCE public.balance_transactions_id_seq TO crm_admin_user;
GRANT USAGE ON SEQUENCE public.balance_transactions_id_seq TO crm_audit_writer;
GRANT SELECT, USAGE ON SEQUENCE public.balance_transactions_id_seq TO crm_supplier_worker;

CREATE TRIGGER trg_audit_block_mut_balance_tx
    BEFORE DELETE OR UPDATE ON public.balance_transactions
    FOR EACH ROW EXECUTE FUNCTION public.audit_block_mutation();

DROP TABLE public.balance_transactions_old;

-- =============================================================================
-- 4. tenant_operations_log
--    Prod columns: id/tenant_id(NOT NULL)/user_id/entity_type(varchar50 NOT NULL)/
--                  entity_id/event(varchar100 NOT NULL)/payload_before/payload_after/
--                  ip_address/user_agent/log_hash/created_at
--    Triggers: trg_audit_chain_hash_tenant_ops + trg_audit_block_mut_tenant_ops
--    RLS: tenant_isolation (simple)
--    Indexes: idx_tenant_ops_entity / idx_tenant_ops_tenant_created
-- =============================================================================

ALTER TABLE public.tenant_operations_log RENAME TO tenant_operations_log_old;
ALTER TABLE public.tenant_operations_log_old RENAME CONSTRAINT tenant_operations_log_pkey TO tenant_operations_log_pkey_old;
ALTER SEQUENCE public.tenant_operations_log_id_seq RENAME TO tenant_operations_log_id_seq_old;
ALTER TABLE public.tenant_operations_log_old DISABLE TRIGGER ALL;
ALTER INDEX public.idx_tenant_ops_entity        RENAME TO idx_tenant_ops_entity_old;
ALTER INDEX public.idx_tenant_ops_tenant_created RENAME TO idx_tenant_ops_tenant_created_old;

CREATE TABLE public.tenant_operations_log (
    id              bigint NOT NULL,
    tenant_id       bigint NOT NULL,
    user_id         bigint,
    entity_type     character varying(50) NOT NULL,
    entity_id       bigint,
    event           character varying(100) NOT NULL,
    payload_before  jsonb,
    payload_after   jsonb,
    ip_address      inet,
    user_agent      text,
    log_hash        bytea,
    created_at      timestamp with time zone DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE public.tenant_operations_log_y2026_m02 PARTITION OF public.tenant_operations_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE public.tenant_operations_log_y2026_m03 PARTITION OF public.tenant_operations_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE public.tenant_operations_log_y2026_m04 PARTITION OF public.tenant_operations_log
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE public.tenant_operations_log_y2026_m05 PARTITION OF public.tenant_operations_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE public.tenant_operations_log_y2026_m06 PARTITION OF public.tenant_operations_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE public.tenant_operations_log_y2026_m07 PARTITION OF public.tenant_operations_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE public.tenant_operations_log_default PARTITION OF public.tenant_operations_log DEFAULT;

ALTER TABLE public.tenant_operations_log ADD CONSTRAINT tenant_operations_log_pkey PRIMARY KEY (id, created_at);

CREATE INDEX idx_tenant_ops_entity ON public.tenant_operations_log
    USING btree (tenant_id, entity_type, entity_id, created_at DESC) WHERE (entity_id IS NOT NULL);
CREATE INDEX idx_tenant_ops_tenant_created ON public.tenant_operations_log
    USING btree (tenant_id, created_at DESC);

ALTER TABLE public.tenant_operations_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON public.tenant_operations_log
    USING ((tenant_id = (current_setting('app.current_tenant_id'::text))::bigint));

CREATE TRIGGER trg_audit_chain_hash_tenant_ops
    BEFORE INSERT ON public.tenant_operations_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_chain_hash();

INSERT INTO public.tenant_operations_log
    (id, tenant_id, user_id, entity_type, entity_id, event, payload_before,
     payload_after, ip_address, user_agent, log_hash, created_at)
SELECT  id, tenant_id, user_id, entity_type, entity_id, event, payload_before,
        payload_after, ip_address, user_agent, log_hash, created_at
FROM public.tenant_operations_log_old
ORDER BY id;

CREATE SEQUENCE public.tenant_operations_log_id_seq
    AS bigint START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.tenant_operations_log_id_seq OWNED BY public.tenant_operations_log.id;
SELECT setval('public.tenant_operations_log_id_seq',
    COALESCE((SELECT MAX(id) FROM public.tenant_operations_log), 1));
ALTER TABLE public.tenant_operations_log ALTER COLUMN id
    SET DEFAULT nextval('public.tenant_operations_log_id_seq'::regclass);

GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.tenant_operations_log TO crm_app_user;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.tenant_operations_log TO crm_admin_user;
GRANT ALL ON TABLE public.tenant_operations_log TO crm_migrator;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.tenant_operations_log TO crm_supplier_worker;
GRANT SELECT, USAGE ON SEQUENCE public.tenant_operations_log_id_seq TO crm_app_user;
GRANT SELECT, USAGE ON SEQUENCE public.tenant_operations_log_id_seq TO crm_admin_user;
GRANT ALL ON SEQUENCE public.tenant_operations_log_id_seq TO crm_migrator;
GRANT SELECT, USAGE ON SEQUENCE public.tenant_operations_log_id_seq TO crm_supplier_worker;

CREATE TRIGGER trg_audit_block_mut_tenant_ops
    BEFORE DELETE OR UPDATE ON public.tenant_operations_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_block_mutation();

DROP TABLE public.tenant_operations_log_old;

-- =============================================================================
-- 5. pd_processing_log
--    Prod columns: id/tenant_id/subject_type(varchar50)/subject_id/action(varchar50)/
--                  purpose(varchar255)/actor_tenant_user_id/actor_admin_user_id/
--                  ip_address/log_hash/created_at + CHECK chk_pd_actor
--    Triggers: trg_audit_chain_hash_pd_log + trg_audit_block_mut_pd_log
--    RLS: tenant_isolation (simple)
--    Indexes: idx_pd_log_admin_actor / idx_pd_log_tenant
-- =============================================================================

ALTER TABLE public.pd_processing_log RENAME TO pd_processing_log_old;
ALTER TABLE public.pd_processing_log_old RENAME CONSTRAINT pd_processing_log_pkey TO pd_processing_log_pkey_old;
ALTER TABLE public.pd_processing_log_old RENAME CONSTRAINT chk_pd_actor TO chk_pd_actor_old;
ALTER SEQUENCE public.pd_processing_log_id_seq RENAME TO pd_processing_log_id_seq_old;
ALTER TABLE public.pd_processing_log_old DISABLE TRIGGER ALL;
ALTER INDEX public.idx_pd_log_admin_actor RENAME TO idx_pd_log_admin_actor_old;
ALTER INDEX public.idx_pd_log_tenant      RENAME TO idx_pd_log_tenant_old;

CREATE TABLE public.pd_processing_log (
    id                      bigint NOT NULL,
    tenant_id               bigint,
    subject_type            character varying(50),
    subject_id              bigint,
    action                  character varying(50),
    purpose                 character varying(255),
    actor_tenant_user_id    bigint,
    actor_admin_user_id     bigint,
    ip_address              inet,
    log_hash                bytea,
    created_at              timestamp with time zone DEFAULT now(),
    CONSTRAINT chk_pd_actor CHECK (
        (((actor_tenant_user_id IS NOT NULL) AND (actor_admin_user_id IS NULL))
        OR ((actor_tenant_user_id IS NULL) AND (actor_admin_user_id IS NOT NULL))
        OR ((actor_tenant_user_id IS NULL) AND (actor_admin_user_id IS NULL))))
) PARTITION BY RANGE (created_at);

CREATE TABLE public.pd_processing_log_y2026_m02 PARTITION OF public.pd_processing_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE public.pd_processing_log_y2026_m03 PARTITION OF public.pd_processing_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE public.pd_processing_log_y2026_m04 PARTITION OF public.pd_processing_log
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE public.pd_processing_log_y2026_m05 PARTITION OF public.pd_processing_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE public.pd_processing_log_y2026_m06 PARTITION OF public.pd_processing_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE public.pd_processing_log_y2026_m07 PARTITION OF public.pd_processing_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE public.pd_processing_log_default PARTITION OF public.pd_processing_log DEFAULT;

ALTER TABLE public.pd_processing_log ADD CONSTRAINT pd_processing_log_pkey PRIMARY KEY (id, created_at);

CREATE INDEX idx_pd_log_admin_actor ON public.pd_processing_log
    USING btree (actor_admin_user_id, created_at DESC) WHERE (actor_admin_user_id IS NOT NULL);
CREATE INDEX idx_pd_log_tenant ON public.pd_processing_log
    USING btree (tenant_id, created_at DESC);

ALTER TABLE public.pd_processing_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON public.pd_processing_log
    USING ((tenant_id = (current_setting('app.current_tenant_id'::text))::bigint));

CREATE TRIGGER trg_audit_chain_hash_pd_log
    BEFORE INSERT ON public.pd_processing_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_chain_hash();

INSERT INTO public.pd_processing_log
    (id, tenant_id, subject_type, subject_id, action, purpose,
     actor_tenant_user_id, actor_admin_user_id, ip_address, log_hash, created_at)
SELECT  id, tenant_id, subject_type, subject_id, action, purpose,
        actor_tenant_user_id, actor_admin_user_id, ip_address, log_hash, created_at
FROM public.pd_processing_log_old
ORDER BY id;

CREATE SEQUENCE public.pd_processing_log_id_seq
    AS bigint START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.pd_processing_log_id_seq OWNED BY public.pd_processing_log.id;
SELECT setval('public.pd_processing_log_id_seq',
    COALESCE((SELECT MAX(id) FROM public.pd_processing_log), 1));
ALTER TABLE public.pd_processing_log ALTER COLUMN id
    SET DEFAULT nextval('public.pd_processing_log_id_seq'::regclass);

GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.pd_processing_log TO crm_app_user;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.pd_processing_log TO crm_admin_user;
GRANT INSERT ON TABLE public.pd_processing_log TO crm_audit_writer;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.pd_processing_log TO crm_supplier_worker;
GRANT SELECT, USAGE ON SEQUENCE public.pd_processing_log_id_seq TO crm_app_user;
GRANT SELECT, USAGE ON SEQUENCE public.pd_processing_log_id_seq TO crm_admin_user;
GRANT USAGE ON SEQUENCE public.pd_processing_log_id_seq TO crm_audit_writer;
GRANT SELECT, USAGE ON SEQUENCE public.pd_processing_log_id_seq TO crm_supplier_worker;

CREATE TRIGGER trg_audit_block_mut_pd_log
    BEFORE DELETE OR UPDATE ON public.pd_processing_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_block_mutation();

DROP TABLE public.pd_processing_log_old;

-- =============================================================================
-- 6. webhook_log  (partition by received_at; NO hash-chain triggers; FK to it dropped above)
--    Prod columns: id/tenant_id/raw_payload(jsonb NOT NULL)/received_at/processed_at/
--                  deal_id/error/source(varchar50)/status(varchar50)/lead_id/ip_address/created_at
--    NO triggers at all (no chain_hash, no block_mut)
--    RLS: tenant_isolation (simple)
--    Indexes: idx_webhook_log_status / idx_webhook_log_tenant_received
-- =============================================================================

ALTER TABLE public.webhook_log RENAME TO webhook_log_old;
ALTER TABLE public.webhook_log_old RENAME CONSTRAINT webhook_log_pkey TO webhook_log_pkey_old;
ALTER SEQUENCE public.webhook_log_id_seq RENAME TO webhook_log_id_seq_old;
ALTER TABLE public.webhook_log_old DISABLE TRIGGER ALL;
ALTER INDEX public.idx_webhook_log_status          RENAME TO idx_webhook_log_status_old;
ALTER INDEX public.idx_webhook_log_tenant_received RENAME TO idx_webhook_log_tenant_received_old;

CREATE TABLE public.webhook_log (
    id              bigint NOT NULL,
    tenant_id       bigint,
    raw_payload     jsonb NOT NULL,
    received_at     timestamp with time zone DEFAULT now(),
    processed_at    timestamp with time zone,
    deal_id         bigint,
    error           text,
    source          character varying(50),
    status          character varying(50),
    lead_id         bigint,
    ip_address      inet,
    created_at      timestamp with time zone DEFAULT now()
) PARTITION BY RANGE (received_at);

CREATE TABLE public.webhook_log_y2026_m02 PARTITION OF public.webhook_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE public.webhook_log_y2026_m03 PARTITION OF public.webhook_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE public.webhook_log_y2026_m04 PARTITION OF public.webhook_log
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE public.webhook_log_y2026_m05 PARTITION OF public.webhook_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE public.webhook_log_y2026_m06 PARTITION OF public.webhook_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE public.webhook_log_y2026_m07 PARTITION OF public.webhook_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE public.webhook_log_default PARTITION OF public.webhook_log DEFAULT;

ALTER TABLE public.webhook_log ADD CONSTRAINT webhook_log_pkey PRIMARY KEY (id, received_at);

CREATE INDEX idx_webhook_log_status ON public.webhook_log
    USING btree (status, created_at DESC);
CREATE INDEX idx_webhook_log_tenant_received ON public.webhook_log
    USING btree (tenant_id, received_at DESC);

ALTER TABLE public.webhook_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON public.webhook_log
    USING ((tenant_id = (current_setting('app.current_tenant_id'::text))::bigint));

-- No chain_hash trigger on webhook_log
INSERT INTO public.webhook_log
    (id, tenant_id, raw_payload, received_at, processed_at, deal_id, error,
     source, status, lead_id, ip_address, created_at)
SELECT  id, tenant_id, raw_payload, received_at, processed_at, deal_id, error,
        source, status, lead_id, ip_address, created_at
FROM public.webhook_log_old
ORDER BY id;

CREATE SEQUENCE public.webhook_log_id_seq
    AS bigint START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.webhook_log_id_seq OWNED BY public.webhook_log.id;
SELECT setval('public.webhook_log_id_seq',
    COALESCE((SELECT MAX(id) FROM public.webhook_log), 1));
ALTER TABLE public.webhook_log ALTER COLUMN id
    SET DEFAULT nextval('public.webhook_log_id_seq'::regclass);

GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.webhook_log TO crm_app_user;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.webhook_log TO crm_admin_user;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.webhook_log TO crm_supplier_worker;
GRANT SELECT, USAGE ON SEQUENCE public.webhook_log_id_seq TO crm_app_user;
GRANT SELECT, USAGE ON SEQUENCE public.webhook_log_id_seq TO crm_admin_user;
GRANT SELECT, USAGE ON SEQUENCE public.webhook_log_id_seq TO crm_supplier_worker;

-- No block_mut trigger on webhook_log
DROP TABLE public.webhook_log_old;

-- =============================================================================
-- 7. saas_admin_audit_log  (NO RLS; SaaS admin table)
--    Prod columns: id/admin_user_id(NOT NULL)/action(varchar100 NOT NULL)/target_type(varchar50)/
--                  target_id/target_tenant_id/payload_before/payload_after/reason/
--                  ip_address(NOT NULL inet)/user_agent/requires_approval(bool default false)/
--                  approved_by/approved_at/log_hash/created_at
--    Triggers: trg_audit_chain_hash_saas_admin_audit + trg_audit_block_mut_saas_admin_audit
--    NO RLS
--    Indexes: idx_admin_audit_action/admin/pending/tenant
-- =============================================================================

ALTER TABLE public.saas_admin_audit_log RENAME TO saas_admin_audit_log_old;
ALTER TABLE public.saas_admin_audit_log_old RENAME CONSTRAINT saas_admin_audit_log_pkey TO saas_admin_audit_log_pkey_old;
ALTER SEQUENCE public.saas_admin_audit_log_id_seq RENAME TO saas_admin_audit_log_id_seq_old;
ALTER TABLE public.saas_admin_audit_log_old DISABLE TRIGGER ALL;
ALTER INDEX public.idx_admin_audit_action  RENAME TO idx_admin_audit_action_old;
ALTER INDEX public.idx_admin_audit_admin   RENAME TO idx_admin_audit_admin_old;
ALTER INDEX public.idx_admin_audit_pending RENAME TO idx_admin_audit_pending_old;
ALTER INDEX public.idx_admin_audit_tenant  RENAME TO idx_admin_audit_tenant_old;

CREATE TABLE public.saas_admin_audit_log (
    id                  bigint NOT NULL,
    admin_user_id       bigint NOT NULL,
    action              character varying(100) NOT NULL,
    target_type         character varying(50),
    target_id           bigint,
    target_tenant_id    bigint,
    payload_before      jsonb,
    payload_after       jsonb,
    reason              text,
    ip_address          inet NOT NULL,
    user_agent          text,
    requires_approval   boolean DEFAULT false,
    approved_by         bigint,
    approved_at         timestamp with time zone,
    log_hash            bytea,
    created_at          timestamp with time zone DEFAULT now()
) PARTITION BY RANGE (created_at);

CREATE TABLE public.saas_admin_audit_log_y2026_m02 PARTITION OF public.saas_admin_audit_log
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE public.saas_admin_audit_log_y2026_m03 PARTITION OF public.saas_admin_audit_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE public.saas_admin_audit_log_y2026_m04 PARTITION OF public.saas_admin_audit_log
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE public.saas_admin_audit_log_y2026_m05 PARTITION OF public.saas_admin_audit_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE public.saas_admin_audit_log_y2026_m06 PARTITION OF public.saas_admin_audit_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE public.saas_admin_audit_log_y2026_m07 PARTITION OF public.saas_admin_audit_log
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE public.saas_admin_audit_log_default PARTITION OF public.saas_admin_audit_log DEFAULT;

ALTER TABLE public.saas_admin_audit_log ADD CONSTRAINT saas_admin_audit_log_pkey PRIMARY KEY (id, created_at);

CREATE INDEX idx_admin_audit_action ON public.saas_admin_audit_log
    USING btree (action, created_at DESC);
CREATE INDEX idx_admin_audit_admin ON public.saas_admin_audit_log
    USING btree (admin_user_id, created_at DESC);
CREATE INDEX idx_admin_audit_pending ON public.saas_admin_audit_log
    USING btree (approved_at) WHERE ((requires_approval = true) AND (approved_at IS NULL));
CREATE INDEX idx_admin_audit_tenant ON public.saas_admin_audit_log
    USING btree (target_tenant_id, created_at DESC) WHERE (target_tenant_id IS NOT NULL);

-- No RLS on saas_admin_audit_log

CREATE TRIGGER trg_audit_chain_hash_saas_admin_audit
    BEFORE INSERT ON public.saas_admin_audit_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_chain_hash();

INSERT INTO public.saas_admin_audit_log
    (id, admin_user_id, action, target_type, target_id, target_tenant_id,
     payload_before, payload_after, reason, ip_address, user_agent,
     requires_approval, approved_by, approved_at, log_hash, created_at)
SELECT  id, admin_user_id, action, target_type, target_id, target_tenant_id,
        payload_before, payload_after, reason, ip_address, user_agent,
        requires_approval, approved_by, approved_at, log_hash, created_at
FROM public.saas_admin_audit_log_old
ORDER BY id;

CREATE SEQUENCE public.saas_admin_audit_log_id_seq
    AS bigint START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.saas_admin_audit_log_id_seq OWNED BY public.saas_admin_audit_log.id;
SELECT setval('public.saas_admin_audit_log_id_seq',
    COALESCE((SELECT MAX(id) FROM public.saas_admin_audit_log), 1));
ALTER TABLE public.saas_admin_audit_log ALTER COLUMN id
    SET DEFAULT nextval('public.saas_admin_audit_log_id_seq'::regclass);

GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.saas_admin_audit_log TO crm_admin_user;
GRANT INSERT ON TABLE public.saas_admin_audit_log TO crm_audit_writer;
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE public.saas_admin_audit_log TO crm_supplier_worker;
GRANT SELECT, USAGE ON SEQUENCE public.saas_admin_audit_log_id_seq TO crm_app_user;
GRANT SELECT, USAGE ON SEQUENCE public.saas_admin_audit_log_id_seq TO crm_admin_user;
GRANT USAGE ON SEQUENCE public.saas_admin_audit_log_id_seq TO crm_audit_writer;
GRANT SELECT, USAGE ON SEQUENCE public.saas_admin_audit_log_id_seq TO crm_supplier_worker;

CREATE TRIGGER trg_audit_block_mut_saas_admin_audit
    BEFORE DELETE OR UPDATE ON public.saas_admin_audit_log
    FOR EACH ROW EXECUTE FUNCTION public.audit_block_mutation();

DROP TABLE public.saas_admin_audit_log_old;

-- =============================================================================
-- Retention seeds in system_settings
-- Key format MUST match PartitionsDropExpired::resolveRetention():
--   partition_retention_months_<table>
-- =============================================================================
INSERT INTO public.system_settings (key, value, type, description)
VALUES
    ('partition_retention_months_auth_log',              '24', 'int', 'Months to retain auth_log partitions (hole #2)'),
    ('partition_retention_months_activity_log',          '36', 'int', 'Months to retain activity_log partitions (hole #2)'),
    ('partition_retention_months_tenant_operations_log', '24', 'int', 'Months to retain tenant_operations_log partitions (hole #2)'),
    ('partition_retention_months_webhook_log',           '3',  'int', 'Months to retain webhook_log partitions (hole #2)'),
    ('partition_retention_months_balance_transactions',  '84', 'int', 'Months to retain balance_transactions partitions, 7y NK RF (hole #2)'),
    ('partition_retention_months_pd_processing_log',     '36', 'int', 'Months to retain pd_processing_log partitions, 152-FZ (hole #2)'),
    ('partition_retention_months_saas_admin_audit_log',  '84', 'int', 'Months to retain saas_admin_audit_log partitions, 7y (hole #2)')
ON CONFLICT (key) DO NOTHING;

-- =============================================================================
-- Migration row
-- =============================================================================
INSERT INTO public.migrations (migration, batch)
VALUES ('2026_05_23_000002_hole2_partition_audit_tables', 3)
ON CONFLICT DO NOTHING;

COMMIT;
