CREATE TABLE IF NOT EXISTS fri_snapshots (
    uuid            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(uuid),
    department      VARCHAR(100) NOT NULL,
    fri_score       DECIMAL(5,2) NOT NULL CHECK (fri_score >= 0 AND fri_score <= 100),
    level           VARCHAR(20) NOT NULL CHECK (level IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL')),
    employee_count  INT NOT NULL CHECK (employee_count >= 0),
    signals         JSONB NOT NULL DEFAULT '{}',
    week_number     INT NOT NULL,
    year            INT NOT NULL,
    computed_at     TIMESTAMP DEFAULT NOW(),
    created_at      TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_id, department, week_number, year)
);

CREATE TABLE IF NOT EXISTS iv_snapshots (
    uuid             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id        UUID NOT NULL REFERENCES tenants(uuid),
    iv_score         DECIMAL(5,2) NOT NULL CHECK (iv_score >= 0 AND iv_score <= 100),
    components       JSONB NOT NULL,
    benchmark_delta  DECIMAL(6,2),
    respondent_count INT NOT NULL CHECK (respondent_count >= 0),
    week_number      INT NOT NULL,
    year             INT NOT NULL,
    computed_at      TIMESTAMP DEFAULT NOW(),
    created_at       TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_id, week_number, year)
);

CREATE INDEX IF NOT EXISTS fri_snapshots_tenant_computed_idx ON fri_snapshots (tenant_id, computed_at DESC);
CREATE INDEX IF NOT EXISTS fri_snapshots_tenant_department_idx ON fri_snapshots (tenant_id, department, computed_at DESC);
CREATE INDEX IF NOT EXISTS iv_snapshots_tenant_computed_idx ON iv_snapshots (tenant_id, computed_at DESC);

DO $$
DECLARE
    table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY ARRAY[
        'fri_snapshots',
        'iv_snapshots'
    ]
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', table_name);
        EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', table_name);
        EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I', table_name);
        EXECUTE format(
            'CREATE POLICY tenant_isolation ON %I USING (tenant_id = current_setting(''app.tenant_id'')::uuid)',
            table_name
        );
        EXECUTE format('GRANT SELECT, INSERT, UPDATE ON %I TO diwall_core_app', table_name);
    END LOOP;
END $$;
