-- =============================================================================
-- balance_freeze_log — журнал заморозок/разморозок тенантов по балансу
-- (Billing v2 Spec C). INSERT-only таблица: записи не обновляются, только
-- добавляются. Используется PreflightBalanceService для аудит-трейла.
-- =============================================================================
CREATE TABLE IF NOT EXISTS balance_freeze_log (
    id                   BIGSERIAL PRIMARY KEY,
    tenant_id            BIGINT NOT NULL REFERENCES tenants(id),
    event_type           VARCHAR(30) NOT NULL,
    triggered_by         VARCHAR(30) NOT NULL,
    balance_rub_at_event DECIMAL(12,2) NOT NULL,
    required_leads       INTEGER NOT NULL,
    capacity_leads       INTEGER NOT NULL,
    total_daily_limit    INTEGER NOT NULL,
    details              JSONB,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE balance_freeze_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON balance_freeze_log
    USING (tenant_id = current_setting('app.current_tenant_id', true)::bigint);

CREATE INDEX IF NOT EXISTS balance_freeze_log_tenant_idx ON balance_freeze_log (tenant_id, created_at DESC);

-- Флаги заморозки на tenants и projects
ALTER TABLE tenants  ADD COLUMN IF NOT EXISTS frozen_by_balance_at TIMESTAMPTZ NULL;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS preflight_blocked_at TIMESTAMPTZ NULL;

CREATE INDEX IF NOT EXISTS tenants_frozen_by_balance_idx  ON tenants  (frozen_by_balance_at) WHERE frozen_by_balance_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS projects_preflight_blocked_idx ON projects (preflight_blocked_at) WHERE preflight_blocked_at IS NOT NULL;

-- Явные GRANT'ы для 4 ролей: на prod таблица создаётся через pgsql_supplier
-- (default privileges от postgres-superuser не наследуются на чужие creator-role).
-- Mirror supplier_lead_deliveries grant pattern. DO block — idempotent + dev-safe
-- (на dev ролей нет → silent skip). balance_freeze_log: SELECT+INSERT только
-- (INSERT-only по дизайну — не UPDATE, не DELETE).
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'crm_app_user') THEN
        GRANT SELECT, INSERT ON balance_freeze_log TO crm_app_user;
        GRANT USAGE, SELECT ON SEQUENCE balance_freeze_log_id_seq TO crm_app_user;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'crm_supplier_worker') THEN
        GRANT SELECT, INSERT ON balance_freeze_log TO crm_supplier_worker;
        GRANT USAGE, SELECT ON SEQUENCE balance_freeze_log_id_seq TO crm_supplier_worker;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'crm_migrator') THEN
        GRANT SELECT, INSERT ON balance_freeze_log TO crm_migrator;
        GRANT USAGE, SELECT ON SEQUENCE balance_freeze_log_id_seq TO crm_migrator;
    END IF;
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'crm_admin_user') THEN
        GRANT SELECT, INSERT ON balance_freeze_log TO crm_admin_user;
        GRANT USAGE, SELECT ON SEQUENCE balance_freeze_log_id_seq TO crm_admin_user;
    END IF;
END $$;
