-- ============================================
-- 会员卡类型表
-- ============================================
CREATE TABLE IF NOT EXISTS member_card (
    member_card_id BIGSERIAL PRIMARY KEY,
    member_card_name VARCHAR(100) NOT NULL,
    member_card_type VARCHAR(20) NOT NULL,
    member_card_price DECIMAL(10, 2) NOT NULL,
    member_card_validity_days INTEGER,
    member_card_total_times INTEGER,
    member_card_amount DECIMAL(10, 2),
    member_card_status INTEGER DEFAULT 1 NOT NULL,
    extra_config JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

COMMENT ON TABLE member_card IS '会员卡类型表';
COMMENT ON COLUMN member_card.member_card_id IS '会员卡ID';
COMMENT ON COLUMN member_card.member_card_name IS '会员卡名称';
COMMENT ON COLUMN member_card.member_card_type IS '会员卡类型：TIME_CARD-时长卡, COUNT_CARD-次卡, STORED_VALUE_CARD-储值卡';
COMMENT ON COLUMN member_card.member_card_price IS '会员卡价格';
COMMENT ON COLUMN member_card.member_card_validity_days IS '有效天数（时长卡用）';
COMMENT ON COLUMN member_card.member_card_total_times IS '总次数（次卡用）';
COMMENT ON COLUMN member_card.member_card_amount IS '面额（储值卡用）';
COMMENT ON COLUMN member_card.member_card_status IS '状态：0-下架, 1-上架';
COMMENT ON COLUMN member_card.extra_config IS '扩展配置（JSON格式，用于未来组合卡等）';

-- ============================================
-- 会员卡记录表（会员持有的卡）
-- ============================================
CREATE TABLE IF NOT EXISTS member_card_record (
    member_card_record_id BIGSERIAL PRIMARY KEY,
    member_id BIGINT NOT NULL,
    member_card_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    remaining_times INTEGER DEFAULT 0,
    remaining_amount DECIMAL(10, 2) DEFAULT 0.00,
    expire_time TIMESTAMPTZ,
    source_order_id BIGINT,
    purchase_time TIMESTAMPTZ DEFAULT NOW(),
    version INTEGER DEFAULT 0 NOT NULL,
    card_composition JSONB DEFAULT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
    -- 移除外键约束，改用应用层验证
);

-- 索引优化
CREATE INDEX idx_member_card_record_member_id ON member_card_record(member_id);
CREATE INDEX idx_member_card_record_status ON member_card_record(status);
CREATE INDEX idx_member_card_record_expire_time ON member_card_record(expire_time);
CREATE INDEX idx_member_card_record_member_status ON member_card_record(member_id, status);
CREATE INDEX idx_member_card_record_status_expire ON member_card_record(status, expire_time)
    WHERE status = 'ACTIVE';

COMMENT ON TABLE member_card_record IS '会员卡记录表';
COMMENT ON COLUMN member_card_record.member_card_record_id IS '会员卡记录ID';
COMMENT ON COLUMN member_card_record.member_id IS '会员ID';
COMMENT ON COLUMN member_card_record.member_card_id IS '会员卡类型ID';
COMMENT ON COLUMN member_card_record.status IS '状态：ACTIVE-有效, USED_UP-用完, EXPIRED-过期, REFUNDED-已退款';
COMMENT ON COLUMN member_card_record.remaining_times IS '剩余次数';
COMMENT ON COLUMN member_card_record.remaining_amount IS '剩余金额';
COMMENT ON COLUMN member_card_record.expire_time IS '到期时间';
COMMENT ON COLUMN member_card_record.source_order_id IS '来源订单ID';
COMMENT ON COLUMN member_card_record.purchase_time IS '购买时间';
COMMENT ON COLUMN member_card_record.version IS '乐观锁版本号';
COMMENT ON COLUMN member_card_record.card_composition IS '卡片组成（JSON格式，用于组合卡）';

-- ============================================
-- 会员卡交易流水表
-- ============================================
CREATE TABLE IF NOT EXISTS member_card_transactions (
    id BIGSERIAL PRIMARY KEY,
    member_card_record_id BIGINT NOT NULL,
    member_id BIGINT NOT NULL,
    member_card_id BIGINT NOT NULL,
    operation_type VARCHAR(20) NOT NULL,
    change_amount INTEGER DEFAULT 0,
    change_balance DECIMAL(10, 2) DEFAULT 0.00,
    after_remaining_count INTEGER DEFAULT 0,
    after_remaining_balance DECIMAL(10, 2) DEFAULT 0.00,
    related_biz_type VARCHAR(20),
    source_order_id BIGINT,
    remark VARCHAR(500),
    is_archived BOOLEAN DEFAULT FALSE NOT NULL,
    archived_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
    -- 移除外键约束，改用应用层验证
);

-- 退款申请表
CREATE TABLE IF NOT EXISTS refund_application (
    id BIGSERIAL PRIMARY KEY,
    record_id BIGINT NOT NULL,
    member_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING', -- PENDING/APPROVED/REJECTED/PROCESSING/SUCCESS/FAILED
    reason VARCHAR(500),
    apply_time TIMESTAMPTZ DEFAULT NOW(),
    audit_time TIMESTAMPTZ,
    auditor_id BIGINT,
    audit_remark VARCHAR(500),
    refund_amount DECIMAL(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_refund_application_record_id ON refund_application(record_id);
CREATE INDEX idx_refund_application_status ON refund_application(status);

COMMENT ON TABLE refund_application IS '退款申请表';
COMMENT ON COLUMN refund_application.status IS '状态：PENDING-待审核, APPROVED-已批准, REJECTED-已拒绝, PROCESSING-处理中, SUCCESS-成功, FAILED-失败';


-- 索引优化
CREATE INDEX idx_member_card_transactions_member_id ON member_card_transactions(member_id);
CREATE INDEX idx_member_card_transactions_record_id ON member_card_transactions(member_card_record_id);
CREATE INDEX idx_member_card_transactions_created_at ON member_card_transactions(created_at);
CREATE INDEX idx_member_card_transactions_member_type_time
    ON member_card_transactions(member_id, operation_type, created_at);

COMMENT ON TABLE member_card_transactions IS '会员卡交易流水表';
COMMENT ON COLUMN member_card_transactions.operation_type IS '操作类型：PURCHASE-购买, DEDUCT-扣次/扣费, RENEW-续费, REFUND-退款, EXPIRE-过期';
COMMENT ON COLUMN member_card_transactions.change_amount IS '变动次数';
COMMENT ON COLUMN member_card_transactions.change_balance IS '变动金额';
COMMENT ON COLUMN member_card_transactions.after_remaining_count IS '变动后剩余次数';
COMMENT ON COLUMN member_card_transactions.after_remaining_balance IS '变动后剩余金额';
COMMENT ON COLUMN member_card_transactions.related_biz_type IS '关联业务类型：GROUP_CLASS-团课, PT_CLASS-私教, CHECK_IN-签到';
COMMENT ON COLUMN member_card_transactions.is_archived IS '是否已归档';
COMMENT ON COLUMN member_card_transactions.archived_at IS '归档时间';
