-- =============================================================================
-- DWS 数据层完整 DDL
-- 版本: v3.0
-- 创建日期: 2026-02-01
-- 描述: 包含配置表(5张)、助教维度(5张)、客户维度(2张)、财务维度(7张)、订单汇总(1张)
-- =============================================================================

-- 创建 DWS Schema
CREATE SCHEMA IF NOT EXISTS billiards_dws;

-- =============================================================================
-- 第一部分：配置表（5张）
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 1. cfg_performance_tier - 绩效档位配置表
-- 说明: 
--   - 助教绩效档位配置，包含阈值、抽成比例、假期天数
--   - 数据来源：DWS 数据库处理需求.md 第35-41行
--   - 基础课收入 = 基础课小时数 × (客户支付价格 - 专业课抽成)
--   - 附加课收入 = 附加课小时数 × 190 × (1 - 打赏课抽成比例)
--   - 支持按时间生效，通过 effective_from/effective_to 控制历史口径
--   - 新入职定档规则: 月1日0点之后入职的，计算为新入职
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_performance_tier CASCADE;
CREATE TABLE billiards_dws.cfg_performance_tier (
    tier_id             SERIAL PRIMARY KEY,           -- 档位ID（自增）
    tier_code           VARCHAR(20) NOT NULL,         -- 档位代码（如 T0-T4）
    tier_name           VARCHAR(50) NOT NULL,         -- 档位名称
    tier_level          INTEGER NOT NULL,             -- 档位等级（数字越大档位越高）
    min_hours           NUMERIC(10,2) NOT NULL,       -- 最低业绩小时数阈值（>=）
    max_hours           NUMERIC(10,2),                -- 最高业绩小时数阈值（<，NULL表示无上限）
    base_deduction      NUMERIC(10,2) NOT NULL DEFAULT 0,  -- 专业课抽成（元/小时），球房从基础课扣除
    bonus_deduction_ratio NUMERIC(5,4) NOT NULL DEFAULT 0, -- 打赏课抽成比例（0-1），球房从附加课扣除
    vacation_days       INTEGER NOT NULL DEFAULT 0,   -- 次月可休假天数
    vacation_unlimited  BOOLEAN NOT NULL DEFAULT FALSE, -- 是否休假自由（最高档特殊）
    is_new_hire_tier    BOOLEAN NOT NULL DEFAULT FALSE, -- 是否为新入职专用档位（预留）
    effective_from      DATE NOT NULL DEFAULT '2000-01-01', -- 生效起始日期（含）
    effective_to        DATE NOT NULL DEFAULT '9999-12-31', -- 生效截止日期（含）
    description         TEXT,                         -- 档位说明
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_cfg_performance_tier UNIQUE (tier_code, effective_from)
);

COMMENT ON TABLE billiards_dws.cfg_performance_tier IS '绩效档位配置表：定义绩效阈值、抽成比例、假期，数据来源DWS数据库处理需求.md';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.tier_code IS '档位代码：按规则表配置（如 T0-T4）';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.min_hours IS '业绩小时数下限（含），基础课+附加课总和';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.max_hours IS '业绩小时数上限（不含），NULL表示无上限';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.base_deduction IS '专业课抽成（元/小时）：球房从基础课每小时扣除的金额';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.bonus_deduction_ratio IS '打赏课抽成比例：球房从附加课收入中扣除的比例，如0.35表示35%';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.vacation_days IS '次月可休假天数';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.vacation_unlimited IS '休假自由标记：最高档为TRUE';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.is_new_hire_tier IS '新入职专用档位标记（预留，当前规则不使用）';
COMMENT ON COLUMN billiards_dws.cfg_performance_tier.effective_from IS '规则生效起始日期，用于历史月份正确取档';

-- 创建查询索引
CREATE INDEX idx_cfg_performance_tier_effective 
    ON billiards_dws.cfg_performance_tier (effective_from, effective_to);


-- -----------------------------------------------------------------------------
-- 2. cfg_assistant_level_price - 助教等级定价表
-- 说明:
--   - 助教等级（初级/中级/高级/星级）对应的基础课和附加课单价
--   - 支持按时间生效，便于历史月份薪资计算使用历史单价
--   - SCD2口径: 助教等级来自dim_assistant，取数时需按有效期as-of join
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_assistant_level_price CASCADE;
CREATE TABLE billiards_dws.cfg_assistant_level_price (
    price_id            SERIAL PRIMARY KEY,           -- 定价ID（自增）
    level_code          INTEGER NOT NULL,             -- 等级代码（来自dim_assistant.assistant_level）
    level_name          VARCHAR(20) NOT NULL,         -- 等级名称（初级/中级/高级/星级）
    base_course_price   NUMERIC(10,2) NOT NULL,       -- 基础课单价（元/小时）
    bonus_course_price  NUMERIC(10,2) NOT NULL,       -- 附加课单价（元/小时），固定190元
    effective_from      DATE NOT NULL DEFAULT '2000-01-01', -- 生效起始日期（含）
    effective_to        DATE NOT NULL DEFAULT '9999-12-31', -- 生效截止日期（含）
    description         TEXT,                         -- 说明
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_cfg_assistant_level_price UNIQUE (level_code, effective_from)
);

COMMENT ON TABLE billiards_dws.cfg_assistant_level_price IS '助教等级定价表：初级/中级/高级/星级的基础课和附加课单价，支持按时间生效';
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.level_code IS '等级代码：8=初级, 10=中级, 20=高级, 30=星级, 40=金牌';
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.base_course_price IS '基础课（陪打/PD）单价，按等级不同';
COMMENT ON COLUMN billiards_dws.cfg_assistant_level_price.bonus_course_price IS '附加课（超休/CX）单价，固定190元/小时';

CREATE INDEX idx_cfg_assistant_level_price_effective 
    ON billiards_dws.cfg_assistant_level_price (effective_from, effective_to);


-- -----------------------------------------------------------------------------
-- 3. cfg_bonus_rules - 奖金规则配置表
-- 说明:
--   - 包含冲刺奖金（按小时阈值，历史/可选）和Top3奖金（按排名）
--   - Top3排名口径: 按绩效总小时数，如遇并列则都算（如2个第一，则记为2个第一，一个第三）
--   - 冲刺奖金: 按规则表配置，不累计取最高档
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_bonus_rules CASCADE;
CREATE TABLE billiards_dws.cfg_bonus_rules (
    rule_id             SERIAL PRIMARY KEY,           -- 规则ID（自增）
    rule_type           VARCHAR(20) NOT NULL,         -- 规则类型: SPRINT（冲刺奖金）, TOP_RANK（Top排名奖金）
    rule_code           VARCHAR(30) NOT NULL,         -- 规则代码: SPRINT_190, SPRINT_220, TOP_1, TOP_2, TOP_3
    rule_name           VARCHAR(50) NOT NULL,         -- 规则名称
    threshold_hours     NUMERIC(10,2),                -- 小时数阈值（冲刺奖金用）
    rank_position       INTEGER,                      -- 排名位置（Top奖金用）
    bonus_amount        NUMERIC(12,2) NOT NULL,       -- 奖金金额（元）
    is_cumulative       BOOLEAN NOT NULL DEFAULT FALSE, -- 是否可累计（冲刺奖金为FALSE，取最高档）
    priority            INTEGER NOT NULL DEFAULT 0,   -- 优先级（数字越大优先级越高，用于非累计时取最高）
    effective_from      DATE NOT NULL DEFAULT '2000-01-01', -- 生效起始日期（含）
    effective_to        DATE NOT NULL DEFAULT '9999-12-31', -- 生效截止日期（含）
    description         TEXT,                         -- 说明
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_cfg_bonus_rules UNIQUE (rule_type, rule_code, effective_from)
);

COMMENT ON TABLE billiards_dws.cfg_bonus_rules IS '奖金规则配置表：冲刺奖金(按小时阈值)和Top3奖金(按排名)，支持按时间生效';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.rule_type IS '规则类型：SPRINT=冲刺奖金, TOP_RANK=Top排名奖金';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.is_cumulative IS '是否累计：冲刺奖金不累计取最高档，Top奖金独立发放';
COMMENT ON COLUMN billiards_dws.cfg_bonus_rules.priority IS '优先级：非累计时用于取最高档奖金';

CREATE INDEX idx_cfg_bonus_rules_effective 
    ON billiards_dws.cfg_bonus_rules (effective_from, effective_to);
CREATE INDEX idx_cfg_bonus_rules_type 
    ON billiards_dws.cfg_bonus_rules (rule_type);


-- -----------------------------------------------------------------------------
-- 4. cfg_area_category - 台区分类映射表
-- 说明:
--   - 将 dim_table.site_table_area_name 映射到财务报表区域分类
--   - 数据来源: BD_manual_dim_table.md 中的实际台区分布
--   - 分类设计:
--     * BILLIARD: 台球散台（A区/B区/C区/TV台）
--     * BILLIARD_VIP: 台球VIP包厢
--     * SNOOKER: 斯诺克区
--     * MAHJONG: 麻将棋牌（麻将房/M7/M8/666/发财）
--     * KTV: K歌娱乐（K包/k包活动区/幸会158）
--     * SPECIAL: 特殊（补时长）
--     * OTHER: 其他
--   - 映射规则: 精确匹配 > 模糊匹配 > 默认兜底
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_area_category CASCADE;
CREATE TABLE billiards_dws.cfg_area_category (
    category_id         SERIAL PRIMARY KEY,           -- 分类ID（自增）
    source_area_name    VARCHAR(100) NOT NULL,        -- 源区域名称（来自dim_table.site_table_area_name）
    category_code       VARCHAR(20) NOT NULL,         -- 分类代码: BILLIARD, BILLIARD_VIP, SNOOKER, MAHJONG, KTV, SPECIAL, OTHER
    category_name       VARCHAR(50) NOT NULL,         -- 分类名称: 台球散台、台球VIP、斯诺克、麻将棋牌、K歌娱乐、补时长、其他
    match_type          VARCHAR(10) NOT NULL DEFAULT 'EXACT', -- 匹配类型: EXACT(精确), LIKE(模糊), DEFAULT(兜底)
    match_priority      INTEGER NOT NULL DEFAULT 100, -- 匹配优先级（数字越小优先级越高）
    is_active           BOOLEAN NOT NULL DEFAULT TRUE, -- 是否启用
    description         TEXT,                         -- 说明
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_cfg_area_category UNIQUE (source_area_name)
);

COMMENT ON TABLE billiards_dws.cfg_area_category IS '台区分类映射表：将dim_table区域名称映射到财务报表分类，基于BD_manual_dim_table.md实际数据';
COMMENT ON COLUMN billiards_dws.cfg_area_category.category_code IS '分类代码：BILLIARD台球散台, BILLIARD_VIP台球VIP, SNOOKER斯诺克, MAHJONG麻将, KTV K歌, SPECIAL特殊, OTHER其他';
COMMENT ON COLUMN billiards_dws.cfg_area_category.match_type IS '匹配类型：EXACT精确匹配, LIKE模糊匹配(用于包含关系), DEFAULT兜底';
COMMENT ON COLUMN billiards_dws.cfg_area_category.match_priority IS '匹配优先级：多条匹配时取优先级最高的';

CREATE INDEX idx_cfg_area_category_code ON billiards_dws.cfg_area_category (category_code);


-- -----------------------------------------------------------------------------
-- 5. cfg_skill_type - 技能→课程类型映射表
-- 说明:
--   - 将 skill_id 映射到课程类型（基础课/附加课）
--   - 基础课（陪打/PD）: skill_id = 2791903611396869
--   - 附加课（超休/CX）: skill_id = 2807440316432197
--   - 避免依赖 skill_name 文本匹配，使用配置表管理
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_skill_type CASCADE;
CREATE TABLE billiards_dws.cfg_skill_type (
    skill_type_id       SERIAL PRIMARY KEY,           -- 映射ID（自增）
    skill_id            BIGINT NOT NULL,              -- 技能ID（来自dwd_assistant_service_log.skill_id）
    skill_name          VARCHAR(50),                  -- 技能名称（仅用于展示和校验）
    course_type_code    VARCHAR(10) NOT NULL,         -- 课程类型代码: BASE（基础课）, BONUS（附加课）
    course_type_name    VARCHAR(20) NOT NULL,         -- 课程类型名称: 基础课/陪打, 附加课/超休
    is_active           BOOLEAN NOT NULL DEFAULT TRUE, -- 是否启用
    description         TEXT,                         -- 说明
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_cfg_skill_type UNIQUE (skill_id)
);

