BEGIN;

ALTER TABLE subject_profile
    DROP CONSTRAINT IF EXISTS subject_profile_status_check;

UPDATE subject_profile SET status='suspended' WHERE status='inactive';

ALTER TABLE subject_profile
    ADD COLUMN language varchar(32) NOT NULL DEFAULT 'zh-CN',
    ADD COLUMN timezone varchar(64) NOT NULL DEFAULT 'Asia/Shanghai',
    ADD COLUMN profile_data jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN conversation_preferences jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN created_by uuid,
    ADD COLUMN updated_by uuid,
    ADD CONSTRAINT subject_profile_status_check
        CHECK (status IN ('active', 'suspended', 'deleting', 'deleted')),
    ADD CONSTRAINT subject_profile_created_by_tenant_fk
        FOREIGN KEY (created_by, tenant_id) REFERENCES user_account (id, tenant_id),
    ADD CONSTRAINT subject_profile_updated_by_tenant_fk
        FOREIGN KEY (updated_by, tenant_id) REFERENCES user_account (id, tenant_id);

CREATE TABLE subject_fact (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    public_id            uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    tenant_id            uuid NOT NULL REFERENCES tenant(id),
    subject_id           uuid NOT NULL,
    fact_type            varchar(64) NOT NULL,
    content              text NOT NULL,
    source_type          varchar(40) NOT NULL
                         CHECK (source_type IN ('subject_statement', 'family_entry', 'model_candidate', 'imported')),
    source_ref           varchar(300),
    confirmation_status  varchar(32) NOT NULL DEFAULT 'pending'
                         CHECK (confirmation_status IN ('pending', 'confirmed', 'rejected', 'superseded')),
    sensitivity          varchar(24) NOT NULL DEFAULT 'normal'
                         CHECK (sensitivity IN ('normal', 'sensitive', 'high')),
    valid_from           timestamptz,
    valid_to             timestamptz,
    version              bigint NOT NULL DEFAULT 1 CHECK (version > 0),
    created_by           uuid NOT NULL,
    updated_by           uuid NOT NULL,
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT subject_fact_subject_tenant_fk
        FOREIGN KEY (subject_id, tenant_id) REFERENCES subject_profile (id, tenant_id),
    CONSTRAINT subject_fact_created_by_tenant_fk
        FOREIGN KEY (created_by, tenant_id) REFERENCES user_account (id, tenant_id),
    CONSTRAINT subject_fact_updated_by_tenant_fk
        FOREIGN KEY (updated_by, tenant_id) REFERENCES user_account (id, tenant_id),
    CONSTRAINT subject_fact_valid_range CHECK (valid_to IS NULL OR valid_from IS NULL OR valid_to >= valid_from),
    CONSTRAINT subject_fact_id_tenant_key UNIQUE (id, tenant_id)
);
CREATE INDEX subject_fact_subject_status_idx
    ON subject_fact (tenant_id, subject_id, confirmation_status, updated_at DESC);

CREATE TABLE consent_record (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    public_id        uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    tenant_id        uuid NOT NULL REFERENCES tenant(id),
    subject_id       uuid NOT NULL,
    consent_type     varchar(64) NOT NULL
                     CHECK (consent_type IN (
                        'transcript_storage', 'raw_audio_storage', 'story_extraction',
                        'personalized_memory', 'family_sharing'
                     )),
    scope            jsonb NOT NULL DEFAULT '{}'::jsonb,
    policy_version   varchar(64) NOT NULL,
    notice_digest    varchar(128) NOT NULL,
    actor_user_id    uuid NOT NULL,
    evidence_ref     varchar(300),
    granted_at       timestamptz NOT NULL DEFAULT now(),
    expires_at       timestamptz,
    withdrawn_at     timestamptz,
    version          bigint NOT NULL DEFAULT 1 CHECK (version > 0),
    created_at       timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT consent_record_subject_tenant_fk
        FOREIGN KEY (subject_id, tenant_id) REFERENCES subject_profile (id, tenant_id),
    CONSTRAINT consent_record_actor_tenant_fk
        FOREIGN KEY (actor_user_id, tenant_id) REFERENCES user_account (id, tenant_id),
    CONSTRAINT consent_record_expiry_check CHECK (expires_at IS NULL OR expires_at > granted_at),
    CONSTRAINT consent_record_withdrawal_check CHECK (withdrawn_at IS NULL OR withdrawn_at >= granted_at),
    CONSTRAINT consent_record_id_tenant_key UNIQUE (id, tenant_id)
);
CREATE INDEX consent_record_subject_type_time_idx
    ON consent_record (tenant_id, subject_id, consent_type, granted_at DESC, id DESC);

-- “同一类型最多一条当前有效授权”包含对当前时间的判断，不能由使用
-- now() 的部分唯一索引表达。授予服务必须在锁定 subject_profile 后，于同一
-- 事务中检查 withdrawn_at / expires_at 并追加新记录。

COMMIT;