-- TCS-IPTV 初始化迁移
-- 说明：链上为权威数据源；PostgreSQL 存业务元数据与链上数据镜像，用于高效查询。
-- 对应需求：需求16（数据结构）、需求3/6/7（映射）、需求12（版本变更）
-- 注：CTID 概念列统一命名为 content_twin_id，避开 PostgreSQL 系统保留列名 ctid。

BEGIN;

-- 内容主表（Content Registry 镜像）
CREATE TABLE IF NOT EXISTS content_registry (
    content_twin_id VARCHAR(64) PRIMARY KEY,
    ma_code         VARCHAR(128) NOT NULL UNIQUE,
    ma_type         VARCHAR(64),
    title           VARCHAR(256) NOT NULL,
    episode_count   INT DEFAULT 1,
    status          VARCHAR(32) NOT NULL DEFAULT 'pending',
    issuer          VARCHAR(128),
    issue_date      DATE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_content_status ON content_registry(status);

-- 哈希绑定表（Hash Binding 镜像）
CREATE TABLE IF NOT EXISTS hash_binding (
    id              BIGSERIAL PRIMARY KEY,
    content_twin_id VARCHAR(64) NOT NULL REFERENCES content_registry(content_twin_id),
    hash_type       VARCHAR(32) NOT NULL,           -- file_sha256 / perceptual / transcoded
    hash_value      VARCHAR(128) NOT NULL,
    merkle_root     VARCHAR(128),
    file_format     VARCHAR(64),
    resolution      VARCHAR(32),
    duration        INT,
    version         VARCHAR(32) NOT NULL DEFAULT 'v1.0',
    parent_hash     VARCHAR(128),                    -- 转码版指向母版哈希（父子关系）
    created_by      VARCHAR(128),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_hash_ctid ON hash_binding(content_twin_id);
CREATE INDEX IF NOT EXISTS idx_hash_value ON hash_binding(hash_value);

-- 三方编码映射表（Identity Mapping 镜像）
CREATE TABLE IF NOT EXISTS identity_mapping (
    id              BIGSERIAL PRIMARY KEY,
    content_twin_id VARCHAR(64) NOT NULL REFERENCES content_registry(content_twin_id),
    party           VARCHAR(32) NOT NULL,            -- cp / reviewer / operator
    party_id        VARCHAR(128) NOT NULL,
    party_name      VARCHAR(128),
    cdn_endpoint    VARCHAR(256),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (content_twin_id, party, party_id)
);
CREATE INDEX IF NOT EXISTS idx_mapping_ctid ON identity_mapping(content_twin_id);
CREATE INDEX IF NOT EXISTS idx_mapping_party ON identity_mapping(party, party_id);

-- 版本变更表（Version History 镜像）
CREATE TABLE IF NOT EXISTS version_history (
    id                BIGSERIAL PRIMARY KEY,
    content_twin_id   VARCHAR(64) NOT NULL REFERENCES content_registry(content_twin_id),
    version           VARCHAR(32) NOT NULL,
    change_reason     TEXT,
    prev_hash         VARCHAR(128),
    new_hash          VARCHAR(128),
    reaudit_required  BOOLEAN NOT NULL DEFAULT TRUE,
    reaudit_status    VARCHAR(32) DEFAULT 'pending',
    affected_episode  INT,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_version_ctid ON version_history(content_twin_id);

-- 链交易记录（异步上链确认）
CREATE TABLE IF NOT EXISTS chain_tx (
    id              BIGSERIAL PRIMARY KEY,
    content_twin_id VARCHAR(64),
    tx_id           VARCHAR(128) NOT NULL UNIQUE,
    method          VARCHAR(64) NOT NULL,            -- issueMA / registerMapping / ...
    status          VARCHAR(16) NOT NULL DEFAULT 'pending', -- pending / confirmed / failed
    block_height    BIGINT,
    payload_hash    VARCHAR(128),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    confirmed_at    TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_tx_ctid ON chain_tx(content_twin_id);
CREATE INDEX IF NOT EXISTS idx_tx_status ON chain_tx(status);

COMMIT;