COMMENT ON TABLE billiards_dws.cfg_skill_type IS '技能→课程类型映射表：将skill_id映射到基础课/附加课，避免依赖skill_name文本';
COMMENT ON COLUMN billiards_dws.cfg_skill_type.course_type_code IS '课程类型：BASE=基础课(陪打), BONUS=附加课(超休)';

CREATE INDEX idx_cfg_skill_type_course ON billiards_dws.cfg_skill_type (course_type_code);


-- =============================================================================
-- 第二部分：助教维度（5张）
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 6. dws_assistant_daily_detail - 助教日度业绩明细表
-- 说明:
--   - 以"助教+日期"为粒度，汇总每日业绩明细
--   - 数据来源: dwd_assistant_service_log + dwd_assistant_trash_event（排除废除记录）
--   - 更新频率: 每小时增量更新
--   - 时间分层: 通过 stat_date 筛选实现近2天/近1月/近3月/全量
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_daily_detail CASCADE;
CREATE TABLE billiards_dws.dws_assistant_daily_detail (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    assistant_id        BIGINT NOT NULL,              -- 助教ID（dim_assistant.site_assistant_id）
    assistant_nickname  VARCHAR(50),                  -- 助教花名（冗余，便于查询展示）
    stat_date           DATE NOT NULL,                -- 统计日期
    -- 等级信息（as-of取值，使用统计日期时点的等级）
    assistant_level_code INTEGER,                     -- 助教等级代码（统计日当日生效的等级）
    assistant_level_name VARCHAR(20),                 -- 助教等级名称
    -- 业绩统计
    total_service_count INTEGER NOT NULL DEFAULT 0,   -- 总服务次数
    base_service_count  INTEGER NOT NULL DEFAULT 0,   -- 基础课服务次数
    bonus_service_count INTEGER NOT NULL DEFAULT 0,   -- 附加课服务次数
    room_service_count  INTEGER NOT NULL DEFAULT 0,   -- 包厢/房间服务次数
    total_seconds       INTEGER NOT NULL DEFAULT 0,   -- 总计费时长（秒）
    base_seconds        INTEGER NOT NULL DEFAULT 0,   -- 基础课计费时长（秒）
    bonus_seconds       INTEGER NOT NULL DEFAULT 0,   -- 附加课计费时长（秒）
    room_seconds        INTEGER NOT NULL DEFAULT 0,   -- 包厢/房间计费时长（秒）
    total_hours         NUMERIC(10,2) NOT NULL DEFAULT 0, -- 总计费小时数（total_seconds/3600）
    base_hours          NUMERIC(10,2) NOT NULL DEFAULT 0, -- 基础课小时数
    bonus_hours         NUMERIC(10,2) NOT NULL DEFAULT 0, -- 附加课小时数
    room_hours          NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间小时数
    -- 金额统计
    total_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 总计费金额（元）
    base_ledger_amount  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 基础课计费金额
    bonus_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 附加课计费金额
    room_ledger_amount  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 包厢/房间计费金额
    -- 客户与台桌统计
    unique_customers    INTEGER NOT NULL DEFAULT 0,   -- 服务客户数（去重）
    unique_tables       INTEGER NOT NULL DEFAULT 0,   -- 服务台桌数（去重）
    -- 废除记录统计
    trashed_seconds     INTEGER NOT NULL DEFAULT 0,   -- 被废除的服务时长（秒）
    trashed_count       INTEGER NOT NULL DEFAULT 0,   -- 被废除的服务次数
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_assistant_daily UNIQUE (site_id, assistant_id, stat_date)
);

COMMENT ON TABLE billiards_dws.dws_assistant_daily_detail IS '助教日度业绩明细：按助教+日期汇总服务次数、时长、金额，支持时间分层查询';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.assistant_level_code IS 'SCD2口径：取stat_date当日生效的助教等级';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.trashed_seconds IS '被废除时长：来自dwd_assistant_trash_event，影响有效业绩';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_service_count IS '包厢/房间服务次数';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_seconds IS '包厢/房间计费时长（秒）';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_hours IS '包厢/房间计费小时数';
COMMENT ON COLUMN billiards_dws.dws_assistant_daily_detail.room_ledger_amount IS '包厢/房间计费金额';

-- 时间分层查询索引（核心）
CREATE INDEX idx_dws_assistant_daily_date ON billiards_dws.dws_assistant_daily_detail (stat_date);
CREATE INDEX idx_dws_assistant_daily_asst_date ON billiards_dws.dws_assistant_daily_detail (assistant_id, stat_date);
CREATE INDEX idx_dws_assistant_daily_site_date ON billiards_dws.dws_assistant_daily_detail (site_id, stat_date);


-- -----------------------------------------------------------------------------
-- 7. dws_assistant_monthly_summary - 助教月度业绩汇总表
-- 说明:
--   - 以"助教+月份"为粒度，汇总月度业绩及档位计算
--   - 数据来源: dws_assistant_daily_detail 聚合 + cfg_performance_tier 档位匹配
--   - 更新频率: 每日更新当月数据
--   - 新入职判断: 入职日期在月1日0点之后则为新入职
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_monthly_summary CASCADE;
CREATE TABLE billiards_dws.dws_assistant_monthly_summary (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    assistant_id        BIGINT NOT NULL,              -- 助教ID
    assistant_nickname  VARCHAR(50),                  -- 助教花名
    stat_month          DATE NOT NULL,                -- 统计月份（月第一天，如2026-01-01）
    -- 等级信息（as-of取值，使用月末时点的等级）
    assistant_level_code INTEGER,                     -- 助教等级代码
    assistant_level_name VARCHAR(20),                 -- 助教等级名称
    -- 入职信息
    hire_date           DATE,                         -- 入职日期（来自dim_assistant）
    is_new_hire         BOOLEAN NOT NULL DEFAULT FALSE, -- 是否新入职（入职日期 >= 统计月1日0点）
    -- 月度业绩汇总
    work_days           INTEGER NOT NULL DEFAULT 0,   -- 有服务天数
    total_service_count INTEGER NOT NULL DEFAULT 0,   -- 总服务次数
    base_service_count  INTEGER NOT NULL DEFAULT 0,   -- 基础课服务次数
    bonus_service_count INTEGER NOT NULL DEFAULT 0,   -- 附加课服务次数
    room_service_count  INTEGER NOT NULL DEFAULT 0,   -- 包厢/房间服务次数
    total_hours         NUMERIC(10,2) NOT NULL DEFAULT 0, -- 总计费小时数
    base_hours          NUMERIC(10,2) NOT NULL DEFAULT 0, -- 基础课小时数
    bonus_hours         NUMERIC(10,2) NOT NULL DEFAULT 0, -- 附加课小时数
    room_hours          NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间小时数
    -- 有效业绩（扣除废除记录后）
    effective_hours     NUMERIC(10,2) NOT NULL DEFAULT 0, -- 有效业绩小时数（影响档位）
    trashed_hours       NUMERIC(10,2) NOT NULL DEFAULT 0, -- 被废除小时数
    -- 金额统计
    total_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 总计费金额
    base_ledger_amount  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 基础课计费金额
    bonus_ledger_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 附加课计费金额
    room_ledger_amount  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 包厢/房间计费金额
    -- 客户统计
    unique_customers    INTEGER NOT NULL DEFAULT 0,   -- 月度服务客户数（去重）
    unique_tables       INTEGER NOT NULL DEFAULT 0,   -- 月度服务台桌数（去重）
    avg_service_seconds NUMERIC(10,2) NOT NULL DEFAULT 0, -- 平均单次服务时长（秒）
    -- 档位信息（根据有效业绩匹配）
    tier_id             INTEGER,                      -- 匹配的档位ID
    tier_code           VARCHAR(20),                  -- 档位代码
    tier_name           VARCHAR(50),                  -- 档位名称
    -- 排名信息（用于Top3奖金，按有效业绩小时数排名）
    rank_by_hours       INTEGER,                      -- 月度排名（按effective_hours降序）
    rank_with_ties      INTEGER,                      -- 考虑并列的排名（如2个第一则都是1）
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_assistant_monthly UNIQUE (site_id, assistant_id, stat_month)
);

COMMENT ON TABLE billiards_dws.dws_assistant_monthly_summary IS '助教月度业绩汇总：按助教+月份汇总业绩、档位匹配、排名计算';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.stat_month IS '统计月份：存储月第一天日期，如2026-01-01';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.is_new_hire IS '新入职标记：入职日期>=月1日0点则为TRUE';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.effective_hours IS '有效业绩：total_hours - trashed_hours，用于档位匹配';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.rank_with_ties IS 'Top3排名口径：如遇并列都算，如2个第一则都是1，下一个是3';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.room_service_count IS '包厢/房间服务次数（月度汇总）';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.room_hours IS '包厢/房间服务小时数（月度汇总）';
COMMENT ON COLUMN billiards_dws.dws_assistant_monthly_summary.room_ledger_amount IS '包厢/房间计费金额（月度汇总）';

CREATE INDEX idx_dws_assistant_monthly_month ON billiards_dws.dws_assistant_monthly_summary (stat_month);
CREATE INDEX idx_dws_assistant_monthly_asst ON billiards_dws.dws_assistant_monthly_summary (assistant_id, stat_month);
CREATE INDEX idx_dws_assistant_monthly_tier ON billiards_dws.dws_assistant_monthly_summary (tier_code);


-- -----------------------------------------------------------------------------
-- 8. dws_assistant_customer_stats - 助教服务客户统计表
-- 说明:
--   - 以"助教+客户"为粒度，统计服务关系和滚动窗口指标
--   - 滚动窗口: 7/10/15/30/60/90天，从统计日期往前计算
--   - 更新频率: 每日更新
--   - 散客处理: member_id=0 不进入此表统计
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_customer_stats CASCADE;
CREATE TABLE billiards_dws.dws_assistant_customer_stats (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    assistant_id        BIGINT NOT NULL,              -- 助教ID
    assistant_nickname  VARCHAR(50),                  -- 助教花名
    member_id           BIGINT NOT NULL,              -- 客户ID（member_id=0散客不入此表）
    member_nickname     VARCHAR(100),                 -- 客户昵称
    member_mobile       VARCHAR(20),                  -- 客户手机号（脱敏）
    stat_date           DATE NOT NULL,                -- 统计基准日期
    -- 全量累计统计
    first_service_date  DATE,                         -- 首次服务日期
    last_service_date   DATE,                         -- 最近服务日期
    total_service_count INTEGER NOT NULL DEFAULT 0,   -- 累计服务次数
    total_service_hours NUMERIC(10,2) NOT NULL DEFAULT 0, -- 累计服务小时数
    total_service_amount NUMERIC(12,2) NOT NULL DEFAULT 0, -- 累计服务金额
    -- 滚动窗口统计（近N天）
    service_count_7d    INTEGER NOT NULL DEFAULT 0,   -- 近7天服务次数
    service_count_10d   INTEGER NOT NULL DEFAULT 0,   -- 近10天服务次数
    service_count_15d   INTEGER NOT NULL DEFAULT 0,   -- 近15天服务次数
    service_count_30d   INTEGER NOT NULL DEFAULT 0,   -- 近30天服务次数
    service_count_60d   INTEGER NOT NULL DEFAULT 0,   -- 近60天服务次数
    service_count_90d   INTEGER NOT NULL DEFAULT 0,   -- 近90天服务次数
    service_hours_7d    NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近7天服务小时数
    service_hours_10d   NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近10天服务小时数
    service_hours_15d   NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近15天服务小时数
    service_hours_30d   NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近30天服务小时数
    service_hours_60d   NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近60天服务小时数
    service_hours_90d   NUMERIC(10,2) NOT NULL DEFAULT 0, -- 近90天服务小时数
    service_amount_7d   NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近7天服务金额
    service_amount_10d  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近10天服务金额
    service_amount_15d  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近15天服务金额
    service_amount_30d  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近30天服务金额
    service_amount_60d  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近60天服务金额
    service_amount_90d  NUMERIC(12,2) NOT NULL DEFAULT 0, -- 近90天服务金额
    -- 活跃度指标
    days_since_last     INTEGER,                      -- 距离最近服务的天数
    is_active_7d        BOOLEAN NOT NULL DEFAULT FALSE, -- 近7天是否活跃
    is_active_30d       BOOLEAN NOT NULL DEFAULT FALSE, -- 近30天是否活跃
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_assistant_customer UNIQUE (site_id, assistant_id, member_id, stat_date)
);

