-- KOL integrity hardening (addresses Phase 1 review findings):
-- 1. Composite unique indexes that enable composite FKs for ACL lineage
-- 2. Composite FKs so subscription_prompts / packages can't reference
--    mismatched (tenant_id, package_id, prompt_id) tuples
-- 3. Status CHECK constraints for defense-in-depth

-- Composite unique indexes (prerequisites for composite FKs)
CREATE UNIQUE INDEX uk_kol_profiles_id_tenant
    ON kol_profiles(id, tenant_id);
CREATE UNIQUE INDEX uk_kol_packages_id_tenant
    ON kol_packages(id, tenant_id);
CREATE UNIQUE INDEX uk_kol_subscriptions_id_tenant_package
    ON kol_subscriptions(id, tenant_id, package_id);
CREATE UNIQUE INDEX uk_kol_prompts_id_package_tenant
    ON kol_prompts(id, package_id, tenant_id);

-- Composite FK: kol_packages.(kol_profile_id, tenant_id) must match a profile
-- (prevents a package from claiming a profile in another tenant)
ALTER TABLE kol_packages
    ADD CONSTRAINT fk_kol_packages_profile_tenant
    FOREIGN KEY (kol_profile_id, tenant_id)
    REFERENCES kol_profiles(id, tenant_id);

-- Composite FK: subscription_prompts.(subscription_id, tenant_id, package_id)
-- must match a subscription (prevents tenant/package mismatch on access rows)
ALTER TABLE kol_subscription_prompts
    ADD CONSTRAINT fk_kol_subscription_prompts_subscription_scope
    FOREIGN KEY (subscription_id, tenant_id, package_id)
    REFERENCES kol_subscriptions(id, tenant_id, package_id);

-- Status enum CHECK constraints (defense-in-depth against bad writes)
ALTER TABLE kol_profiles
    ADD CONSTRAINT chk_kol_profiles_status
    CHECK (status IN ('active', 'suspended', 'closed'));
ALTER TABLE kol_packages
    ADD CONSTRAINT chk_kol_packages_status
    CHECK (status IN ('draft', 'published', 'archived'));
ALTER TABLE kol_prompts
    ADD CONSTRAINT chk_kol_prompts_status
    CHECK (status IN ('draft', 'active', 'archived'));
ALTER TABLE kol_subscriptions
    ADD CONSTRAINT chk_kol_subscriptions_status
    CHECK (status IN ('pending', 'active', 'expired', 'revoked'));
ALTER TABLE kol_subscription_prompts
    ADD CONSTRAINT chk_kol_subscription_prompts_status
    CHECK (status IN ('active', 'revoked'));
ALTER TABLE kol_usage_logs
    ADD CONSTRAINT chk_kol_usage_logs_status
    CHECK (status IN ('pending', 'completed', 'failed'));