-- =============================================================================
-- 迁移脚本：创建 DWS 库存汇总表（日/周/月）
-- 日期: 2026-02-20
-- 依赖: dwd.dwd_goods_stock_summary 已存在
-- 说明: 三张表结构相同，仅 stat_period 默认值不同
-- 回滚: 见文件末尾 ROLLBACK 段
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- 1. 日度汇总
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dws.dws_goods_stock_daily_summary (
    site_id                   BIGINT       NOT NULL,
    tenant_id                 BIGINT,
    stat_date                 DATE         NOT NULL,
    site_goods_id             BIGINT       NOT NULL,
    goods_name                TEXT,
    goods_unit                TEXT,
    goods_category_id         BIGINT,
    goods_category_second_id  BIGINT,
    category_name             TEXT,
    range_start_stock         NUMERIC,
    range_end_stock           NUMERIC,
    range_in                  NUMERIC,
    range_out                 NUMERIC,
    range_sale                NUMERIC,
    range_sale_money          NUMERIC(12,2),
    range_inventory           NUMERIC,
    current_stock             NUMERIC,
    stat_period               TEXT         NOT NULL DEFAULT 'daily',
    created_at                TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at                TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    PRIMARY KEY (site_id, stat_date, site_goods_id)
);

COMMENT ON TABLE dws.dws_goods_stock_daily_summary
    IS '库存日度汇总：按门店+日期+商品汇总库存变动';

CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_daily_date
    ON dws.dws_goods_stock_daily_summary (stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_daily_goods
    ON dws.dws_goods_stock_daily_summary (site_goods_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_daily_site
    ON dws.dws_goods_stock_daily_summary (site_id, stat_date);

-- -----------------------------------------------------------------------------
-- 2. 周度汇总
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dws.dws_goods_stock_weekly_summary (
    site_id                   BIGINT       NOT NULL,
    tenant_id                 BIGINT,
    stat_date                 DATE         NOT NULL,
    site_goods_id             BIGINT       NOT NULL,
    goods_name                TEXT,
    goods_unit                TEXT,
    goods_category_id         BIGINT,
    goods_category_second_id  BIGINT,
    category_name             TEXT,
    range_start_stock         NUMERIC,
    range_end_stock           NUMERIC,
    range_in                  NUMERIC,
    range_out                 NUMERIC,
    range_sale                NUMERIC,
    range_sale_money          NUMERIC(12,2),
    range_inventory           NUMERIC,
    current_stock             NUMERIC,
    stat_period               TEXT         NOT NULL DEFAULT 'weekly',
    created_at                TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at                TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    PRIMARY KEY (site_id, stat_date, site_goods_id)
);

COMMENT ON TABLE dws.dws_goods_stock_weekly_summary
    IS '库存周度汇总：按门店+ISO周+商品汇总库存变动，stat_date 为周一日期';

CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_weekly_date
    ON dws.dws_goods_stock_weekly_summary (stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_weekly_goods
    ON dws.dws_goods_stock_weekly_summary (site_goods_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_weekly_site
    ON dws.dws_goods_stock_weekly_summary (site_id, stat_date);

-- -----------------------------------------------------------------------------
-- 3. 月度汇总
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS dws.dws_goods_stock_monthly_summary (
    site_id                   BIGINT       NOT NULL,
    tenant_id                 BIGINT,
    stat_date                 DATE         NOT NULL,
    site_goods_id             BIGINT       NOT NULL,
    goods_name                TEXT,
    goods_unit                TEXT,
    goods_category_id         BIGINT,
    goods_category_second_id  BIGINT,
    category_name             TEXT,
    range_start_stock         NUMERIC,
    range_end_stock           NUMERIC,
    range_in                  NUMERIC,
    range_out                 NUMERIC,
    range_sale                NUMERIC,
    range_sale_money          NUMERIC(12,2),
    range_inventory           NUMERIC,
    current_stock             NUMERIC,
    stat_period               TEXT         NOT NULL DEFAULT 'monthly',
    created_at                TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at                TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    PRIMARY KEY (site_id, stat_date, site_goods_id)
);

COMMENT ON TABLE dws.dws_goods_stock_monthly_summary
    IS '库存月度汇总：按门店+自然月+商品汇总库存变动，stat_date 为月首日期';

CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_monthly_date
    ON dws.dws_goods_stock_monthly_summary (stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_monthly_goods
    ON dws.dws_goods_stock_monthly_summary (site_goods_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_dws_goods_stock_monthly_site
    ON dws.dws_goods_stock_monthly_summary (site_id, stat_date);

COMMIT;

-- =============================================================================
-- 验证 SQL
-- =============================================================================
-- 1. 确认三张表已创建
-- SELECT table_name FROM information_schema.tables
--   WHERE table_schema = 'dws' AND table_name LIKE 'dws_goods_stock_%_summary';
--
-- 2. 确认主键约束
-- SELECT conname, conrelid::regclass
--   FROM pg_constraint
--   WHERE conrelid::regclass::text LIKE 'dws.dws_goods_stock_%_summary'
--     AND contype = 'p';
--
-- 3. 确认索引数量（每张表 3 个 + 1 个主键 = 4）
-- SELECT indexrelid::regclass, indrelid::regclass
--   FROM pg_index
--   WHERE indrelid::regclass::text LIKE 'dws.dws_goods_stock_%_summary';

-- =============================================================================
-- ROLLBACK（回滚脚本）
-- =============================================================================
-- DROP TABLE IF EXISTS dws.dws_goods_stock_daily_summary CASCADE;
-- DROP TABLE IF EXISTS dws.dws_goods_stock_weekly_summary CASCADE;
-- DROP TABLE IF EXISTS dws.dws_goods_stock_monthly_summary CASCADE;