COMMENT ON TABLE billiards_dws.dws_assistant_customer_stats IS '助教服务客户统计：按助教+客户统计服务关系和滚动窗口指标';
COMMENT ON COLUMN billiards_dws.dws_assistant_customer_stats.member_id IS '客户ID：member_id=0散客不入此表';
COMMENT ON COLUMN billiards_dws.dws_assistant_customer_stats.service_count_7d IS '滚动窗口：从stat_date往前7天的服务次数';

CREATE INDEX idx_dws_assistant_customer_date ON billiards_dws.dws_assistant_customer_stats (stat_date);
CREATE INDEX idx_dws_assistant_customer_asst ON billiards_dws.dws_assistant_customer_stats (assistant_id, stat_date);
CREATE INDEX idx_dws_assistant_customer_member ON billiards_dws.dws_assistant_customer_stats (member_id, stat_date);


-- -----------------------------------------------------------------------------
-- 9. dws_assistant_salary_calc - 助教工资计算详情表
-- 说明:
--   - 以"助教+月份"为粒度，计算月度工资明细
--   - 数据来源: dws_assistant_monthly_summary + cfg_* 配置表
--   - 计算公式（来自DWS数据库处理需求.md）:
--     * 基础课收入 = 基础课小时数 × (客户支付价格 - 专业课抽成)
--     * 附加课收入 = 附加课小时数 × 190 × (1 - 打赏课抽成比例)
--     * 包厢课收入 = 包厢课小时数 × (138 - 专业课抽成)
--     * 应发工资 = 课时收入 + 奖金
--   - 更新频率: 月初计算上月工资
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_salary_calc CASCADE;
CREATE TABLE billiards_dws.dws_assistant_salary_calc (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    assistant_id        BIGINT NOT NULL,              -- 助教ID
    assistant_nickname  VARCHAR(50),                  -- 助教花名
    salary_month        DATE NOT NULL,                -- 工资月份（月第一天）
    -- 助教信息快照
    assistant_level_code INTEGER,                     -- 助教等级代码（8/10/20/30/40）
    assistant_level_name VARCHAR(20),                 -- 助教等级名称（初级/中级/高级/星级）
    hire_date           DATE,                         -- 入职日期
    is_new_hire         BOOLEAN NOT NULL DEFAULT FALSE, -- 是否新入职
    -- 业绩数据（来自monthly_summary）
    effective_hours     NUMERIC(10,2) NOT NULL DEFAULT 0, -- 有效业绩小时数（基础课+附加课-废除）
    base_hours          NUMERIC(10,2) NOT NULL DEFAULT 0, -- 基础课/专业课小时数
    bonus_hours         NUMERIC(10,2) NOT NULL DEFAULT 0, -- 附加课/打赏课小时数
    room_hours          NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间服务小时数
    -- 档位信息（来自cfg_performance_tier）
    tier_id             INTEGER,                      -- 档位ID
    tier_code           VARCHAR(20),                  -- 档位代码（如 T0-T4）
    tier_name           VARCHAR(50),                  -- 档位名称
    -- 排名信息
    rank_with_ties      INTEGER,                      -- 月度排名（考虑并列，用于Top3奖金）
    -- 定价信息（SCD2口径，取salary_month对应的值）
    base_course_price   NUMERIC(10,2) NOT NULL DEFAULT 0,   -- 基础课客户支付价格（98/108/118/138）
    bonus_course_price  NUMERIC(10,2) NOT NULL DEFAULT 0,   -- 附加课客户支付价格（固定190）
    base_deduction      NUMERIC(10,2) NOT NULL DEFAULT 0,   -- 专业课抽成（元/小时）
    bonus_deduction_ratio NUMERIC(5,4) NOT NULL DEFAULT 0,  -- 打赏课抽成比例（0-1）
    -- 工资计算明细
    base_income         NUMERIC(12,2) NOT NULL DEFAULT 0, -- 基础课收入 = base_hours × (base_course_price - base_deduction)
    bonus_income        NUMERIC(12,2) NOT NULL DEFAULT 0, -- 附加课收入 = bonus_hours × 190 × (1 - bonus_deduction_ratio)
    room_income         NUMERIC(12,2) NOT NULL DEFAULT 0, -- 包厢/房间收入
    total_course_income NUMERIC(12,2) NOT NULL DEFAULT 0, -- 课时收入合计
    -- 奖金
    sprint_bonus        NUMERIC(12,2) NOT NULL DEFAULT 0, -- 冲刺奖金（按规则表配置，不累计取最高）
    top_rank_bonus      NUMERIC(12,2) NOT NULL DEFAULT 0, -- Top3排名奖金（1st:1000, 2nd:600, 3rd:400）
    recharge_commission NUMERIC(12,2) NOT NULL DEFAULT 0, -- 充值提成（来自dws_assistant_recharge_commission）
    other_bonus         NUMERIC(12,2) NOT NULL DEFAULT 0, -- 其他奖金（手动调整）
    total_bonus         NUMERIC(12,2) NOT NULL DEFAULT 0, -- 奖金合计
    -- 工资汇总
    gross_salary        NUMERIC(12,2) NOT NULL DEFAULT 0, -- 应发工资 = total_course_income + total_bonus
    -- 假期信息
    vacation_days       INTEGER NOT NULL DEFAULT 0,   -- 次月可休假天数
    vacation_unlimited  BOOLEAN NOT NULL DEFAULT FALSE, -- 休假自由标记（最高档为TRUE）
    -- 备注
    calc_notes          TEXT,                         -- 计算备注（异常说明等）
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_assistant_salary UNIQUE (site_id, assistant_id, salary_month)
);

COMMENT ON TABLE billiards_dws.dws_assistant_salary_calc IS '助教工资计算详情：按DWS数据库处理需求.md公式计算，包含课时收入和各类奖金';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.base_deduction IS '专业课抽成（元/小时）：档位决定，球房从基础课每小时扣除';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.bonus_deduction_ratio IS '打赏课抽成比例：档位决定，球房从附加课收入扣除的比例';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.base_income IS '基础课收入 = 小时数 × (客户价格 - 专业课抽成)，如170×(108-13)=16150';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.bonus_income IS '附加课收入 = 小时数 × 190 × (1 - 抽成比例)，如15×190×0.65=1852.5';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.room_hours IS '包厢/房间服务小时数（来自monthly_summary）';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.room_income IS '包厢/房间收入（包厢课统一138元/小时）';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.sprint_bonus IS '冲刺奖金：按规则表配置，不累计取最高档';
COMMENT ON COLUMN billiards_dws.dws_assistant_salary_calc.top_rank_bonus IS 'Top3奖金：按effective_hours排名，并列都算（如2个第1则无第2）';

CREATE INDEX idx_dws_assistant_salary_month ON billiards_dws.dws_assistant_salary_calc (salary_month);
CREATE INDEX idx_dws_assistant_salary_asst ON billiards_dws.dws_assistant_salary_calc (assistant_id, salary_month);


