-- Data warehouse schema for the entertainment chain (ODS -> DWD -> DWS)
-- ASCII only to keep cross-platform friendly.

DROP SCHEMA IF EXISTS billiards_ods CASCADE;
CREATE SCHEMA IF NOT EXISTS billiards_ods;
CREATE SCHEMA IF NOT EXISTS billiards_dwd;
CREATE SCHEMA IF NOT EXISTS billiards_dws;

-- ---------- ODS (raw, lightly typed) ----------
-- Each ODS table keeps the source payload for replay/debug while exposing key fields.

CREATE TABLE IF NOT EXISTS billiards_ods.ods_member_profile (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    member_id           BIGINT          NOT NULL,
    system_member_id    BIGINT,
    register_site_id    BIGINT,
    site_name           TEXT,
    member_name         TEXT,
    nickname            TEXT,
    mobile              TEXT,
    gender              TEXT,
    birthday            DATE,
    register_time       TIMESTAMPTZ,
    member_type_id      BIGINT,
    member_type_name    TEXT,
    member_card_grade_code TEXT,
    status              TEXT,
    user_status         TEXT,
    balance             NUMERIC(18,2),
    points              NUMERIC(18,2),
    growth_value        NUMERIC(18,2),
    last_visit_time     TIMESTAMPTZ,
    wechat_id           TEXT,
    alipay_id           TEXT,
    member_card_no      TEXT,
    referrer_member_id  BIGINT,
    remarks             TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, member_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_member_card (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    card_id             BIGINT          NOT NULL,
    member_id           BIGINT,
    tenant_member_id    BIGINT,
    card_type_id        BIGINT,
    card_type_name      TEXT,
    card_no             TEXT,
    card_physics_type   TEXT,
    card_balance        NUMERIC(18,2),
    denomination        NUMERIC(18,2),
    discount_rate       NUMERIC(8,4),
    table_discount      NUMERIC(18,2),
    goods_discount      NUMERIC(18,2),
    assistant_discount  NUMERIC(18,2),
    assistant_reward_discount NUMERIC(18,2),
    valid_start_date    DATE,
    valid_end_date      DATE,
    disable_start_date  DATE,
    disable_end_date    DATE,
    last_consume_time   TIMESTAMPTZ,
    status              TEXT,
    is_delete           BOOLEAN,
    activate_time       TIMESTAMPTZ,
    deactivate_time     TIMESTAMPTZ,
    register_site_id    BIGINT,
    issuer_id           BIGINT,
    issuer_name         TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, card_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_balance_change (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    change_id           BIGINT          NOT NULL,
    member_id           BIGINT,
    tenant_member_id    BIGINT,
    tenant_member_card_id BIGINT,
    member_name         TEXT,
    member_mobile       TEXT,
    change_amount       NUMERIC(18,2),
    balance_before      NUMERIC(18,2),
    balance_after       NUMERIC(18,2),
    change_type         INT,
    payment_method      INT,
    refund_amount       NUMERIC(18,2),
    relate_id           BIGINT,
    register_site_id    BIGINT,
    register_site_name  TEXT,
    pay_site_name       TEXT,
    remark              TEXT,
    operator_id         BIGINT,
    operator_name       TEXT,
    change_time         TIMESTAMPTZ,
    is_deleted          BOOLEAN         DEFAULT FALSE,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, change_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_recharge_record (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    recharge_id         BIGINT          NOT NULL,
    member_id           BIGINT,
    recharge_amount     NUMERIC(18,2),
    gift_amount         NUMERIC(18,2),
    pay_method          INT,
    pay_trade_no        TEXT,
    order_trade_no      TEXT,
    recharge_time       TIMESTAMPTZ,
    status              TEXT,
    operator_id         BIGINT,
    operator_name       TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, recharge_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_product (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    goods_id            BIGINT          NOT NULL,
    goods_name          TEXT,
    goods_code          TEXT,
    category_id         BIGINT,
    category_name       TEXT,
    goods_second_category_id BIGINT,
    unit                TEXT,
    price               NUMERIC(18,2),
    cost_price          NUMERIC(18,2),
    market_price        NUMERIC(18,2),
    goods_state         TEXT,
    goods_cover         TEXT,
    goods_bar_code      TEXT,
    able_discount       BOOLEAN,
    is_delete           BOOLEAN,
    status              TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, goods_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_store_product (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    site_goods_id       BIGINT          NOT NULL,
    goods_id            BIGINT,
    goods_name          TEXT,
    category_id         BIGINT,
    category_name       TEXT,
    unit                TEXT,
    sale_price          NUMERIC(18,2),
    cost_price          NUMERIC(18,2),
    sale_num            NUMERIC(18,2),
    stock_a             NUMERIC(18,2),
    stock               NUMERIC(18,2),
    provisional_total_cost NUMERIC(18,2),
    total_purchase_cost NUMERIC(18,2),
    batch_stock_quantity NUMERIC(18,2),
    goods_state         TEXT,
    status              TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, site_goods_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_store_sale_item (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    sale_item_id        BIGINT          NOT NULL,
    order_goods_id      BIGINT,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    site_goods_id       BIGINT,
    goods_id            BIGINT,
    goods_name          TEXT,
    category_id         BIGINT,
    quantity            NUMERIC(18,4),
    unit_price          NUMERIC(18,2),
    original_amount     NUMERIC(18,2),
    discount_amount     NUMERIC(18,2),
    final_amount        NUMERIC(18,2),
    is_gift             BOOLEAN         DEFAULT FALSE,
    sale_time           TIMESTAMPTZ,
    member_id           BIGINT,
    salesman_id         BIGINT,
    operator_id         BIGINT,
    is_refunded         BOOLEAN         DEFAULT FALSE,
    discount_price      NUMERIC(18,2),
    cost_money          NUMERIC(18,2),
    coupon_deduct_amount NUMERIC(18,2),
    member_discount_amount NUMERIC(18,2),
    point_discount_money NUMERIC(18,2),
    point_discount_cost NUMERIC(18,2),
    sales_type          TEXT,
    goods_remark        TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, sale_item_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_table_info (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    table_id            BIGINT          NOT NULL,
    table_code          TEXT,
    table_name          TEXT,
    table_type          TEXT,
    area_name           TEXT,
    site_table_area_id  BIGINT,
    tenant_table_area_id BIGINT,
    table_price         NUMERIC(18,2),
    table_status        TEXT,
    audit_status        INT,
    show_status         INT,
    light_status        INT,
    virtual_table       BOOLEAN,
    is_rest_area        BOOLEAN,
    charge_free         BOOLEAN,
    table_cloth_use_time INT,
    table_cloth_use_cycle INT,
    status              TEXT,
    created_time        TIMESTAMPTZ,
    updated_time        TIMESTAMPTZ,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, table_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_table_use_log (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    ledger_id           BIGINT          NOT NULL,
    table_id            BIGINT,
    table_name          TEXT,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    start_time          TIMESTAMPTZ,
    end_time            TIMESTAMPTZ,
    duration_seconds    INT,
    billing_unit_price  NUMERIC(18,4),
    billing_count       NUMERIC(18,4),
    original_table_fee  NUMERIC(18,2),
    member_discount_amount NUMERIC(18,2),
    coupon_discount_amount NUMERIC(18,2),
    manual_discount_amount NUMERIC(18,2),
    service_fee         NUMERIC(18,2),
    final_table_fee     NUMERIC(18,2),
    member_id           BIGINT,
    operator_id         BIGINT,
    salesman_id         BIGINT,
    is_canceled         BOOLEAN         DEFAULT FALSE,
    cancel_time         TIMESTAMPTZ,
    site_table_area_id  BIGINT,
    tenant_table_area_id BIGINT,
    site_table_area_name TEXT,
    is_single_order     BOOLEAN,
    used_card_amount    NUMERIC(18,2),
    adjust_amount       NUMERIC(18,2),
    coupon_promotion_amount NUMERIC(18,2),
    service_money       NUMERIC(18,2),
    mgmt_fee            NUMERIC(18,2),
    fee_total           NUMERIC(18,2),
    real_table_use_seconds INT,
    last_use_time       TIMESTAMPTZ,
    ledger_start_time   TIMESTAMPTZ,
    ledger_end_time     TIMESTAMPTZ,
    ledger_status       INT,
    start_use_time      TIMESTAMPTZ,
    add_clock_seconds   INT,
    status              TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, ledger_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_table_fee_adjust (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    adjust_id           BIGINT          NOT NULL,
    ledger_id           BIGINT,
    order_trade_no      TEXT,
    discount_amount     NUMERIC(18,2),
    reason              TEXT,
    operator_id         BIGINT,
    operator_name       TEXT,
    created_at          TIMESTAMPTZ,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, adjust_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_assistant_account (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    assistant_id        BIGINT          NOT NULL,
    assistant_name      TEXT,
    mobile              TEXT,
    assistant_no        INT,
    team_id             BIGINT,
    team_name           TEXT,
    group_id            BIGINT,
    group_name          TEXT,
    job_num             TEXT,
    entry_type          TEXT,
    leave_status        TEXT,
    assistant_status    TEXT,
    allow_cx            BOOLEAN,
    status              TEXT,
    hired_date          DATE,
    left_date           DATE,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, assistant_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_assistant_service_log (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    ledger_id           BIGINT          NOT NULL,
    assistant_id        BIGINT,
    assistant_name      TEXT,
    service_type        TEXT,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    start_time          TIMESTAMPTZ,
    end_time            TIMESTAMPTZ,
    duration_seconds    INT,
    original_fee        NUMERIC(18,2),
    member_discount_amount NUMERIC(18,2),
    manual_discount_amount NUMERIC(18,2),
    coupon_discount_amount NUMERIC(18,2),
    final_fee           NUMERIC(18,2),
    member_id           BIGINT,
    operator_id         BIGINT,
    salesman_id         BIGINT,
    is_canceled         BOOLEAN         DEFAULT FALSE,
    cancel_time         TIMESTAMPTZ,
    skill_grade         NUMERIC(10,2),
    service_grade       NUMERIC(10,2),
    composite_grade     NUMERIC(10,2),
    overall_score       NUMERIC(10,2),
    status              TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, ledger_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_assistant_cancel_log (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    cancel_id           BIGINT          NOT NULL,
    ledger_id           BIGINT,
    assistant_id        BIGINT,
    order_trade_no      TEXT,
    table_id            BIGINT,
    table_area_id       BIGINT,
    table_area_name     TEXT,
    table_name          TEXT,
    assistant_on        INT,
    pd_charge_minutes   INT,
    assistant_abolish_amount NUMERIC(18,2),
    reason              TEXT,
    cancel_time         TIMESTAMPTZ,
    operator_id         BIGINT,
    operator_name       TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, cancel_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_group_package (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    package_id          BIGINT          NOT NULL,
    package_name        TEXT,
    table_area_id       BIGINT,
    table_area_name     TEXT,
    platform_code       TEXT,
    status              TEXT,
    face_price          NUMERIC(18,2),
    settle_price        NUMERIC(18,2),
    selling_price       NUMERIC(18,2),
    duration            INT,
    valid_from          DATE,
    valid_to            DATE,
    start_time          TIMESTAMPTZ,
    end_time            TIMESTAMPTZ,
    is_enabled          BOOLEAN,
    is_delete           BOOLEAN,
    package_type        TEXT,
    usable_count        INT,
    creator_name        TEXT,
    tenant_table_area_id BIGINT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, package_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_group_package_log (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    usage_id            BIGINT          NOT NULL,
    package_id          BIGINT,
    coupon_id           BIGINT,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    status              TEXT,
    used_time           TIMESTAMPTZ,
    deduct_amount       NUMERIC(18,2),
    settle_price        NUMERIC(18,2),
    coupon_code         TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, usage_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_platform_coupon_log (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    coupon_id           BIGINT          NOT NULL,
    platform_code       TEXT,
    verify_code         TEXT,
    coupon_code         TEXT,
    coupon_channel      TEXT,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    status              TEXT,
    used_time           TIMESTAMPTZ,
    deduct_amount       NUMERIC(18,2),
    settle_price        NUMERIC(18,2),
    coupon_money        NUMERIC(18,2),
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, coupon_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_inventory_change (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    change_id           BIGINT          NOT NULL,
    site_goods_id       BIGINT,
    goods_id            BIGINT,
    stock_type          TEXT,
    change_amount       NUMERIC(18,2),
    before_stock        NUMERIC(18,2),
    after_stock         NUMERIC(18,2),
    change_amount_alt   NUMERIC(18,2),
    before_stock_alt    NUMERIC(18,2),
    after_stock_alt     NUMERIC(18,2),
    change_type         TEXT,
    relate_id           BIGINT,
    unit                TEXT,
    price               NUMERIC(18,2),
    goods_category_id   BIGINT,
    goods_second_category_id BIGINT,
    remark              TEXT,
    operator_id         BIGINT,
    operator_name       TEXT,
    change_time         TIMESTAMPTZ,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, change_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_inventory_stock (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    site_goods_id       BIGINT          NOT NULL,
    goods_id            BIGINT,
    goods_name          TEXT,
    goods_unit          TEXT,
    goods_category_id   BIGINT,
    goods_second_category_id BIGINT,
    range_start_stock   NUMERIC(18,2),
    range_end_stock     NUMERIC(18,2),
    range_in            NUMERIC(18,2),
    range_out           NUMERIC(18,2),
    range_inventory     NUMERIC(18,2),
    range_sale          NUMERIC(18,2),
    range_sale_money    NUMERIC(18,2),
    current_stock       NUMERIC(18,2),
    cost_price          NUMERIC(18,2),
    category_name       TEXT,
    snapshot_key        TEXT            NOT NULL DEFAULT 'default',
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, site_goods_id, snapshot_key)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_order_settle (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    order_settle_id     BIGINT          NOT NULL,
    settle_relate_id    BIGINT,
    settle_name         TEXT,
    settle_type         INT,
    settle_status       INT,
    member_id           BIGINT,
    member_name         TEXT,
    member_phone        TEXT,
    table_id            BIGINT,
    consume_money       NUMERIC(18,2),
    table_charge_money  NUMERIC(18,2),
    goods_money         NUMERIC(18,2),
    service_money       NUMERIC(18,2),
    assistant_pd_money  NUMERIC(18,2),
    assistant_cx_money  NUMERIC(18,2),
    pay_amount          NUMERIC(18,2),
    cash_amount         NUMERIC(18,2),
    online_amount       NUMERIC(18,2),
    point_amount        NUMERIC(18,2),
    coupon_amount       NUMERIC(18,2),
    card_amount         NUMERIC(18,2),
    balance_amount      NUMERIC(18,2),
    refund_amount       NUMERIC(18,2),
    prepay_money        NUMERIC(18,2),
    adjust_amount       NUMERIC(18,2),
    rounding_amount     NUMERIC(18,2),
    member_discount_amount NUMERIC(18,2),
    coupon_sale_amount  NUMERIC(18,2),
    goods_promotion_money NUMERIC(18,2),
    assistant_promotion_money NUMERIC(18,2),
    point_discount_price NUMERIC(18,2),
    point_discount_cost NUMERIC(18,2),
    real_goods_money    NUMERIC(18,2),
    assistant_manual_discount NUMERIC(18,2),
    all_coupon_discount NUMERIC(18,2),
    is_use_coupon       BOOLEAN,
    is_use_discount     BOOLEAN,
    is_activity         BOOLEAN,
    is_bind_member      BOOLEAN,
    is_first            BOOLEAN,
    recharge_card_amount NUMERIC(18,2),
    gift_card_amount    NUMERIC(18,2),
    payment_method      INT,
    create_time         TIMESTAMPTZ,
    pay_time            TIMESTAMPTZ,
    revoke_order_id     BIGINT,
    revoke_order_name   TEXT,
    revoke_time         TIMESTAMPTZ,
    can_be_revoked      BOOLEAN,
    serial_number       TEXT,
    sales_man_name      TEXT,
    sales_man_user_id   BIGINT,
    order_remark        TEXT,
    operator_id         BIGINT,
    operator_name       TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, order_settle_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_goods_category (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    category_id         BIGINT          NOT NULL,
    category_name       TEXT,
    parent_id           BIGINT,
    level_no            INT,
    status              TEXT,
    remark              TEXT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, category_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_payment_record (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    pay_id              BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    pay_method_code     TEXT,
    pay_method_name     TEXT,
    pay_status          INT,
    pay_amount          NUMERIC(18,2),
    pay_time            TIMESTAMPTZ,
    online_pay_channel  TEXT,
    transaction_id      TEXT,
    operator_id         BIGINT,
    remark              TEXT,
    relate_type         TEXT,
    relate_id           BIGINT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, pay_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_refund_record (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    refund_id           BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    pay_sn              TEXT,
    pay_amount          NUMERIC(18,2),
    pay_status          INT,
    is_revoke           BOOLEAN,
    is_delete           BOOLEAN,
    online_pay_channel  TEXT,
    pay_method_code     TEXT,
    refund_amount       NUMERIC(18,2),
    refund_time         TIMESTAMPTZ,
    action_type         INT,
    pay_terminal        INT,
    pay_config_id       BIGINT,
    cashier_point_id    BIGINT,
    operator_id         BIGINT,
    member_card_id      BIGINT,
    balance_frozen_amount NUMERIC(18,2),
    card_frozen_amount  NUMERIC(18,2),
    round_amount        NUMERIC(18,2),
    online_pay_type     INT,
    channel_payer_id    TEXT,
    channel_pay_no      TEXT,
    check_status        INT,
    channel_fee         NUMERIC(18,2),
    relate_type         TEXT,
    relate_id           BIGINT,
    status              TEXT,
    reason              TEXT,
    related_payment_id  BIGINT,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, refund_id)
);

CREATE TABLE IF NOT EXISTS billiards_ods.ods_order_receipt_detail (
    tenant_id           BIGINT,
    site_id             BIGINT          NOT NULL,
    order_settle_id     BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_number TEXT,
    settle_type         INT,
    receipt_no          TEXT,
    receipt_time        TIMESTAMPTZ,
    total_amount        NUMERIC(18,2),
    discount_amount     NUMERIC(18,2),
    final_amount        NUMERIC(18,2),
    actual_payment      NUMERIC(18,2),
    ledger_amount       NUMERIC(18,2),
    member_offer_amount NUMERIC(18,2),
    delivery_fee        NUMERIC(18,2),
    adjust_amount       NUMERIC(18,2),
    payment_method      INT,
    pay_time            TIMESTAMPTZ,
    member_id           BIGINT,
    order_remark        TEXT,
    cashier_name        TEXT,
    ticket_remark       TEXT,
    ticket_custom_content TEXT,
    voucher_money       NUMERIC(18,2),
    reward_name         TEXT,
    consume_money       NUMERIC(18,2),
    refund_amount       NUMERIC(18,2),
    balance_amount      NUMERIC(18,2),
    coupon_amount       NUMERIC(18,2),
    member_deduct_amount NUMERIC(18,2),
    prepay_money        NUMERIC(18,2),
    delivery_address    TEXT,
    snapshot_raw        JSONB,
    member_snapshot     JSONB,
    source_file         TEXT,
    source_endpoint     TEXT,
    fetched_at          TIMESTAMPTZ     DEFAULT now(),
    payload             JSONB           NOT NULL,
    PRIMARY KEY (site_id, order_settle_id)
);

-- ---------- DWD Dimensions ----------

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_tenant (
    tenant_id       BIGINT PRIMARY KEY,
    tenant_name     TEXT,
    short_name      TEXT,
    status          TEXT,
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_site (
    site_id         BIGINT PRIMARY KEY,
    tenant_id       BIGINT,
    site_code       TEXT,
    site_name       TEXT,
    city            TEXT,
    region          TEXT,
    status          TEXT,
    open_date       DATE,
    close_date      DATE,
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_member_card_type (
    card_type_id    BIGINT PRIMARY KEY,
    card_type_name  TEXT,
    discount_rate   NUMERIC(8,4),
    description     TEXT,
    remark          TEXT
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_product_category (
    category_id     BIGINT PRIMARY KEY,
    category_name   TEXT,
    parent_id       BIGINT,
    level_no        INT,
    status          TEXT,
    remark          TEXT
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_product (
    goods_id        BIGINT PRIMARY KEY,
    goods_name      TEXT,
    goods_code      TEXT,
    category_id     BIGINT REFERENCES billiards_dwd.dim_product_category (category_id),
    category_name   TEXT,
    unit            TEXT,
    default_price   NUMERIC(18,2),
    status          TEXT,
    updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_table (
    table_id        BIGINT PRIMARY KEY,
    site_id         BIGINT,
    table_code      TEXT,
    table_name      TEXT,
    table_type      TEXT,
    area_name       TEXT,
    status          TEXT,
    created_time    TIMESTAMPTZ,
    updated_time    TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_assistant_team (
    team_id         BIGINT PRIMARY KEY,
    team_name       TEXT,
    remark          TEXT,
    updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_assistant (
    assistant_id    BIGINT PRIMARY KEY,
    assistant_name  TEXT,
    mobile          TEXT,
    team_id         BIGINT REFERENCES billiards_dwd.dim_assistant_team (team_id),
    status          TEXT,
    hired_date      DATE,
    left_date       DATE,
    updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_pay_method (
    pay_method_code TEXT PRIMARY KEY,
    pay_method_name TEXT,
    is_stored_value BOOLEAN DEFAULT FALSE,
    status          TEXT,
    updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_order_assist_type (
    assist_type_code TEXT PRIMARY KEY,
    assist_type_name TEXT,
    description      TEXT
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_coupon_platform (
    platform_code   TEXT PRIMARY KEY,
    platform_name   TEXT,
    description     TEXT
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_date (
    date_key        DATE PRIMARY KEY,
    year_no         INT,
    month_no        INT,
    day_no          INT,
    week_no         INT,
    day_of_week     INT,
    month_name      TEXT
);

CREATE TABLE IF NOT EXISTS billiards_dwd.dim_member (
    site_id                 BIGINT,
    member_id               BIGINT,
    tenant_id               BIGINT,
    member_name             TEXT,
    nickname                TEXT,
    gender                  TEXT,
    birthday                DATE,
    mobile                  TEXT,
    member_type_id          BIGINT REFERENCES billiards_dwd.dim_member_card_type (card_type_id),
    member_type_name        TEXT,
    status                  TEXT,
    register_time           TIMESTAMPTZ,
    valid_from              DATE,
    valid_to                DATE,
    last_visit_time         TIMESTAMPTZ,
    balance                 NUMERIC(18,2),
    total_recharge_amount   NUMERIC(18,2),
    total_consumed_amount   NUMERIC(18,2),
    wechat_id               TEXT,
    alipay_id               TEXT,
    remark                  TEXT,
    updated_at              TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (site_id, member_id)
);

-- ---------- DWD Facts ----------

CREATE TABLE IF NOT EXISTS billiards_dwd.fact_sale_item (
    site_id             BIGINT          NOT NULL,
    sale_item_id        BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    goods_id            BIGINT REFERENCES billiards_dwd.dim_product (goods_id),
    category_id         BIGINT REFERENCES billiards_dwd.dim_product_category (category_id),
    quantity            NUMERIC(18,4),
    original_amount     NUMERIC(18,2),
    discount_amount     NUMERIC(18,2),
    final_amount        NUMERIC(18,2),
    is_gift             BOOLEAN         DEFAULT FALSE,
    sale_time           TIMESTAMPTZ,
    created_at          TIMESTAMPTZ     DEFAULT now(),
    updated_at          TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, sale_item_id)
);

CREATE TABLE IF NOT EXISTS billiards_dwd.fact_table_usage (
    site_id             BIGINT          NOT NULL,
    ledger_id           BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    table_id            BIGINT REFERENCES billiards_dwd.dim_table (table_id),
    member_id           BIGINT,
    start_time          TIMESTAMPTZ,
    end_time            TIMESTAMPTZ,
    duration_minutes    INT,
    original_table_fee  NUMERIC(18,2),
    member_discount_amount NUMERIC(18,2),
    manual_discount_amount NUMERIC(18,2),
    final_table_fee     NUMERIC(18,2),
    is_canceled         BOOLEAN         DEFAULT FALSE,
    cancel_time         TIMESTAMPTZ,
    created_at          TIMESTAMPTZ     DEFAULT now(),
    updated_at          TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, ledger_id)
);

CREATE TABLE IF NOT EXISTS billiards_dwd.fact_assistant_service (
    site_id             BIGINT          NOT NULL,
    ledger_id           BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    assistant_id        BIGINT REFERENCES billiards_dwd.dim_assistant (assistant_id),
    assist_type_code    TEXT REFERENCES billiards_dwd.dim_order_assist_type (assist_type_code),
    member_id           BIGINT,
    start_time          TIMESTAMPTZ,
    end_time            TIMESTAMPTZ,
    duration_minutes    INT,
    original_fee        NUMERIC(18,2),
    member_discount_amount NUMERIC(18,2),
    manual_discount_amount NUMERIC(18,2),
    final_fee           NUMERIC(18,2),
    is_canceled         BOOLEAN         DEFAULT FALSE,
    cancel_time         TIMESTAMPTZ,
    created_at          TIMESTAMPTZ     DEFAULT now(),
    updated_at          TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, ledger_id)
);

CREATE TABLE IF NOT EXISTS billiards_dwd.fact_coupon_usage (
    site_id             BIGINT          NOT NULL,
    coupon_id           BIGINT          NOT NULL,
    package_id          BIGINT,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    platform_code       TEXT REFERENCES billiards_dwd.dim_coupon_platform (platform_code),
    status              TEXT,
    deduct_amount       NUMERIC(18,2),
    settle_price        NUMERIC(18,2),
    used_time           TIMESTAMPTZ,
    created_at          TIMESTAMPTZ     DEFAULT now(),
    updated_at          TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, coupon_id)
);

CREATE TABLE IF NOT EXISTS billiards_dwd.fact_payment (
    site_id             BIGINT          NOT NULL,
    pay_id              BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    pay_method_code     TEXT REFERENCES billiards_dwd.dim_pay_method (pay_method_code),
    pay_amount          NUMERIC(18,2),
    pay_time            TIMESTAMPTZ,
    relate_type         TEXT,
    relate_id           BIGINT,
    created_at          TIMESTAMPTZ     DEFAULT now(),
    updated_at          TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, pay_id)
);

CREATE TABLE IF NOT EXISTS billiards_dwd.fact_refund (
    site_id             BIGINT          NOT NULL,
    refund_id           BIGINT          NOT NULL,
    order_trade_no      TEXT,
    order_settle_id     BIGINT,
    member_id           BIGINT,
    pay_method_code     TEXT REFERENCES billiards_dwd.dim_pay_method (pay_method_code),
    refund_amount       NUMERIC(18,2),
    refund_time         TIMESTAMPTZ,
    status              TEXT,
    created_at          TIMESTAMPTZ     DEFAULT now(),
    updated_at          TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, refund_id)
);

CREATE TABLE IF NOT EXISTS billiards_dwd.fact_balance_change (
    site_id             BIGINT          NOT NULL,
    change_id           BIGINT          NOT NULL,
    member_id           BIGINT,
    change_type         INT,
    relate_type         TEXT,
    relate_id           BIGINT,
    pay_method_code     TEXT REFERENCES billiards_dwd.dim_pay_method (pay_method_code),
    change_amount       NUMERIC(18,2),
    balance_before      NUMERIC(18,2),
    balance_after       NUMERIC(18,2),
    change_time         TIMESTAMPTZ,
    created_at          TIMESTAMPTZ     DEFAULT now(),
    updated_at          TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, change_id)
);

-- ---------- DWS (serving layers) ----------

CREATE TABLE IF NOT EXISTS billiards_dws.dws_order_summary (
    site_id                         BIGINT          NOT NULL,
    order_settle_id                 BIGINT          NOT NULL,
    order_trade_no                  TEXT,
    order_date                      DATE,
    tenant_id                       BIGINT,
    member_id                       BIGINT,
    member_flag                     BOOLEAN         DEFAULT FALSE,
    recharge_order_flag             BOOLEAN         DEFAULT FALSE,
    item_count                      INT,
    total_item_quantity             NUMERIC(18,4),
    table_fee_amount                NUMERIC(18,2),
    assistant_service_amount        NUMERIC(18,2),
    goods_amount                    NUMERIC(18,2),
    group_amount                    NUMERIC(18,2),
    total_coupon_deduction          NUMERIC(18,2),
    member_discount_amount          NUMERIC(18,2),
    manual_discount_amount          NUMERIC(18,2),
    order_original_amount           NUMERIC(18,2),
    order_final_amount              NUMERIC(18,2),
    stored_card_deduct              NUMERIC(18,2),
    external_paid_amount            NUMERIC(18,2),
    total_paid_amount               NUMERIC(18,2),
    book_table_flow                 NUMERIC(18,2),
    book_assistant_flow             NUMERIC(18,2),
    book_goods_flow                 NUMERIC(18,2),
    book_group_flow                 NUMERIC(18,2),
    book_order_flow                 NUMERIC(18,2),
    order_effective_consume_cash    NUMERIC(18,2),
    order_effective_recharge_cash   NUMERIC(18,2),
    order_effective_flow            NUMERIC(18,2),
    refund_amount                   NUMERIC(18,2),
    net_income                      NUMERIC(18,2),
    created_at                      TIMESTAMPTZ     DEFAULT now(),
    updated_at                      TIMESTAMPTZ     DEFAULT now(),
    PRIMARY KEY (site_id, order_settle_id)
);

-- ---------- etl_admin (scheduler, cursor, run tracking) ----------
CREATE SCHEMA IF NOT EXISTS etl_admin;

CREATE TABLE IF NOT EXISTS etl_admin.etl_task (
    task_id                 BIGSERIAL PRIMARY KEY,
    task_code               TEXT            NOT NULL,
    store_id                BIGINT          NOT NULL,
    enabled                 BOOLEAN         DEFAULT TRUE,
    cursor_field            TEXT,
    window_minutes_default  INT             DEFAULT 30,
    overlap_seconds         INT             DEFAULT 120,
    page_size               INT             DEFAULT 200,
    retry_max               INT             DEFAULT 3,
    params                  JSONB           DEFAULT '{}'::jsonb,
    created_at              TIMESTAMPTZ     DEFAULT now(),
    updated_at              TIMESTAMPTZ     DEFAULT now(),
    UNIQUE (task_code, store_id)
);

CREATE TABLE IF NOT EXISTS etl_admin.etl_cursor (
    cursor_id   BIGSERIAL PRIMARY KEY,
    task_id     BIGINT      NOT NULL REFERENCES etl_admin.etl_task(task_id) ON DELETE CASCADE,
    store_id    BIGINT      NOT NULL,
    last_start  TIMESTAMPTZ,
    last_end    TIMESTAMPTZ,
    last_id     BIGINT,
    last_run_id BIGINT,
    extra       JSONB       DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ DEFAULT now(),
    updated_at  TIMESTAMPTZ DEFAULT now(),
    UNIQUE (task_id, store_id)
);

CREATE TABLE IF NOT EXISTS etl_admin.etl_run (
    run_id          BIGSERIAL PRIMARY KEY,
    run_uuid        TEXT            NOT NULL,
    task_id         BIGINT          NOT NULL REFERENCES etl_admin.etl_task(task_id) ON DELETE CASCADE,
    store_id        BIGINT          NOT NULL,
    status          TEXT            NOT NULL,
    started_at      TIMESTAMPTZ     DEFAULT now(),
    ended_at        TIMESTAMPTZ,
    window_start    TIMESTAMPTZ,
    window_end      TIMESTAMPTZ,
    window_minutes  INT,
    overlap_seconds INT,
    fetched_count   INT             DEFAULT 0,
    loaded_count    INT             DEFAULT 0,
    updated_count   INT             DEFAULT 0,
    skipped_count   INT             DEFAULT 0,
    error_count     INT             DEFAULT 0,
    unknown_fields  INT             DEFAULT 0,
    export_dir      TEXT,
    log_path        TEXT,
    request_params  JSONB           DEFAULT '{}'::jsonb,
    manifest        JSONB           DEFAULT '{}'::jsonb,
    error_message   TEXT,
    extra           JSONB           DEFAULT '{}'::jsonb
);

-- Default task registry seed (idempotent)
INSERT INTO etl_admin.etl_task (task_code, store_id, enabled)
VALUES
    ('PRODUCTS', 2790685415443269, TRUE),
    ('TABLES', 2790685415443269, TRUE),
    ('MEMBERS', 2790685415443269, TRUE),
    ('ASSISTANTS', 2790685415443269, TRUE),
    ('PACKAGES_DEF', 2790685415443269, TRUE),
    ('ORDERS', 2790685415443269, TRUE),
    ('PAYMENTS', 2790685415443269, TRUE),
    ('REFUNDS', 2790685415443269, TRUE),
    ('COUPON_USAGE', 2790685415443269, TRUE),
    ('INVENTORY_CHANGE', 2790685415443269, TRUE),
    ('TOPUPS', 2790685415443269, TRUE),
    ('TABLE_DISCOUNT', 2790685415443269, TRUE),
    ('ASSISTANT_ABOLISH', 2790685415443269, TRUE),
    ('LEDGER', 2790685415443269, TRUE),
    ('TICKET_DWD', 2790685415443269, TRUE),
    ('PAYMENTS_DWD', 2790685415443269, TRUE),
    ('MEMBERS_DWD', 2790685415443269, TRUE),
    ('MANUAL_INGEST', 2790685415443269, TRUE),
    ('ODS_ORDER_SETTLE', 2790685415443269, TRUE),
    ('ODS_TABLE_USE', 2790685415443269, TRUE),
    ('ODS_ASSISTANT_LEDGER', 2790685415443269, TRUE),
    ('ODS_ASSISTANT_ABOLISH', 2790685415443269, TRUE),
    ('ODS_GOODS_LEDGER', 2790685415443269, TRUE),
    ('ODS_PAYMENT', 2790685415443269, TRUE),
    ('ODS_REFUND', 2790685415443269, TRUE),
    ('ODS_COUPON_VERIFY', 2790685415443269, TRUE),
    ('ODS_MEMBER', 2790685415443269, TRUE),
    ('ODS_MEMBER_CARD', 2790685415443269, TRUE),
    ('ODS_PACKAGE', 2790685415443269, TRUE),
    ('ODS_INVENTORY_STOCK', 2790685415443269, TRUE),
    ('ODS_INVENTORY_CHANGE', 2790685415443269, TRUE)
ON CONFLICT (task_code, store_id) DO NOTHING;
