-- 迁移：创建 member_birthday_manual 表（助教手动补录会员生日）
-- 日期：2026-02-22
-- 关联：需求 C2（需求 5.1）— 助教手动补录会员生日
-- 目标库：zqyy_app / test_zqyy_app
-- 影响：新建 member_birthday_manual 表 + 唯一约束 + 索引
-- 幂等：CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS，可重复执行

BEGIN;

-- ============================================================
-- 1. 创建 member_birthday_manual 表
-- ============================================================
CREATE TABLE IF NOT EXISTS member_birthday_manual (
    id                       BIGSERIAL    PRIMARY KEY,
    member_id                BIGINT       NOT NULL,
    birthday_value           DATE         NOT NULL,
    recorded_by_assistant_id BIGINT,
    recorded_by_name         VARCHAR(50),
    recorded_at              TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    source                   VARCHAR(20)  DEFAULT 'assistant',
    site_id                  BIGINT       NOT NULL,
    CONSTRAINT uk_member_birthday_manual
        UNIQUE (member_id, recorded_by_assistant_id)
);

COMMENT ON TABLE member_birthday_manual
    IS '助教手动补录的会员生日信息';

-- ============================================================
-- 2. 创建索引
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_mbd_member
    ON member_birthday_manual (member_id);
CREATE INDEX IF NOT EXISTS idx_mbd_site_id
    ON member_birthday_manual (site_id);

COMMIT;

-- ============================================================
-- 回滚（需手动执行，不在事务内）
-- ============================================================
-- BEGIN;
-- DROP TABLE IF EXISTS member_birthday_manual CASCADE;
-- COMMIT;

-- ============================================================
-- 验证 SQL
-- ============================================================
-- 1. 确认表存在
-- SELECT table_name
--   FROM information_schema.tables
--  WHERE table_schema = 'public'
--    AND table_name = 'member_birthday_manual';
-- 预期：1 行

-- 2. 确认唯一约束 uk_member_birthday_manual 存在
-- SELECT conname, contype
--   FROM pg_constraint
--  WHERE conrelid = 'member_birthday_manual'::regclass
--    AND conname = 'uk_member_birthday_manual';
-- 预期：1 行，contype = 'u'

-- 3. 确认索引 idx_mbd_member 存在
-- SELECT indexname
--   FROM pg_indexes
--  WHERE tablename = 'member_birthday_manual'
--    AND indexname = 'idx_mbd_member';
-- 预期：1 行

-- 4. 确认表注释已设置
-- SELECT obj_description('member_birthday_manual'::regclass, 'pg_class');
-- 预期：'助教手动补录的会员生日信息'

-- 5. 确认列结构完整（8 列）
-- SELECT column_name, data_type, is_nullable
--   FROM information_schema.columns
--  WHERE table_schema = 'public'
--    AND table_name = 'member_birthday_manual'
--  ORDER BY ordinal_position;
-- 预期：id, member_id, birthday_value, recorded_by_assistant_id,
--        recorded_by_name, recorded_at, source, site_id