-- -----------------------------------------------------------------------------
-- 10. dws_assistant_recharge_commission - 助教充值提成表
-- 说明:
--   - 以"助教+月份+充值订单"为粒度，记录充值提成
--   - 数据来源: Excel手动导入
--   - 导入字段: 月份、充值订单金额、助教获得的提成金额
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_recharge_commission CASCADE;
CREATE TABLE billiards_dws.dws_assistant_recharge_commission (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    assistant_id        BIGINT NOT NULL,              -- 助教ID
    assistant_nickname  VARCHAR(50),                  -- 助教花名
    commission_month    DATE NOT NULL,                -- 提成月份（月第一天）
    -- 充值订单关联
    recharge_order_id   BIGINT,                       -- 充值订单ID（可选，关联dwd_recharge_order）
    recharge_order_no   VARCHAR(50),                  -- 充值订单号
    -- 提成信息
    recharge_amount     NUMERIC(12,2) NOT NULL DEFAULT 0, -- 充值订单金额
    commission_amount   NUMERIC(12,2) NOT NULL DEFAULT 0, -- 提成金额
    commission_ratio    NUMERIC(5,4),                 -- 提成比例（可选，反算或导入）
    -- 导入信息
    import_batch_no     VARCHAR(50),                  -- 导入批次号
    import_file_name    VARCHAR(200),                 -- 导入文件名
    import_time         TIMESTAMPTZ,                  -- 导入时间
    import_user         VARCHAR(50),                  -- 导入操作人
    -- 备注
    remark              TEXT,                         -- 备注
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE billiards_dws.dws_assistant_recharge_commission IS '助教充值提成：Excel导入，记录月份、充值金额、提成金额';
COMMENT ON COLUMN billiards_dws.dws_assistant_recharge_commission.commission_month IS '提成月份：导入表格中明确的月份';
COMMENT ON COLUMN billiards_dws.dws_assistant_recharge_commission.import_batch_no IS '导入批次号：用于追溯和去重';

CREATE INDEX idx_dws_assistant_commission_month ON billiards_dws.dws_assistant_recharge_commission (commission_month);
CREATE INDEX idx_dws_assistant_commission_asst ON billiards_dws.dws_assistant_recharge_commission (assistant_id, commission_month);
CREATE INDEX idx_dws_assistant_commission_batch ON billiards_dws.dws_assistant_recharge_commission (import_batch_no);


-- =============================================================================
-- 第三部分：客户维度（2张）
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 11. dws_member_consumption_summary - 会员消费汇总表
-- 说明:
--   - 以"会员"为粒度，统计消费行为和滚动窗口指标
--   - 散客处理: member_id=0 不进入此表
--   - 滚动窗口: 7/10/15/30/60/90天
--   - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_consumption_summary CASCADE;
CREATE TABLE billiards_dws.dws_member_consumption_summary (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    member_id           BIGINT NOT NULL,              -- 会员ID（member_id=0散客不入此表）
    stat_date           DATE NOT NULL,                -- 统计基准日期
    -- 会员基本信息快照
    member_nickname     VARCHAR(100),                 -- 会员昵称
    member_mobile       VARCHAR(20),                  -- 手机号（脱敏）
    card_grade_name     VARCHAR(50),                  -- 卡等级名称
    register_date       DATE,                         -- 注册日期
    -- 全量累计统计
    first_consume_date  DATE,                         -- 首次消费日期
    last_consume_date   DATE,                         -- 最近消费日期
    total_visit_count   INTEGER NOT NULL DEFAULT 0,   -- 累计到店次数
    total_consume_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计消费金额
    total_recharge_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计充值金额
    total_table_fee     NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计台费
    total_goods_amount  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计商品消费
    total_assistant_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 累计助教服务消费
    -- 滚动窗口统计（近N天）
    visit_count_7d      INTEGER NOT NULL DEFAULT 0,   -- 近7天到店次数
    visit_count_10d     INTEGER NOT NULL DEFAULT 0,   -- 近10天到店次数
    visit_count_15d     INTEGER NOT NULL DEFAULT 0,   -- 近15天到店次数
    visit_count_30d     INTEGER NOT NULL DEFAULT 0,   -- 近30天到店次数
    visit_count_60d     INTEGER NOT NULL DEFAULT 0,   -- 近60天到店次数
    visit_count_90d     INTEGER NOT NULL DEFAULT 0,   -- 近90天到店次数
    consume_amount_7d   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近7天消费金额
    consume_amount_10d  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近10天消费金额
    consume_amount_15d  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近15天消费金额
    consume_amount_30d  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近30天消费金额
    consume_amount_60d  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近60天消费金额
    consume_amount_90d  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近90天消费金额
    -- 会员卡余额快照
    cash_card_balance   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡余额（现金卡）
    gift_card_balance   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡余额（台费卡+酒水卡+活动券）
    total_card_balance  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 总卡余额
    -- 活跃度指标
    days_since_last     INTEGER,                      -- 距离最近消费的天数
    is_active_7d        BOOLEAN NOT NULL DEFAULT FALSE, -- 近7天是否活跃
    is_active_30d       BOOLEAN NOT NULL DEFAULT FALSE, -- 近30天是否活跃
    is_active_90d       BOOLEAN NOT NULL DEFAULT FALSE, -- 近90天是否活跃
    -- 客户分层标签
    customer_tier       VARCHAR(20),                  -- 客户分层（高价值/中等/低活跃/流失）
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_member_consumption UNIQUE (site_id, member_id, stat_date)
);

COMMENT ON TABLE billiards_dws.dws_member_consumption_summary IS '会员消费汇总：按会员统计消费行为和滚动窗口指标，散客不入此表';
COMMENT ON COLUMN billiards_dws.dws_member_consumption_summary.member_id IS '会员ID：member_id=0散客不统计';
COMMENT ON COLUMN billiards_dws.dws_member_consumption_summary.cash_card_balance IS '储值卡余额：card_type_id=2793249295533893';
COMMENT ON COLUMN billiards_dws.dws_member_consumption_summary.gift_card_balance IS '赠送卡余额：台费卡+酒水卡+活动抵用券';

CREATE INDEX idx_dws_member_consumption_date ON billiards_dws.dws_member_consumption_summary (stat_date);
CREATE INDEX idx_dws_member_consumption_member ON billiards_dws.dws_member_consumption_summary (member_id, stat_date);
CREATE INDEX idx_dws_member_consumption_tier ON billiards_dws.dws_member_consumption_summary (customer_tier);


-- -----------------------------------------------------------------------------
-- 12. dws_member_visit_detail - 会员来店明细表
-- 说明:
--   - 以"会员+订单"为粒度，记录每次来店消费明细
--   - 散客处理: member_id=0 不进入此表
--   - 数据来源: dwd_settlement_head + 关联明细表
--   - 更新频率: 每日增量更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_visit_detail CASCADE;
CREATE TABLE billiards_dws.dws_member_visit_detail (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    member_id           BIGINT NOT NULL,              -- 会员ID（散客不入此表）
    order_settle_id     BIGINT NOT NULL,              -- 结账单ID
    visit_date          DATE NOT NULL,                -- 来店日期
    visit_time          TIMESTAMPTZ,                  -- 来店时间
    -- 会员信息快照
    member_nickname     VARCHAR(100),                 -- 会员昵称
    member_mobile       VARCHAR(20),                  -- 手机号
    member_birthday     DATE,                         -- 会员生日（关联dim_member）
    -- 台桌信息
    table_id            BIGINT,                       -- 台桌ID
    table_name          VARCHAR(50),                  -- 台桌名称
    area_name           VARCHAR(50),                  -- 区域名称（原始）
    area_category       VARCHAR(20),                  -- 区域分类（散台区/包厢区/VIP区）
    -- 消费金额明细
    table_fee           NUMERIC(12,2) NOT NULL DEFAULT 0, -- 台费
    goods_amount        NUMERIC(12,2) NOT NULL DEFAULT 0, -- 商品金额
    assistant_amount    NUMERIC(12,2) NOT NULL DEFAULT 0, -- 助教服务金额
    total_consume       NUMERIC(12,2) NOT NULL DEFAULT 0, -- 消费总额（正价）
    total_discount      NUMERIC(12,2) NOT NULL DEFAULT 0, -- 优惠总额
    actual_pay          NUMERIC(12,2) NOT NULL DEFAULT 0, -- 实付金额
    -- 支付方式明细
    cash_pay            NUMERIC(12,2) NOT NULL DEFAULT 0, -- 现金/刷卡支付
    cash_card_pay       NUMERIC(12,2) NOT NULL DEFAULT 0, -- 储值卡支付
    gift_card_pay       NUMERIC(12,2) NOT NULL DEFAULT 0, -- 赠送卡支付
    groupbuy_pay        NUMERIC(12,2) NOT NULL DEFAULT 0, -- 团购券支付
    -- 时长信息
    table_duration_min  INTEGER NOT NULL DEFAULT 0,   -- 台桌使用时长（分钟）
    assistant_duration_min INTEGER NOT NULL DEFAULT 0, -- 助教服务时长（分钟）
    -- 助教服务明细（JSON格式，便于存储多个助教）
    assistant_services  JSONB,                        -- 助教服务列表 [{assistant_id, nickname, duration_min, amount}]
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_member_visit UNIQUE (site_id, member_id, order_settle_id)
);

COMMENT ON TABLE billiards_dws.dws_member_visit_detail IS '会员来店明细：按会员+订单记录每次来店消费，包含台桌、助教、支付明细';
COMMENT ON COLUMN billiards_dws.dws_member_visit_detail.member_id IS '会员ID：member_id=0散客不入此表';
COMMENT ON COLUMN billiards_dws.dws_member_visit_detail.area_category IS '区域分类：来自cfg_area_category映射';
COMMENT ON COLUMN billiards_dws.dws_member_visit_detail.assistant_services IS 'JSON格式：[{assistant_id, nickname, duration_min, amount}]';

CREATE INDEX idx_dws_member_visit_date ON billiards_dws.dws_member_visit_detail (visit_date);
CREATE INDEX idx_dws_member_visit_member ON billiards_dws.dws_member_visit_detail (member_id, visit_date);
CREATE INDEX idx_dws_member_visit_order ON billiards_dws.dws_member_visit_detail (order_settle_id);


-- =============================================================================
-- 第四部分：财务维度（7张）
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 13. dws_finance_daily_summary - 财务日度汇总表
-- 说明:
--   - 以"日期"为粒度，汇总当日财务数据
--   - 时间分层: 通过 stat_date 筛选实现近2天/近1月/近3月/全量
--   - 时间口径: 本周起始为周一，本月/季度起始为第一天0点
--   - 更新频率: 每小时更新当日数据
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_daily_summary CASCADE;
CREATE TABLE billiards_dws.dws_finance_daily_summary (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    stat_date           DATE NOT NULL,                -- 统计日期
    -- 发生额（正价）
    gross_amount        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 发生额合计
    table_fee_amount    NUMERIC(14,2) NOT NULL DEFAULT 0, -- 台费正价
    goods_amount        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 商品正价
    assistant_pd_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教基础课正价（陪打）
    assistant_cx_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教激励课正价（超休）
    -- 优惠拆分
    discount_total      NUMERIC(14,2) NOT NULL DEFAULT 0, -- 优惠合计
    discount_groupbuy   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购优惠 = coupon_amount - 团购支付金额
    discount_vip        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 会员折扣（member_discount_amount）
    discount_gift_card  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡抵扣
    discount_manual     NUMERIC(14,2) NOT NULL DEFAULT 0, -- 手动调整（adjust_amount）
    discount_rounding   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 抹零（rounding_amount）
    discount_other      NUMERIC(14,2) NOT NULL DEFAULT 0, -- 其他优惠
    -- 确认收入
    confirmed_income    NUMERIC(14,2) NOT NULL DEFAULT 0, -- 确认收入 = 发生额 - 优惠
    -- 现金流入
    cash_inflow_total   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金流入合计
    cash_pay_amount     NUMERIC(14,2) NOT NULL DEFAULT 0, -- 收银实付（pay_amount）
    groupbuy_pay_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购支付金额
    platform_settlement_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 平台回款金额（导入）
    platform_fee_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 平台佣金+服务费（导入）
    recharge_cash_inflow NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值现金流入（不含赠送）
    -- 储值卡消费（非现金流入）
    card_consume_total  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 卡消费合计
    cash_card_consume   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡消费
    gift_card_consume   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡消费
    -- 现金流出
    cash_outflow_total  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金流出合计（支出汇总）
    -- 现金余额变动
    cash_balance_change NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金余额变动 = 流入 - 流出
    -- 充值统计
    recharge_count      INTEGER NOT NULL DEFAULT 0,   -- 充值笔数
    recharge_total      NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值总额（含赠送）
    recharge_cash       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值现金部分
    recharge_gift       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值赠送部分
    first_recharge_count INTEGER NOT NULL DEFAULT 0,  -- 首充笔数
    first_recharge_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充金额
    renewal_count       INTEGER NOT NULL DEFAULT 0,   -- 续充笔数
    renewal_amount      NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充金额
    -- 订单统计
    order_count         INTEGER NOT NULL DEFAULT 0,   -- 结账单数
    member_order_count  INTEGER NOT NULL DEFAULT 0,   -- 会员订单数
    guest_order_count   INTEGER NOT NULL DEFAULT 0,   -- 散客订单数（member_id=0）
    avg_order_amount    NUMERIC(12,2) NOT NULL DEFAULT 0, -- 平均客单价
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_finance_daily UNIQUE (site_id, stat_date)
);

COMMENT ON TABLE billiards_dws.dws_finance_daily_summary IS '财务日度汇总：按日期汇总发生额、优惠、收入、现金流、充值等财务指标';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.gross_amount IS '发生额：table_charge_money + goods_money + assistant_pd_money + assistant_cx_money';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.discount_groupbuy IS '团购优惠：coupon_amount - 团购支付金额（pl_coupon_sale_amount或groupbuy_redemption.ledger_unit_price）';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.platform_settlement_amount IS '平台回款金额：来自dws_platform_settlement.settlement_amount';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.platform_fee_amount IS '平台费用：commission_amount + service_fee';
COMMENT ON COLUMN billiards_dws.dws_finance_daily_summary.first_recharge_count IS '首充：dwd_recharge_order.is_first=1';

CREATE INDEX idx_dws_finance_daily_date ON billiards_dws.dws_finance_daily_summary (stat_date);
CREATE INDEX idx_dws_finance_daily_site ON billiards_dws.dws_finance_daily_summary (site_id, stat_date);


-- -----------------------------------------------------------------------------
-- 14. dws_finance_income_structure - 收入结构分析表
-- 说明:
--   - 以"日期+区域/类型"为粒度，分析收入结构
--   - 区域分类: 使用cfg_area_category映射
--   - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_income_structure CASCADE;
CREATE TABLE billiards_dws.dws_finance_income_structure (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    stat_date           DATE NOT NULL,                -- 统计日期
    -- 分类维度
    structure_type      VARCHAR(20) NOT NULL,         -- 结构类型: AREA（区域）, INCOME_TYPE（收入类型）
    category_code       VARCHAR(30) NOT NULL,         -- 分类代码
    category_name       VARCHAR(50) NOT NULL,         -- 分类名称
    -- 收入金额
    income_amount       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 收入金额
    income_ratio        NUMERIC(5,4) NOT NULL DEFAULT 0,  -- 收入占比
    -- 订单统计
    order_count         INTEGER NOT NULL DEFAULT 0,   -- 订单数
    -- 时长统计（仅台费/助教相关）
    duration_minutes    INTEGER NOT NULL DEFAULT 0,   -- 时长（分钟）
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_finance_income_structure UNIQUE (site_id, stat_date, structure_type, category_code)
);

COMMENT ON TABLE billiards_dws.dws_finance_income_structure IS '收入结构分析：按区域/收入类型分析收入构成';
COMMENT ON COLUMN billiards_dws.dws_finance_income_structure.structure_type IS '结构类型：AREA=按区域, INCOME_TYPE=按收入类型（台费/商品/助教）';
COMMENT ON COLUMN billiards_dws.dws_finance_income_structure.category_code IS '分类代码：区域用SCATTER/ROOM/VIP, 收入类型用TABLE_FEE/GOODS/ASSISTANT';

CREATE INDEX idx_dws_finance_income_date ON billiards_dws.dws_finance_income_structure (stat_date);
CREATE INDEX idx_dws_finance_income_type ON billiards_dws.dws_finance_income_structure (structure_type, category_code);


-- -----------------------------------------------------------------------------
-- 15. dws_finance_discount_detail - 优惠明细表
-- 说明:
--   - 以"日期+优惠类型"为粒度，分析优惠构成
--   - 优惠类型: 团购优惠、会员折扣、赠送卡、手动调整、抹零、大客户优惠、其他
--   - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_discount_detail CASCADE;
CREATE TABLE billiards_dws.dws_finance_discount_detail (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    stat_date           DATE NOT NULL,                -- 统计日期
    -- 优惠类型
    discount_type_code  VARCHAR(30) NOT NULL,         -- 优惠类型代码
    discount_type_name  VARCHAR(50) NOT NULL,         -- 优惠类型名称
    -- 优惠金额
    discount_amount     NUMERIC(14,2) NOT NULL DEFAULT 0, -- 优惠金额
    discount_ratio      NUMERIC(5,4) NOT NULL DEFAULT 0,  -- 优惠占比（占总优惠）
    -- 使用统计
    usage_count         INTEGER NOT NULL DEFAULT 0,   -- 使用次数
    affected_orders     INTEGER NOT NULL DEFAULT 0,   -- 影响订单数
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_finance_discount_detail UNIQUE (site_id, stat_date, discount_type_code)
);

COMMENT ON TABLE billiards_dws.dws_finance_discount_detail IS '优惠明细：按优惠类型分析优惠构成';
COMMENT ON COLUMN billiards_dws.dws_finance_discount_detail.discount_type_code IS '优惠类型：GROUPBUY/VIP/GIFT_CARD/MANUAL/ROUNDING/BIG_CUSTOMER/OTHER';

CREATE INDEX idx_dws_finance_discount_date ON billiards_dws.dws_finance_discount_detail (stat_date);
CREATE INDEX idx_dws_finance_discount_type ON billiards_dws.dws_finance_discount_detail (discount_type_code);


-- -----------------------------------------------------------------------------
-- 16. dws_finance_recharge_summary - 充值统计表
-- 说明:
--   - 以"日期"为粒度，统计充值数据
--   - 区分首充/续充（is_first字段）
--   - 区分现金充值/赠送金额
--   - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_recharge_summary CASCADE;
CREATE TABLE billiards_dws.dws_finance_recharge_summary (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    stat_date           DATE NOT NULL,                -- 统计日期
    -- 充值汇总
    recharge_count      INTEGER NOT NULL DEFAULT 0,   -- 充值笔数
    recharge_total      NUMERIC(14,2) NOT NULL DEFAULT 0, -- 充值总额（含赠送）
    recharge_cash       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 现金充值金额
    recharge_gift       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送金额
    -- 首充统计
    first_recharge_count INTEGER NOT NULL DEFAULT 0,  -- 首充笔数
    first_recharge_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充现金
    first_recharge_gift NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充赠送
    first_recharge_total NUMERIC(14,2) NOT NULL DEFAULT 0, -- 首充总额
    -- 续充统计
    renewal_count       INTEGER NOT NULL DEFAULT 0,   -- 续充笔数
    renewal_cash        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充现金
    renewal_gift        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充赠送
    renewal_total       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 续充总额
    -- 充值会员统计
    recharge_member_count INTEGER NOT NULL DEFAULT 0, -- 充值会员数（去重）
    new_member_count    INTEGER NOT NULL DEFAULT 0,   -- 新增会员数
    -- 卡余额快照（当日末）
    total_card_balance  NUMERIC(14,2) NOT NULL DEFAULT 0, -- 全部会员卡余额
    cash_card_balance   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡余额
    gift_card_balance   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 赠送卡余额
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_finance_recharge UNIQUE (site_id, stat_date)
);

COMMENT ON TABLE billiards_dws.dws_finance_recharge_summary IS '充值统计：按日期统计充值数据，区分首充/续充、现金/赠送';
COMMENT ON COLUMN billiards_dws.dws_finance_recharge_summary.first_recharge_count IS '首充：dwd_recharge_order.is_first=1';
COMMENT ON COLUMN billiards_dws.dws_finance_recharge_summary.cash_card_balance IS '储值卡余额：card_type_id=2793249295533893';

CREATE INDEX idx_dws_finance_recharge_date ON billiards_dws.dws_finance_recharge_summary (stat_date);


-- -----------------------------------------------------------------------------
-- 17. dws_finance_expense_summary - 支出结构表
-- 说明:
--   - 以"月份+支出类型"为粒度，记录支出数据
--   - 数据来源: Excel手动导入
--   - 支出类型: 房租、水电、物业、工资、报销、平台费等
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_finance_expense_summary CASCADE;
CREATE TABLE billiards_dws.dws_finance_expense_summary (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    expense_month       DATE NOT NULL,                -- 支出月份（月第一天）
    -- 支出分类
    expense_type_code   VARCHAR(30) NOT NULL,         -- 支出类型代码
    expense_type_name   VARCHAR(50) NOT NULL,         -- 支出类型名称
    expense_category    VARCHAR(20),                  -- 支出大类（固定成本/变动成本/其他）
    -- 支出金额
    expense_amount      NUMERIC(14,2) NOT NULL DEFAULT 0, -- 支出金额
    -- 明细（可选）
    expense_detail      TEXT,                         -- 支出明细说明
    -- 导入信息
    import_batch_no     VARCHAR(50),                  -- 导入批次号
    import_file_name    VARCHAR(200),                 -- 导入文件名
    import_time         TIMESTAMPTZ,                  -- 导入时间
    import_user         VARCHAR(50),                  -- 导入操作人
    -- 备注
    remark              TEXT,                         -- 备注
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_finance_expense UNIQUE (site_id, expense_month, expense_type_code, import_batch_no)
);

COMMENT ON TABLE billiards_dws.dws_finance_expense_summary IS '支出结构：Excel导入，按月份+类型记录支出数据';
COMMENT ON COLUMN billiards_dws.dws_finance_expense_summary.expense_type_code IS '支出类型：RENT/UTILITY/PROPERTY/SALARY/REIMBURSE/PLATFORM_FEE/OTHER';
COMMENT ON COLUMN billiards_dws.dws_finance_expense_summary.expense_category IS '支出大类：FIXED_COST/VARIABLE_COST/OTHER';

CREATE INDEX idx_dws_finance_expense_month ON billiards_dws.dws_finance_expense_summary (expense_month);
CREATE INDEX idx_dws_finance_expense_type ON billiards_dws.dws_finance_expense_summary (expense_type_code);
CREATE INDEX idx_dws_finance_expense_batch ON billiards_dws.dws_finance_expense_summary (import_batch_no);


-- -----------------------------------------------------------------------------
-- 18. dws_assistant_finance_analysis - 助教收支分析表
-- 说明:
--   - 以"日期+助教"为粒度，分析助教产出的收入和成本
--   - 数据来源: dwd_assistant_service_log + dws_assistant_salary_calc
--   - 更新频率: 每日更新
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_assistant_finance_analysis CASCADE;
CREATE TABLE billiards_dws.dws_assistant_finance_analysis (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    stat_date           DATE NOT NULL,                -- 统计日期
    assistant_id        BIGINT NOT NULL,              -- 助教ID
    assistant_nickname  VARCHAR(50),                  -- 助教花名
    -- 收入（助教产出）
    revenue_total       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教产出收入（ledger_amount汇总）
    revenue_base        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 基础课收入
    revenue_bonus       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 附加课收入
    revenue_room        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 包厢/房间收入
    -- 成本（助教工资分摊）
    cost_daily          NUMERIC(14,2) NOT NULL DEFAULT 0, -- 日均工资成本（月工资/工作天数）
    -- 毛利
    gross_profit        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 毛利 = 收入 - 成本
    gross_margin        NUMERIC(5,4) NOT NULL DEFAULT 0,  -- 毛利率
    -- 服务统计
    service_count       INTEGER NOT NULL DEFAULT 0,   -- 服务次数
    service_hours       NUMERIC(10,2) NOT NULL DEFAULT 0, -- 服务小时数
    room_service_count  INTEGER NOT NULL DEFAULT 0,   -- 包厢/房间服务次数
    room_service_hours  NUMERIC(10,2) NOT NULL DEFAULT 0, -- 包厢/房间服务小时数
    unique_customers    INTEGER NOT NULL DEFAULT 0,   -- 服务客户数
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_assistant_finance UNIQUE (site_id, stat_date, assistant_id)
);

COMMENT ON TABLE billiards_dws.dws_assistant_finance_analysis IS '助教收支分析：按日期+助教分析产出收入和工资成本';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.revenue_total IS '助教产出收入：dwd_assistant_service_log.ledger_amount汇总';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.cost_daily IS '日均工资成本：月工资/当月工作天数';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.revenue_room IS '包厢/房间收入';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.room_service_count IS '包厢/房间服务次数';
COMMENT ON COLUMN billiards_dws.dws_assistant_finance_analysis.room_service_hours IS '包厢/房间服务小时数';

CREATE INDEX idx_dws_assistant_finance_date ON billiards_dws.dws_assistant_finance_analysis (stat_date);
CREATE INDEX idx_dws_assistant_finance_asst ON billiards_dws.dws_assistant_finance_analysis (assistant_id, stat_date);


-- -----------------------------------------------------------------------------
-- 19. dws_platform_settlement - 平台回款/服务费表
-- 说明:
--   - 以"回款日期+平台+订单"为粒度，记录平台结算数据
--   - 数据来源: Excel手动导入
--   - 字段: 回款金额、佣金、服务费、回款日期、平台类型、订单关联键
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_platform_settlement CASCADE;
CREATE TABLE billiards_dws.dws_platform_settlement (
    id                  BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    settlement_date     DATE NOT NULL,                -- 回款日期
    -- 平台信息
    platform_type       VARCHAR(30) NOT NULL,         -- 平台类型（美团/抖音/大众点评/其他）
    platform_name       VARCHAR(50),                  -- 平台名称
    -- 订单关联
    platform_order_no   VARCHAR(100),                 -- 平台订单号
    order_settle_id     BIGINT,                       -- 关联的结账单ID（可选）
    -- 金额明细
    settlement_amount   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 回款金额（实际入账）
    commission_amount   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 佣金（平台抽成）
    service_fee         NUMERIC(14,2) NOT NULL DEFAULT 0, -- 服务费
    gross_amount        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单原始金额
    -- 导入信息
    import_batch_no     VARCHAR(50),                  -- 导入批次号
    import_file_name    VARCHAR(200),                 -- 导入文件名
    import_time         TIMESTAMPTZ,                  -- 导入时间
    import_user         VARCHAR(50),                  -- 导入操作人
    -- 备注
    remark              TEXT,                         -- 备注
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE billiards_dws.dws_platform_settlement IS '平台回款/服务费：Excel导入，记录各平台结算明细';
COMMENT ON COLUMN billiards_dws.dws_platform_settlement.platform_type IS '平台类型：MEITUAN/DOUYIN/DIANPING/OTHER';
COMMENT ON COLUMN billiards_dws.dws_platform_settlement.settlement_amount IS '回款金额：实际入账金额 = gross_amount - commission_amount - service_fee';

CREATE INDEX idx_dws_platform_settlement_date ON billiards_dws.dws_platform_settlement (settlement_date);
CREATE INDEX idx_dws_platform_settlement_platform ON billiards_dws.dws_platform_settlement (platform_type);
CREATE INDEX idx_dws_platform_settlement_order ON billiards_dws.dws_platform_settlement (order_settle_id);
CREATE INDEX idx_dws_platform_settlement_batch ON billiards_dws.dws_platform_settlement (import_batch_no);


-- =============================================================================
-- 第五部分：订单汇总（保留原有表，增强字段）
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 20. dws_order_summary - 订单汇总表
-- 说明:
--   - 以"订单"为粒度，汇总订单级别的数据
--   - 作为订单级别的聚合层，用于财务与经营分析
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_order_summary CASCADE;
CREATE TABLE billiards_dws.dws_order_summary (
    site_id                    BIGINT NOT NULL,              -- 门店ID
    order_settle_id            BIGINT NOT NULL,              -- 结账单ID
    order_trade_no             VARCHAR(64),                  -- 交易单号
    order_date                 DATE NOT NULL,                -- 订单日期
    tenant_id                  BIGINT NOT NULL,              -- 租户ID
    member_id                  BIGINT,                       -- 会员ID
    member_flag                BOOLEAN NOT NULL DEFAULT FALSE, -- 是否会员订单
    recharge_order_flag        BOOLEAN NOT NULL DEFAULT FALSE, -- 是否充值订单
    item_count                 INTEGER NOT NULL DEFAULT 0,   -- 项目条目数
    total_item_quantity        INTEGER NOT NULL DEFAULT 0,   -- 项目总数量
    table_fee_amount           NUMERIC(14,2) NOT NULL DEFAULT 0, -- 台费金额
    assistant_service_amount   NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教服务金额
    goods_amount               NUMERIC(14,2) NOT NULL DEFAULT 0, -- 商品金额
    group_amount               NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购金额
    total_coupon_deduction     NUMERIC(14,2) NOT NULL DEFAULT 0, -- 优惠券抵扣
    member_discount_amount     NUMERIC(14,2) NOT NULL DEFAULT 0, -- 会员折扣
    manual_discount_amount     NUMERIC(14,2) NOT NULL DEFAULT 0, -- 手动优惠
    order_original_amount      NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单原价
    order_final_amount         NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单最终金额
    stored_card_deduct         NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值卡抵扣
    external_paid_amount       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 外部支付金额
    total_paid_amount          NUMERIC(14,2) NOT NULL DEFAULT 0, -- 实收金额
    book_table_flow            NUMERIC(14,2) NOT NULL DEFAULT 0, -- 台费流水
    book_assistant_flow        NUMERIC(14,2) NOT NULL DEFAULT 0, -- 助教流水
    book_goods_flow            NUMERIC(14,2) NOT NULL DEFAULT 0, -- 商品流水
    book_group_flow            NUMERIC(14,2) NOT NULL DEFAULT 0, -- 团购流水
    book_order_flow            NUMERIC(14,2) NOT NULL DEFAULT 0, -- 订单总流水
    order_effective_consume_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 有效消费现金
    order_effective_recharge_cash NUMERIC(14,2) NOT NULL DEFAULT 0, -- 有效充值现金
    order_effective_flow       NUMERIC(14,2) NOT NULL DEFAULT 0, -- 有效流水
    refund_amount              NUMERIC(14,2) NOT NULL DEFAULT 0, -- 退款金额
    net_income                 NUMERIC(14,2) NOT NULL DEFAULT 0, -- 净收入
    created_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at                 TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT [REDACTED]PRIMARY KEY (site_id, order_settle_id)
);

COMMENT ON TABLE billiards_dws.dws_order_summary IS '订单汇总：按订单汇总各项金额、优惠、支付信息';
COMMENT ON COLUMN billiards_dws.dws_order_summary.order_date IS '订单日期：优先 pay_time，缺失时取 create_time';
COMMENT ON COLUMN billiards_dws.dws_order_summary.recharge_order_flag IS '充值订单标记：消费金额=0 且 实收>0';
COMMENT ON COLUMN billiards_dws.dws_order_summary.order_original_amount IS '原价金额 = 实收 + 优惠/折扣';
COMMENT ON COLUMN billiards_dws.dws_order_summary.external_paid_amount IS '外部支付金额（实收-储值抵扣）';
COMMENT ON COLUMN billiards_dws.dws_order_summary.book_order_flow IS '订单总流水 = 台费 + 助教 + 商品 + 团购';
COMMENT ON COLUMN billiards_dws.dws_order_summary.net_income IS '净收入 = 实收 - 退款';

CREATE INDEX idx_dws_order_summary_member ON billiards_dws.dws_order_summary (member_id, order_date);
CREATE INDEX idx_dws_order_summary_site_date ON billiards_dws.dws_order_summary (site_id, order_date);
CREATE INDEX idx_dws_order_summary_trade_no ON billiards_dws.dws_order_summary (order_trade_no);


-- =============================================================================
-- 第六部分：时间分层辅助视图
-- 说明:
--   - 时间分层通过查询条件实现，不单独创建分层表
--   - 提供常用时间窗口的参考视图
--   - 时间口径: 周起始为周一，月/季度起始为第一天0点
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 时间窗口计算函数
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION billiards_dws.get_time_window(
    p_window_type VARCHAR(30),  -- 窗口类型: THIS_WEEK, LAST_WEEK, THIS_MONTH, LAST_MONTH, LAST_3_MONTHS_EXCL_CURRENT, LAST_3_MONTHS_INCL_CURRENT, THIS_QUARTER, LAST_QUARTER, LAST_6_MONTHS, LAST_2_DAYS, LAST_1_MONTH, LAST_3_MONTHS
    p_base_date DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE (
    window_start DATE,
    window_end DATE
) AS $$
DECLARE
    v_year INTEGER;
    v_month INTEGER;
    v_quarter INTEGER;
BEGIN
    v_year := EXTRACT(YEAR FROM p_base_date);
    v_month := EXTRACT(MONTH FROM p_base_date);
    v_quarter := EXTRACT(QUARTER FROM p_base_date);
    
    CASE p_window_type
        -- 本周（周一起始）
        WHEN 'THIS_WEEK' THEN
            window_start := DATE_TRUNC('week', p_base_date)::DATE;
            window_end := p_base_date;
        -- 上周
        WHEN 'LAST_WEEK' THEN
            window_start := (DATE_TRUNC('week', p_base_date) - INTERVAL '7 days')::DATE;
            window_end := (DATE_TRUNC('week', p_base_date) - INTERVAL '1 day')::DATE;
        -- 本月
        WHEN 'THIS_MONTH' THEN
            window_start := DATE_TRUNC('month', p_base_date)::DATE;
            window_end := p_base_date;
        -- 上月
        WHEN 'LAST_MONTH' THEN
            window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 month')::DATE;
            window_end := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 day')::DATE;
        -- 前3个月（不含本月）
        WHEN 'LAST_3_MONTHS_EXCL_CURRENT' THEN
            window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '3 months')::DATE;
            window_end := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 day')::DATE;
        -- 前3个月（含本月）
        WHEN 'LAST_3_MONTHS_INCL_CURRENT' THEN
            window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '2 months')::DATE;
            window_end := p_base_date;
        -- 本季度
        WHEN 'THIS_QUARTER' THEN
            window_start := DATE_TRUNC('quarter', p_base_date)::DATE;
            window_end := p_base_date;
        -- 上季度
        WHEN 'LAST_QUARTER' THEN
            window_start := (DATE_TRUNC('quarter', p_base_date) - INTERVAL '3 months')::DATE;
            window_end := (DATE_TRUNC('quarter', p_base_date) - INTERVAL '1 day')::DATE;
        -- 最近半年（不含本月）
        WHEN 'LAST_6_MONTHS' THEN
            window_start := (DATE_TRUNC('month', p_base_date) - INTERVAL '6 months')::DATE;
            window_end := (DATE_TRUNC('month', p_base_date) - INTERVAL '1 day')::DATE;
        -- 近2天
        WHEN 'LAST_2_DAYS' THEN
            window_start := (p_base_date - INTERVAL '1 day')::DATE;
            window_end := p_base_date;
        -- 近1月
        WHEN 'LAST_1_MONTH' THEN
            window_start := (p_base_date - INTERVAL '1 month')::DATE;
            window_end := p_base_date;
        -- 近3月
        WHEN 'LAST_3_MONTHS' THEN
            window_start := (p_base_date - INTERVAL '3 months')::DATE;
            window_end := p_base_date;
        ELSE
            -- 默认全量
            window_start := '2000-01-01'::DATE;
            window_end := p_base_date;
    END CASE;
    
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION billiards_dws.get_time_window IS '时间窗口计算函数：根据窗口类型返回起止日期，周起始为周一';


-- -----------------------------------------------------------------------------
-- 环比计算辅助函数
-- 说明: 环比规则为"对比上一个等长区间"
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION billiards_dws.get_comparison_window(
    p_start_date DATE,
    p_end_date DATE
)
RETURNS TABLE (
    prev_start DATE,
    prev_end DATE
) AS $$
DECLARE
    v_interval INTERVAL;
BEGIN
    -- 计算区间长度
    v_interval := (p_end_date - p_start_date + 1) * INTERVAL '1 day';
    
    -- 上一个等长区间
    prev_end := p_start_date - INTERVAL '1 day';
    prev_start := (prev_end - v_interval + INTERVAL '1 day')::DATE;
    
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

COMMENT ON FUNCTION billiards_dws.get_comparison_window IS '环比窗口计算：返回上一个等长区间的起止日期';


-- =============================================================================
-- 第六部分：指数算法（6张）
-- =============================================================================

-- -----------------------------------------------------------------------------
-- 21. cfg_index_parameters - 指数算法参数配置表
-- 说明:
--   - 存储客户召回/新客转化/客户唤回/亲密指数的算法参数
--   - 支持按时间生效，便于参数调优和历史追溯
--   - 参数类型: RECALL（召回指数）, INTIMACY（亲密指数）, NCI（新客转化）, WBI（唤回指数）
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.cfg_index_parameters CASCADE;
CREATE TABLE billiards_dws.cfg_index_parameters (
    param_id            SERIAL PRIMARY KEY,           -- 参数ID（自增）
    index_type          VARCHAR(50) NOT NULL,         -- 指数类型: RECALL/INTIMACY
    param_name          VARCHAR(100) NOT NULL,        -- 参数名称
    param_value         NUMERIC(14,6) NOT NULL,       -- 参数值
    description         TEXT,                         -- 参数说明
    effective_from      DATE NOT NULL DEFAULT CURRENT_DATE, -- 生效起始日期
    effective_to        DATE,                         -- 生效截止日期（NULL=永久有效）
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_cfg_index_parameters UNIQUE (index_type, param_name, effective_from)
);

COMMENT ON TABLE billiards_dws.cfg_index_parameters IS '指数算法参数配置表：存储 RS/OS/MS/ML/NCI/WBI 等指数参数';
COMMENT ON COLUMN billiards_dws.cfg_index_parameters.index_type IS '指数类型：RS/OS/MS/ML/NCI/WBI（兼容保留 RECALL/INTIMACY）';
COMMENT ON COLUMN billiards_dws.cfg_index_parameters.param_name IS '参数名称：如lookback_days, halflife_new, weight_overdue等';

CREATE INDEX idx_cfg_index_params_type ON billiards_dws.cfg_index_parameters (index_type);
CREATE INDEX idx_cfg_index_params_effective ON billiards_dws.cfg_index_parameters (effective_from, effective_to);


-- -----------------------------------------------------------------------------
-- 22. dws_member_recall_index - 客户召回指数表
-- 说明:
--   - 以"会员"为粒度，计算客户召回的必要性和紧急程度
--   - 算法基于：超期紧急性、新客户加分、刚充值加分、热度断档加分
--   - 尊重客户个人到店周期（μ=中位数, σ=MAD）
--   - 散客(member_id=0)不参与计算
--   - 更新频率: 每2小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_recall_index CASCADE;
CREATE TABLE billiards_dws.dws_member_recall_index (
    recall_id           BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    member_id           BIGINT NOT NULL,              -- 会员ID (散客不计算)
    
    -- 计算输入特征
    days_since_last_visit INTEGER,                    -- 距最近一次到店的天数 (t)
    visit_interval_median NUMERIC(10,2),              -- 到店周期中位数 (μ)
    visit_interval_mad  NUMERIC(10,2),                -- 到店周期MAD (σ)
    days_since_first_visit INTEGER,                   -- 距首访天数
    days_since_last_recharge INTEGER,                 -- 距最近充值天数
    visits_last_14_days INTEGER NOT NULL DEFAULT 0,   -- 近14天到店次数
    visits_last_60_days INTEGER NOT NULL DEFAULT 0,   -- 近60天到店次数
    
    -- 分项得分
    score_overdue       NUMERIC(10,4),                -- 超期紧急性得分: 1-exp(-max(0,(t-μ)/σ))
    score_new_bonus     NUMERIC(10,4),                -- 新客户加分: decay(d_first, h_new)
    score_recharge_bonus NUMERIC(10,4),               -- 刚充值加分: decay(d_recharge, h_re)
    score_hot_drop      NUMERIC(10,4),                -- 热度断档加分: max(0, ln(1+(r14/r60-1)))
    
    -- 最终分数
    raw_score           NUMERIC(14,6),                -- Raw Score (无上限)
    display_score       NUMERIC(4,2),                 -- Display Score (0-10)
    
    -- 元数据
    calc_time           TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
    calc_version        INTEGER NOT NULL DEFAULT 1,   -- 计算版本（参数变更时递增）
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT uk_dws_member_recall UNIQUE (site_id, member_id)
);

COMMENT ON TABLE billiards_dws.dws_member_recall_index IS '客户召回指数：衡量客户召回的必要性和紧急程度，尊重个人到店周期';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.visit_interval_median IS '到店周期中位数(μ)：近60天到店间隔的中位数';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.visit_interval_mad IS '到店周期MAD(σ)：中位绝对偏差，下限为sigma_min=2';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.score_overdue IS '超期紧急性：1-exp(-z), z=max(0,(t-μ)/σ)';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.raw_score IS 'Raw Score：无上限，公式=w_over*overdue+w_new*new+w_re*recharge+w_hot*hot_drop';
COMMENT ON COLUMN billiards_dws.dws_member_recall_index.display_score IS 'Display Score：0-10，Winsorize(P5,P95)+MinMax映射';

CREATE INDEX idx_dws_recall_display ON billiards_dws.dws_member_recall_index (site_id, display_score DESC);
CREATE INDEX idx_dws_recall_raw ON billiards_dws.dws_member_recall_index (site_id, raw_score DESC);
CREATE INDEX idx_dws_recall_calc_time ON billiards_dws.dws_member_recall_index (calc_time);


-- -----------------------------------------------------------------------------
-- 23. dws_member_newconv_index - 新客转化指数表
-- 说明:
--   - 以"会员"为粒度，衡量新客转化潜力与触达优先级
--   - 更新频率: 每2小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_newconv_index CASCADE;
CREATE TABLE billiards_dws.dws_member_newconv_index (
    newconv_id          BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    member_id           BIGINT NOT NULL,              -- 会员ID
    status              VARCHAR(30),                  -- 状态标记
    segment             VARCHAR(10),                  -- 分群标签
    member_create_time  TIMESTAMPTZ,                  -- 注册/建档时间
    first_visit_time    TIMESTAMPTZ,                  -- 首访时间
    last_visit_time     TIMESTAMPTZ,                  -- 最近到店时间
    last_recharge_time  TIMESTAMPTZ,                  -- 最近充值时间
    t_v                 NUMERIC(6,2),                 -- 访问间隔特征
    t_r                 NUMERIC(6,2),                 -- 充值间隔特征
    t_a                 NUMERIC(6,2),                 -- 活跃度特征
    visits_14d          INTEGER NOT NULL DEFAULT 0,   -- 近14天到店次数
    visits_60d          INTEGER NOT NULL DEFAULT 0,   -- 近60天到店次数
    visits_total        INTEGER NOT NULL DEFAULT 0,   -- 累计到店次数
    spend_30d           NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近30天消费
    spend_180d          NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近180天消费
    sv_balance          NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值余额
    recharge_60d_amt    NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近60天充值
    interval_count      INTEGER NOT NULL DEFAULT 0,   -- 访次间隔计数
    need_new            NUMERIC(10,4),                -- 需求强度
    salvage_new         NUMERIC(10,4),                -- 挽回强度
    recharge_new        NUMERIC(10,4),                -- 充值驱动
    value_new           NUMERIC(10,4),                -- 价值权重
    welcome_new         NUMERIC(10,4),                -- 欢迎触达权重
    raw_score_welcome   NUMERIC(14,6),                -- Raw Score（欢迎阶段）
    raw_score_convert   NUMERIC(14,6),                -- Raw Score（转化阶段）
    raw_score           NUMERIC(14,6),                -- Raw Score（综合）
    display_score_welcome NUMERIC(4,2),               -- Display Score（欢迎阶段）
    display_score_convert NUMERIC(4,2),               -- Display Score（转化阶段）
    display_score       NUMERIC(4,2),                 -- Display Score（综合）
    last_wechat_touch_time TIMESTAMPTZ,               -- 最近触达时间
    calc_time           TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
    calc_version        INTEGER NOT NULL DEFAULT 1,   -- 计算版本
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uk_dws_member_newconv UNIQUE (site_id, member_id)
);

COMMENT ON TABLE billiards_dws.dws_member_newconv_index IS '新客转化指数：衡量新客转化潜力与触达优先级';
COMMENT ON COLUMN billiards_dws.dws_member_newconv_index.raw_score IS 'Raw Score：无上限，综合各项特征权重后的原始分数';
COMMENT ON COLUMN billiards_dws.dws_member_newconv_index.display_score IS 'Display Score：0-10，标准化展示分';

CREATE INDEX idx_dws_newconv_display ON billiards_dws.dws_member_newconv_index (site_id, display_score DESC);


-- -----------------------------------------------------------------------------
-- 24. dws_member_winback_index - 客户唤回指数表
-- 说明:
--   - 以"会员"为粒度，衡量客户唤回紧急度与优先级
--   - 更新频率: 每2小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_winback_index CASCADE;
CREATE TABLE billiards_dws.dws_member_winback_index (
    winback_id          BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    member_id           BIGINT NOT NULL,              -- 会员ID
    status              VARCHAR(30),                  -- 状态标记
    segment             VARCHAR(10),                  -- 分群标签
    member_create_time  TIMESTAMPTZ,                  -- 注册/建档时间
    first_visit_time    TIMESTAMPTZ,                  -- 首访时间
    last_visit_time     TIMESTAMPTZ,                  -- 最近到店时间
    last_recharge_time  TIMESTAMPTZ,                  -- 最近充值时间
    t_v                 NUMERIC(6,2),                 -- 访问间隔特征
    t_r                 NUMERIC(6,2),                 -- 充值间隔特征
    t_a                 NUMERIC(6,2),                 -- 活跃度特征
    visits_14d          INTEGER NOT NULL DEFAULT 0,   -- 近14天到店次数
    visits_60d          INTEGER NOT NULL DEFAULT 0,   -- 近60天到店次数
    visits_total        INTEGER NOT NULL DEFAULT 0,   -- 累计到店次数
    spend_30d           NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近30天消费
    spend_180d          NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近180天消费
    sv_balance          NUMERIC(14,2) NOT NULL DEFAULT 0, -- 储值余额
    recharge_60d_amt    NUMERIC(14,2) NOT NULL DEFAULT 0, -- 近60天充值
    interval_count      INTEGER NOT NULL DEFAULT 0,   -- 访次间隔计数
    overdue_old         NUMERIC(10,4),                -- 超期强度
    drop_old            NUMERIC(10,4),                -- 流失强度
    recharge_old        NUMERIC(10,4),                -- 充值驱动
    value_old           NUMERIC(10,4),                -- 价值权重
    raw_score           NUMERIC(14,6),                -- Raw Score（综合）
    display_score       NUMERIC(4,2),                 -- Display Score（综合）
    last_wechat_touch_time TIMESTAMPTZ,               -- 最近触达时间
    calc_time           TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
    calc_version        INTEGER NOT NULL DEFAULT 1,   -- 计算版本
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    overdue_cdf_p       NUMERIC(10,4),                -- 超期CDF分位
    ideal_interval_days NUMERIC(10,2),                -- 理想到店间隔（天）
    ideal_next_visit_date DATE,                       -- 理想下次到店日期
    CONSTRAINT uk_dws_member_winback UNIQUE (site_id, member_id)
);

COMMENT ON TABLE billiards_dws.dws_member_winback_index IS '客户唤回指数：衡量客户回访紧急度与优先级';
COMMENT ON COLUMN billiards_dws.dws_member_winback_index.raw_score IS 'Raw Score：无上限，综合各项特征权重后的原始分数';
COMMENT ON COLUMN billiards_dws.dws_member_winback_index.display_score IS 'Display Score：0-10，标准化展示分';

CREATE INDEX idx_dws_winback_display ON billiards_dws.dws_member_winback_index (site_id, display_score DESC);


-- -----------------------------------------------------------------------------
-- 25. v_member_recall_priority - 召回优先级视图
-- 说明:
--   - 合并唤回指数(WBI)与新客转化指数(NCI)用于统一排序
-- -----------------------------------------------------------------------------
CREATE OR REPLACE VIEW billiards_dws.v_member_recall_priority AS
SELECT
    dws_member_winback_index.site_id,
    dws_member_winback_index.tenant_id,
    dws_member_winback_index.member_id,
    'WBI'::character varying(10) AS index_type,
    dws_member_winback_index.status,
    dws_member_winback_index.segment,
    dws_member_winback_index.member_create_time,
    dws_member_winback_index.first_visit_time,
    dws_member_winback_index.last_visit_time,
    dws_member_winback_index.last_recharge_time,
    dws_member_winback_index.t_v,
    dws_member_winback_index.t_r,
    dws_member_winback_index.t_a,
    dws_member_winback_index.visits_14d,
    dws_member_winback_index.visits_60d,
    dws_member_winback_index.visits_total,
    dws_member_winback_index.spend_30d,
    dws_member_winback_index.spend_180d,
    dws_member_winback_index.sv_balance,
    dws_member_winback_index.recharge_60d_amt,
    NULL::numeric(10,4) AS need_new,
    NULL::numeric(10,4) AS salvage_new,
    NULL::numeric(10,4) AS recharge_new,
    NULL::numeric(10,4) AS value_new,
    NULL::numeric(10,4) AS welcome_new,
    NULL::numeric(14,6) AS raw_score_welcome,
    NULL::numeric(14,6) AS raw_score_convert,
    dws_member_winback_index.raw_score,
    NULL::numeric(4,2) AS display_score_welcome,
    NULL::numeric(4,2) AS display_score_convert,
    dws_member_winback_index.display_score,
    dws_member_winback_index.last_wechat_touch_time,
    dws_member_winback_index.calc_time
FROM billiards_dws.dws_member_winback_index
UNION ALL
SELECT
    dws_member_newconv_index.site_id,
    dws_member_newconv_index.tenant_id,
    dws_member_newconv_index.member_id,
    'NCI'::character varying(10) AS index_type,
    dws_member_newconv_index.status,
    dws_member_newconv_index.segment,
    dws_member_newconv_index.member_create_time,
    dws_member_newconv_index.first_visit_time,
    dws_member_newconv_index.last_visit_time,
    dws_member_newconv_index.last_recharge_time,
    dws_member_newconv_index.t_v,
    dws_member_newconv_index.t_r,
    dws_member_newconv_index.t_a,
    dws_member_newconv_index.visits_14d,
    dws_member_newconv_index.visits_60d,
    dws_member_newconv_index.visits_total,
    dws_member_newconv_index.spend_30d,
    dws_member_newconv_index.spend_180d,
    dws_member_newconv_index.sv_balance,
    dws_member_newconv_index.recharge_60d_amt,
    dws_member_newconv_index.need_new,
    dws_member_newconv_index.salvage_new,
    dws_member_newconv_index.recharge_new,
    dws_member_newconv_index.value_new,
    dws_member_newconv_index.welcome_new,
    dws_member_newconv_index.raw_score_welcome,
    dws_member_newconv_index.raw_score_convert,
    dws_member_newconv_index.raw_score,
    dws_member_newconv_index.display_score_welcome,
    dws_member_newconv_index.display_score_convert,
    dws_member_newconv_index.display_score,
    dws_member_newconv_index.last_wechat_touch_time,
    dws_member_newconv_index.calc_time
FROM billiards_dws.dws_member_newconv_index;

COMMENT ON VIEW billiards_dws.v_member_recall_priority IS '召回优先级视图：合并WBI与NCI指数用于统一排序';


-- -----------------------------------------------------------------------------
-- 26. dws_member_assistant_relation_index - 客户-助教关系指数表（RS/OS/MS/ML）
-- 说明:
--   - 关系粒度: site_id + member_id + assistant_id
--   - 单任务产出 RS（关系强度）/OS（归属份额）/MS（升温动量）/ML（付费关联）
--   - ML 由人工台账窄表 dws_ml_manual_order_alloc 驱动
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_assistant_relation_index CASCADE;
CREATE TABLE billiards_dws.dws_member_assistant_relation_index (
    relation_id          BIGSERIAL PRIMARY KEY,
    site_id              BIGINT NOT NULL,
    tenant_id            BIGINT NOT NULL,
    member_id            BIGINT NOT NULL,
    assistant_id         BIGINT NOT NULL,

    -- 服务行为特征
    session_count        INTEGER NOT NULL DEFAULT 0,
    total_duration_minutes INTEGER NOT NULL DEFAULT 0,
    basic_session_count  INTEGER NOT NULL DEFAULT 0,
    incentive_session_count INTEGER NOT NULL DEFAULT 0,
    days_since_last_session INTEGER,

    -- RS
    rs_f                 NUMERIC(14,6) NOT NULL DEFAULT 0,
    rs_d                 NUMERIC(14,6) NOT NULL DEFAULT 0,
    rs_r                 NUMERIC(14,6) NOT NULL DEFAULT 0,
    rs_raw               NUMERIC(14,6) NOT NULL DEFAULT 0,
    rs_display           NUMERIC(4,2) NOT NULL DEFAULT 0,

    -- OS
    os_share             NUMERIC(10,6) NOT NULL DEFAULT 0,
    os_label             VARCHAR(20) NOT NULL DEFAULT 'POOL',
    os_rank              INTEGER,

    -- MS
    ms_f_short           NUMERIC(14,6) NOT NULL DEFAULT 0,
    ms_f_long            NUMERIC(14,6) NOT NULL DEFAULT 0,
    ms_raw               NUMERIC(14,6) NOT NULL DEFAULT 0,
    ms_display           NUMERIC(4,2) NOT NULL DEFAULT 0,

    -- ML
    ml_order_count       INTEGER NOT NULL DEFAULT 0,
    ml_allocated_amount  NUMERIC(14,2) NOT NULL DEFAULT 0,
    ml_raw               NUMERIC(14,6) NOT NULL DEFAULT 0,
    ml_display           NUMERIC(4,2) NOT NULL DEFAULT 0,

    -- 元数据
    calc_time            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_dws_member_assistant_relation_index UNIQUE (site_id, member_id, assistant_id)
);

COMMENT ON TABLE billiards_dws.dws_member_assistant_relation_index IS '客户-助教关系指数结果表（RS/OS/MS/ML）';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_relation_index.os_label IS '归属标签：UNASSIGNED/MAIN/COMANAGE/POOL';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_relation_index.ml_allocated_amount IS '人工台账分摊后金额';

CREATE INDEX idx_dws_relation_member ON billiards_dws.dws_member_assistant_relation_index (site_id, member_id, os_share DESC);
CREATE INDEX idx_dws_relation_assistant ON billiards_dws.dws_member_assistant_relation_index (site_id, assistant_id, rs_display DESC);
CREATE INDEX idx_dws_relation_calc_time ON billiards_dws.dws_member_assistant_relation_index (calc_time);


-- -----------------------------------------------------------------------------
-- 27. dws_ml_manual_order_source - ML 人工台账宽表（订单一行）
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_ml_manual_order_source CASCADE;
CREATE TABLE billiards_dws.dws_ml_manual_order_source (
    source_id            BIGSERIAL PRIMARY KEY,
    site_id              BIGINT NOT NULL,
    biz_date             DATE NOT NULL,
    external_id          VARCHAR(128) NOT NULL,
    member_id            BIGINT NOT NULL DEFAULT 0,
    pay_time             TIMESTAMPTZ NOT NULL,
    order_amount         NUMERIC(14,2) NOT NULL DEFAULT 0,
    currency             VARCHAR(16) NOT NULL DEFAULT 'CNY',

    assistant_id_1       BIGINT,
    assistant_name_1     VARCHAR(128),
    assistant_id_2       BIGINT,
    assistant_name_2     VARCHAR(128),
    assistant_id_3       BIGINT,
    assistant_name_3     VARCHAR(128),
    assistant_id_4       BIGINT,
    assistant_name_4     VARCHAR(128),
    assistant_id_5       BIGINT,
    assistant_name_5     VARCHAR(128),

    import_batch_no      VARCHAR(64) NOT NULL,
    import_file_name     VARCHAR(255) NOT NULL,
    import_scope_key     VARCHAR(128) NOT NULL,
    import_time          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    import_user          VARCHAR(64),
    row_no               INTEGER NOT NULL,
    remark               TEXT,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_dws_ml_manual_order_source UNIQUE (site_id, external_id, import_scope_key, row_no)
);

COMMENT ON TABLE billiards_dws.dws_ml_manual_order_source IS 'ML人工台账宽表：订单一行，支持最多5名助教';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_source.external_id IS '外部订单ID，必填';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_source.import_scope_key IS '覆盖范围键：DAY或P30';

CREATE INDEX idx_dws_ml_source_scope ON billiards_dws.dws_ml_manual_order_source (site_id, biz_date);
CREATE INDEX idx_dws_ml_source_external ON billiards_dws.dws_ml_manual_order_source (site_id, external_id);


-- -----------------------------------------------------------------------------
-- 28. dws_ml_manual_order_alloc - ML 人工台账分摊窄表（用于计算 ML_raw）
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_ml_manual_order_alloc CASCADE;
CREATE TABLE billiards_dws.dws_ml_manual_order_alloc (
    alloc_id             BIGSERIAL PRIMARY KEY,
    site_id              BIGINT NOT NULL,
    biz_date             DATE NOT NULL,
    external_id          VARCHAR(128) NOT NULL,
    member_id            BIGINT NOT NULL DEFAULT 0,
    pay_time             TIMESTAMPTZ NOT NULL,
    order_amount         NUMERIC(14,2) NOT NULL DEFAULT 0,
    assistant_id         BIGINT NOT NULL,
    assistant_name       VARCHAR(128),
    share_ratio          NUMERIC(14,8) NOT NULL DEFAULT 0,
    allocated_amount     NUMERIC(14,2) NOT NULL DEFAULT 0,
    currency             VARCHAR(16) NOT NULL DEFAULT 'CNY',

    import_scope_key     VARCHAR(128) NOT NULL,
    import_batch_no      VARCHAR(64) NOT NULL,
    import_file_name     VARCHAR(255) NOT NULL,
    import_time          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    import_user          VARCHAR(64),
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT uk_dws_ml_manual_order_alloc UNIQUE (site_id, external_id, assistant_id)
);

COMMENT ON TABLE billiards_dws.dws_ml_manual_order_alloc IS 'ML人工台账窄表：按订单-助教分摊后明细，关系指数ML直接读取';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_alloc.share_ratio IS '分摊比例：一单多助教默认均分';
COMMENT ON COLUMN billiards_dws.dws_ml_manual_order_alloc.allocated_amount IS '分摊金额 = order_amount × share_ratio';

CREATE INDEX idx_dws_ml_alloc_scope ON billiards_dws.dws_ml_manual_order_alloc (site_id, biz_date);
CREATE INDEX idx_dws_ml_alloc_member_assistant ON billiards_dws.dws_ml_manual_order_alloc (site_id, member_id, assistant_id);


-- -----------------------------------------------------------------------------
-- 29. dws_member_assistant_intimacy - 客户-助教亲密指数表（兼容保留）
-- 说明:
--   - 以"会员+助教"为粒度，衡量客户与助教的关系强度
--   - 用于助教约课精力分配和约课成功率预估
--   - 算法基于：频次强度、最近温度、归因充值、时长贡献、激增放大
--   - 附加课权重=基础课的1.5倍
--   - 会话合并：同一客人对同一助教，间隔<4小时算同次服务
--   - 充值归因：服务结束后1小时内的充值算做该助教贡献
--   - 散客(member_id=0)不参与计算
--   - 更新频率: 每4小时
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_member_assistant_intimacy CASCADE;
CREATE TABLE billiards_dws.dws_member_assistant_intimacy (
    intimacy_id         BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    tenant_id           BIGINT NOT NULL,              -- 租户ID
    member_id           BIGINT NOT NULL,              -- 会员ID
    assistant_id        BIGINT NOT NULL,              -- 助教ID（来自dim_assistant.assistant_id，通过user_id关联获取）
    
    -- 计算输入特征
    session_count       INTEGER NOT NULL DEFAULT 0,   -- 会话次数（合并后）
    total_duration_minutes INTEGER NOT NULL DEFAULT 0, -- 总服务时长(分钟)
    basic_session_count INTEGER NOT NULL DEFAULT 0,   -- 基础课次数
    incentive_session_count INTEGER NOT NULL DEFAULT 0, -- 附加课次数（激励课/超休）
    days_since_last_session INTEGER,                  -- 距最近一次服务的天数
    attributed_recharge_count INTEGER NOT NULL DEFAULT 0, -- 归因充值次数
    attributed_recharge_amount NUMERIC(14,2) NOT NULL DEFAULT 0, -- 归因充值金额
    
    -- 分项得分
    score_frequency     NUMERIC(10,4),                -- 频次强度 F: Σ(τ_i × decay(d_i, h_sess))
    score_recency       NUMERIC(10,4),                -- 最近温度 R: decay(d_last, h_last)
    score_recharge      NUMERIC(10,4),                -- 归因充值强度 M: Σ(ln(1+amt/A0) × decay(d_r, h_pay))
    score_duration      NUMERIC(10,4),                -- 时长贡献 D: Σ(sqrt(dur/60) × τ × decay(d, h_sess))
    burst_multiplier    NUMERIC(6,4),                 -- 激增放大因子: 1 + γ × burst
    
    -- 最终分数
    raw_score           NUMERIC(14,6),                -- Raw Score (无上限)
    display_score       NUMERIC(4,2),                 -- Display Score (0-10)
    
    -- 元数据
    calc_time           TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- 计算时间
    calc_version        INTEGER NOT NULL DEFAULT 1,   -- 计算版本
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT uk_dws_member_assistant_intimacy UNIQUE (site_id, member_id, assistant_id)
);

COMMENT ON TABLE billiards_dws.dws_member_assistant_intimacy IS '客户-助教亲密指数：衡量客户与助教的关系强度，用于约课分配';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.assistant_id IS '助教ID：来自dim_assistant.assistant_id，通过服务日志的user_id关联dim_assistant.user_id获取';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.incentive_session_count IS '附加课次数：skill_id=2790683529513798（附加课/激励课），权重1.5倍';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.attributed_recharge_count IS '归因充值：服务结束后1小时内发生的充值';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.score_frequency IS '频次强度F：课型加权(τ)×时间衰减，τ=1.5(附加课)/1.0(基础课)';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.burst_multiplier IS '激增放大：1+γ×max(0,ln(1+(F_short/F_long-1)))';
COMMENT ON COLUMN billiards_dws.dws_member_assistant_intimacy.raw_score IS 'Raw Score：(w_F×F+w_R×R+w_M×M+w_D×D)×mult';

CREATE INDEX idx_dws_intimacy_member ON billiards_dws.dws_member_assistant_intimacy (site_id, member_id, display_score DESC);
CREATE INDEX idx_dws_intimacy_assistant ON billiards_dws.dws_member_assistant_intimacy (site_id, assistant_id, display_score DESC);
CREATE INDEX idx_dws_intimacy_calc_time ON billiards_dws.dws_member_assistant_intimacy (calc_time);


-- -----------------------------------------------------------------------------
-- 30. dws_index_percentile_history - 分位点历史表
-- 说明:
--   - 记录每轮指数计算的分位点，用于EWMA平滑
--   - 防止分数分布轻微变化导致全员分数跳动
--   - 更新频率高时（如每小时）建议启用EWMA平滑
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS billiards_dws.dws_index_percentile_history CASCADE;
CREATE TABLE billiards_dws.dws_index_percentile_history (
    history_id          BIGSERIAL PRIMARY KEY,        -- 自增主键
    site_id             BIGINT NOT NULL,              -- 门店ID
    index_type          VARCHAR(50) NOT NULL,         -- 指数类型: RS/MS/ML/NCI/WBI（兼容 RECALL/INTIMACY）
    calc_time           TIMESTAMPTZ NOT NULL,         -- 计算时间
    
    -- 原始分位点
    percentile_5        NUMERIC(14,6),                -- 5分位（下锚）
    percentile_95       NUMERIC(14,6),                -- 95分位（上锚）
    
    -- EWMA平滑后的分位点
    percentile_5_smoothed NUMERIC(14,6),              -- 平滑后的5分位
    percentile_95_smoothed NUMERIC(14,6),             -- 平滑后的95分位
    
    -- 统计信息
    record_count        INTEGER,                      -- 记录数
    min_raw_score       NUMERIC(14,6),                -- 最小Raw Score
    max_raw_score       NUMERIC(14,6),                -- 最大Raw Score
    avg_raw_score       NUMERIC(14,6),                -- 平均Raw Score
    
    -- 元数据
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    CONSTRAINT uk_dws_index_percentile_history UNIQUE (site_id, index_type, calc_time)
);

COMMENT ON TABLE billiards_dws.dws_index_percentile_history IS '分位点历史表：记录每轮指数计算的分位点，用于EWMA平滑防抖';
COMMENT ON COLUMN billiards_dws.dws_index_percentile_history.percentile_5_smoothed IS 'EWMA平滑：Q_t=(1-α)×Q_{t-1}+α×Q_now，α=0.2';

CREATE INDEX idx_dws_percentile_history ON billiards_dws.dws_index_percentile_history (site_id, index_type, calc_time DESC);


-- =============================================================================
-- 完成提示
-- =============================================================================
-- DDL执行完成，共创建：
-- - 6张配置表（cfg_*）
-- - 5张助教维度表（dws_assistant_*）
-- - 客户维度表（dws_member_*）：包含召回/新客转化/唤回/亲密指数
-- - 1张关系指数表（dws_member_assistant_relation_index）
-- - 2张ML人工台账表（dws_ml_manual_order_source, dws_ml_manual_order_alloc）
-- - 7张财务维度表（dws_finance_* + dws_assistant_finance_* + dws_platform_*）
-- - 1张订单汇总表（dws_order_summary）
-- - 1张分位点历史表（dws_index_percentile_history）
-- - 1个召回优先级视图（v_member_recall_priority）
-- - 2个辅助函数（get_time_window, get_comparison_window）
