-- ============================================================
-- 桃育种·单株鉴定升级（手册权威结构落地）
-- 幂等：全部 IF EXISTS / IF NOT EXISTS；契约对齐生成器 ModelMixin
-- 1) 新建 性状观测明细长表 bre_trait_observation
-- 2) bre_tree 加 行向/责任人
-- 3) bre_tree_photo 加 所属鉴定/所属标签页；清理遗留 tree_no
-- 4) bre_tree_evaluation 删固定性状列(迁 trait_observation)、加综合评分、清理遗留 tree_no
-- 注：三张目标表当前均 0 行，无数据迁移负担。
-- ============================================================

-- ── 1) 性状观测明细长表（EAV）────────────────────────────────
CREATE TABLE IF NOT EXISTS bre_trait_observation (
    id            SERIAL PRIMARY KEY,
    evaluation_id INTEGER REFERENCES bre_tree_evaluation(id) ON DELETE CASCADE,
    tree_id       INTEGER REFERENCES bre_tree(id) ON DELETE CASCADE,
    combination_id INTEGER REFERENCES bre_cross_combination(id) ON DELETE CASCADE,
    trait_id      INTEGER NOT NULL REFERENCES bre_trait(id) ON DELETE CASCADE,
    evaluate_year INTEGER,
    value_numeric DOUBLE PRECISION,
    value_text    VARCHAR(255),
    value_date    VARCHAR(20),
    remark        TEXT,
    -- ModelMixin 契约列
    uuid          VARCHAR(64) NOT NULL,
    is_deleted    BOOLEAN NOT NULL DEFAULT false,
    created_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_time  TIMESTAMPTZ
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_bre_trait_observation_uuid ON bre_trait_observation(uuid);
CREATE INDEX IF NOT EXISTS ix_bre_trait_observation_created_deleted ON bre_trait_observation(created_time, is_deleted);
CREATE INDEX IF NOT EXISTS ix_bre_trait_observation_evaluation ON bre_trait_observation(evaluation_id);
CREATE INDEX IF NOT EXISTS ix_bre_trait_observation_tree ON bre_trait_observation(tree_id);
CREATE INDEX IF NOT EXISTS ix_bre_trait_observation_trait ON bre_trait_observation(trait_id);
CREATE INDEX IF NOT EXISTS ix_bre_trait_observation_combination ON bre_trait_observation(combination_id);
-- 同一次鉴定内同一性状仅一条（evaluation_id 为空的历史/散记录不受约束）
CREATE UNIQUE INDEX IF NOT EXISTS uq_bre_trait_observation_eval_trait
    ON bre_trait_observation(evaluation_id, trait_id)
    WHERE evaluation_id IS NOT NULL AND is_deleted = false;

-- ── 2) bre_tree：行向 + 责任人 ──────────────────────────
ALTER TABLE bre_tree ADD COLUMN IF NOT EXISTS row_orientation VARCHAR(50);
ALTER TABLE bre_tree ADD COLUMN IF NOT EXISTS bre_personnel_id INTEGER REFERENCES bre_personnel(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS ix_bre_tree_personnel ON bre_tree(bre_personnel_id);

-- ── 3) bre_tree_photo：所属鉴定 + 所属标签页 ────────────
ALTER TABLE bre_tree_photo ADD COLUMN IF NOT EXISTS evaluation_id INTEGER REFERENCES bre_tree_evaluation(id) ON DELETE CASCADE;
ALTER TABLE bre_tree_photo ADD COLUMN IF NOT EXISTS category VARCHAR(50);
CREATE INDEX IF NOT EXISTS ix_bre_tree_photo_evaluation ON bre_tree_photo(evaluation_id);
ALTER TABLE bre_tree_photo DROP COLUMN IF EXISTS tree_no;

-- ── 4) bre_tree_evaluation：删固定性状列 + 综合评分 ──────
ALTER TABLE bre_tree_evaluation ADD COLUMN IF NOT EXISTS overall_score DOUBLE PRECISION;
ALTER TABLE bre_tree_evaluation DROP COLUMN IF EXISTS growth_vigor;
ALTER TABLE bre_tree_evaluation DROP COLUMN IF EXISTS fruit_shape;
ALTER TABLE bre_tree_evaluation DROP COLUMN IF EXISTS fruit_weight;
ALTER TABLE bre_tree_evaluation DROP COLUMN IF EXISTS sugar_content;
ALTER TABLE bre_tree_evaluation DROP COLUMN IF EXISTS tree_no;