-- ============================================================
-- 报表引擎 — 快手电商演示数据初始化脚本
-- ============================================================
-- 使用方式：
--   PGPASSWORD='Bjang09@686^*^' psql -h 116.204.74.41 \
--     -p 15432 -U postgres -d engine -f demo_kuaishou.sql
-- ============================================================

BEGIN;

-- ============================================================
-- 第〇步：确保系统表存在（CREATE TABLE IF NOT EXISTS）
-- ============================================================

CREATE TABLE IF NOT EXISTS report_business_config (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    business_code VARCHAR(64) NOT NULL,
    business_name VARCHAR(128) NOT NULL,
    description TEXT DEFAULT '',
    status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
    config JSONB DEFAULT '{}',
    creator VARCHAR(64) DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updater VARCHAR(64) DEFAULT '',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE TABLE IF NOT EXISTS report_report_config (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    business_code VARCHAR(64) NOT NULL,
    report_code VARCHAR(64) NOT NULL,
    report_name VARCHAR(128) NOT NULL,
    description TEXT DEFAULT '',
    status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
    stat_table_name VARCHAR(128) NOT NULL,
    stat_table_comment VARCHAR(256) DEFAULT '',
    date_field VARCHAR(64) DEFAULT 'stat_date',
    primary_keys JSONB DEFAULT '["id"]'::jsonb,
    conflict_keys JSONB DEFAULT '["stat_date"]'::jsonb,
    config JSONB DEFAULT '{}',
    creator VARCHAR(64) DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updater VARCHAR(64) DEFAULT '',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT uk_business_report_code UNIQUE (tenant_id, business_code, report_code)
);

CREATE TABLE IF NOT EXISTS report_field_config (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    business_code VARCHAR(64) NOT NULL,
    report_code VARCHAR(64) NOT NULL,
    field_code VARCHAR(64) NOT NULL,
    field_name VARCHAR(128) NOT NULL,
    field_type VARCHAR(32) NOT NULL,
    data_type VARCHAR(32) NOT NULL DEFAULT 'STRING',
    field_role VARCHAR(32) NOT NULL,
    is_aggregatable BOOLEAN DEFAULT FALSE,
    is_filterable BOOLEAN DEFAULT TRUE,
    is_queryable BOOLEAN DEFAULT TRUE,
    is_sortable BOOLEAN DEFAULT TRUE,
    default_aggregate VARCHAR(32) DEFAULT '',
    valid_aggregates JSONB DEFAULT '[]'::jsonb,
    filter_operators JSONB DEFAULT '["=","!=",">","<",">=","<=","IN","LIKE","BETWEEN"]'::jsonb,
    expression VARCHAR(512) DEFAULT '',
    expression_type VARCHAR(32) DEFAULT '',
    format_pattern VARCHAR(64) DEFAULT '',
    unit VARCHAR(32) DEFAULT '',
    dict_code VARCHAR(64) DEFAULT '',
    sort_order INT DEFAULT 0,
    group_name VARCHAR(64) DEFAULT '',
    status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
    creator VARCHAR(64) DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updater VARCHAR(64) DEFAULT '',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT uk_business_report_field_code UNIQUE (tenant_id, business_code, report_code, field_code)
);

CREATE TABLE IF NOT EXISTS report_extract_config (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    business_code VARCHAR(64) NOT NULL,
    report_code VARCHAR(64) NOT NULL,
    extract_code VARCHAR(64) NOT NULL,
    extract_name VARCHAR(128) NOT NULL,
    source_table_name VARCHAR(128) NOT NULL,
    source_table_alias VARCHAR(64) DEFAULT '',
    target_table_name VARCHAR(128) NOT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    extract_type VARCHAR(32) NOT NULL DEFAULT 'FULL',
    extract_mode VARCHAR(32) NOT NULL DEFAULT 'DIRECT',
    extract_key_field VARCHAR(64) DEFAULT '',
    extract_key_format VARCHAR(64) DEFAULT '',
    group_by_fields JSONB DEFAULT '[]'::jsonb,
    filter_expression TEXT DEFAULT '',
    join_configs JSONB DEFAULT '[]'::jsonb,
    field_mappings JSONB DEFAULT '[]'::jsonb,
    transform_rules JSONB DEFAULT '[]'::jsonb,
    batch_size INT DEFAULT 1000,
    status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
    creator VARCHAR(64) DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updater VARCHAR(64) DEFAULT '',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT uk_business_report_extract_code UNIQUE (tenant_id, business_code, report_code, extract_code)
);

CREATE TABLE IF NOT EXISTS report_extract_log (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    business_code VARCHAR(64) NOT NULL,
    report_code VARCHAR(64) NOT NULL,
    extract_code VARCHAR(64) NOT NULL,
    stat_date VARCHAR(16) NOT NULL,
    extract_type VARCHAR(32) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'RUNNING',
    total_count INT DEFAULT 0,
    success_count INT DEFAULT 0,
    fail_count INT DEFAULT 0,
    start_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    end_time TIMESTAMP WITH TIME ZONE,
    error_message TEXT DEFAULT '',
    executor VARCHAR(64) DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT uk_extract_keys UNIQUE (tenant_id, business_code, report_code, extract_code, stat_date)
);

-- ============================================================
-- 第一步：创建源数据表 + 模拟数据
-- ============================================================

-- 店铺表
DROP TABLE IF EXISTS demo_kuaishou_shop CASCADE;
CREATE TABLE demo_kuaishou_shop (
    shop_id VARCHAR(64) PRIMARY KEY,
    shop_name VARCHAR(128) NOT NULL,
    shop_level VARCHAR(16) DEFAULT '普通',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

INSERT INTO demo_kuaishou_shop VALUES
('KS001', '快手官方旗舰店', '旗舰'),
('KS002', '数码3C专营店', '高级'),
('KS003', '美妆个护优选', '普通'),
('KS004', '食品生鲜超市', '高级'),
('KS005', '服装潮流馆', '普通');

-- 订单明细表
DROP TABLE IF EXISTS demo_kuaishou_orders CASCADE;
CREATE TABLE demo_kuaishou_orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL,
    shop_id VARCHAR(64) NOT NULL,
    order_status VARCHAR(16) NOT NULL DEFAULT '已完成',
    product_name VARCHAR(256),
    product_category VARCHAR(64),
    quantity INT DEFAULT 1,
    unit_price NUMERIC(12,2) DEFAULT 0,
    order_amount NUMERIC(12,2) DEFAULT 0,
    refund_amount NUMERIC(12,2) DEFAULT 0,
    shipping_cost NUMERIC(10,2) DEFAULT 0,
    payment_method VARCHAR(32) DEFAULT '微信',
    buyer_id VARCHAR(64) DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 生成30天模拟订单
DO $$
DECLARE
    v_date DATE;
    v_order_date DATE := CURRENT_DATE - INTERVAL '30 days';
    v_order_count INT;
    v_shop_id VARCHAR(64);
    v_shop_ids VARCHAR[] := ARRAY['KS001','KS002','KS003','KS004','KS005'];
    v_categories VARCHAR[] := ARRAY['手机','电脑','美妆','食品','服装'];
    v_payments VARCHAR[] := ARRAY['微信','支付宝','快手支付'];
    v_statuses VARCHAR[] := ARRAY['已完成','已完成','已完成','已完成','已完成','已完成','已完成','已取消','已完成'];
    v_price NUMERIC(12,2);
    v_qty INT;
BEGIN
    FOR day_offset IN 0..29 LOOP
        v_date := v_order_date + day_offset;
        v_order_count := 20 + floor(random() * 31)::int;
        FOR i IN 1..v_order_count LOOP
            v_shop_id := v_shop_ids[1 + floor(random() * 5)::int];
            v_qty := 1 + floor(random() * 5)::int;
            v_price := round((50 + random() * 2000)::numeric, 2);
            INSERT INTO demo_kuaishou_orders (order_no, shop_id, order_status, product_name,
                product_category, quantity, unit_price, order_amount, refund_amount,
                shipping_cost, payment_method, buyer_id, created_at, updated_at)
            VALUES (
                'KS' || to_char(v_date, 'YYYYMMDD') || '-' || lpad(i::text, 4, '0'),
                v_shop_id, v_statuses[1 + floor(random() * 9)::int],
                '商品' || (1000 + floor(random() * 9000)::int),
                v_categories[1 + floor(random() * 5)::int],
                v_qty, v_price, round((v_price * v_qty)::numeric, 2),
                CASE WHEN random() < 0.1 THEN round((v_price * v_qty * random() * 0.5)::numeric, 2) ELSE 0 END,
                round((5 + random() * 15)::numeric, 2),
                v_payments[1 + floor(random() * 3)::int],
                'buyer_' || (10000 + floor(random() * 90000)::int),
                v_date + time '08:00:00' + (random() * interval '12 hours'),
                v_date + time '08:00:00' + (random() * interval '12 hours')
            );
        END LOOP;
    END LOOP;
END $$;

ANALYZE demo_kuaishou_orders;

-- ============================================================
-- 第二步：插入系统配置（先清空避免重复）
-- ============================================================

DELETE FROM report_business_config WHERE business_code = 'KUAISHOU';
DELETE FROM report_report_config WHERE business_code = 'KUAISHOU';
DELETE FROM report_field_config WHERE business_code = 'KUAISHOU';
DELETE FROM report_extract_config WHERE business_code = 'KUAISHOU';

-- 1. 业务配置
INSERT INTO report_business_config (tenant_id, business_code, business_name, description, status, config, creator)
VALUES (1, 'KUAISHOU', '快手电商', '快手电商平台订单数据', 'ACTIVE', '{}', 'system');

-- 2. 报表配置
INSERT INTO report_report_config (tenant_id, business_code, report_code, report_name, description,
    status, stat_table_name, stat_table_comment, date_field, primary_keys, conflict_keys, config, creator)
VALUES (1, 'KUAISHOU', 'shop_daily', '店铺日报',
    '按店铺+日期统计订单数据', 'ACTIVE',
    'stat_kuaishou_shop_daily', '快手电商店铺日报统计宽表',
    'stat_date', '["id"]'::jsonb, '["stat_date", "shop_id"]'::jsonb, '{}', 'system');

-- 3. 字段配置
INSERT INTO report_field_config (tenant_id, business_code, report_code, field_code, field_name,
    field_type, data_type, field_role, is_aggregatable, is_filterable, is_queryable, is_sortable,
    default_aggregate, valid_aggregates, filter_operators, sort_order, group_name, status, creator)
VALUES
-- 维度
(1, 'KUAISHOU', 'shop_daily', 'stat_date', '统计日期',
    'DATE', 'STRING', 'DIMENSION', false, true, true, true,
    '', '[]'::jsonb, '["=",">=","<=","BETWEEN"]'::jsonb, 1, '时间维度', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'order_no', '订单号',
    'STRING', 'STRING', 'DIMENSION', false, true, true, true,
    '', '[]'::jsonb, '["=","IN"]'::jsonb, 0, '订单维度', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'shop_id', '店铺ID',
    'STRING', 'STRING', 'DIMENSION', false, true, true, true,
    '', '[]'::jsonb, '["=","IN"]'::jsonb, 2, '店铺维度', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'shop_name', '店铺名称',
    'STRING', 'STRING', 'DIMENSION', false, true, true, true,
    '', '[]'::jsonb, '["=","LIKE"]'::jsonb, 3, '店铺维度', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'product_category', '商品类目',
    'STRING', 'STRING', 'DIMENSION', false, true, true, true,
    '', '[]'::jsonb, '["=","IN"]'::jsonb, 4, '商品维度', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'order_status', '订单状态',
    'STRING', 'STRING', 'DIMENSION', false, true, true, true,
    '', '[]'::jsonb, '["=","IN"]'::jsonb, 5, '订单维度', 'ACTIVE', 'system'),
-- 指标
(1, 'KUAISHOU', 'shop_daily', 'order_count', '订单数',
    'INT', 'INT', 'INDICATOR', true, false, true, true,
    'SUM', '["SUM","COUNT"]'::jsonb, '[]'::jsonb, 10, '交易指标', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'order_amount', '订单金额',
    'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
    'SUM', '["SUM","AVG","MAX","MIN"]'::jsonb, '[]'::jsonb, 11, '交易指标', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'refund_amount', '退款金额',
    'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
    'SUM', '["SUM","AVG"]'::jsonb, '[]'::jsonb, 12, '交易指标', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'shipping_cost', '运费',
    'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
    'SUM', '["SUM","AVG"]'::jsonb, '[]'::jsonb, 13, '交易指标', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'total_quantity', '商品数量',
    'INT', 'INT', 'INDICATOR', true, false, true, true,
    'SUM', '["SUM","AVG"]'::jsonb, '[]'::jsonb, 14, '交易指标', 'ACTIVE', 'system'),
(1, 'KUAISHOU', 'shop_daily', 'avg_price', '平均单价',
    'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
    'AVG', '["AVG","MAX","MIN"]'::jsonb, '[]'::jsonb, 15, '交易指标', 'ACTIVE', 'system');

-- 4. 抽取配置（DIRECT 模式：逐行抽取，查询时动态聚合）
INSERT INTO report_extract_config (tenant_id, business_code, report_code, extract_code, extract_name,
    source_table_name, source_table_alias, target_table_name, is_enabled,
    extract_type, extract_mode, extract_key_field, group_by_fields, filter_expression,
    field_mappings, join_configs, batch_size, status, creator)
VALUES (1, 'KUAISHOU', 'shop_daily', 'shop_daily_direct', '店铺日订单(逐行,可自由组合查询)',
    'demo_kuaishou_orders', 'o', 'stat_kuaishou_shop_daily',
    true, 'INCREMENTAL', 'DIRECT', 'created_at',
    '[]'::jsonb,
    'o.order_status = ''已完成''',
    '[
        {"sourceField":"order_no", "targetField":"order_no", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
        {"sourceField":"shop_id", "targetField":"shop_id", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
        {"sourceField":"product_category", "targetField":"product_category", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
        {"sourceField":"order_status", "targetField":"order_status", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
        {"sourceField":"order_amount", "targetField":"order_amount", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0},
        {"sourceField":"refund_amount", "targetField":"refund_amount", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0},
        {"sourceField":"shipping_cost", "targetField":"shipping_cost", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0},
        {"sourceField":"quantity", "targetField":"total_quantity", "fieldType":"INT", "aggregateFunction":"", "defaultValue":0},
        {"sourceField":"unit_price", "targetField":"unit_price", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0}
    ]'::jsonb,
    '[
        {
            "joinTable":"demo_kuaishou_shop",
            "joinAlias":"s",
            "joinType":"LEFT",
            "joinCondition":"o.shop_id = s.shop_id",
            "fieldMappings":[
                {"sourceField":"shop_name", "targetField":"shop_name", "fieldType":"STRING", "aggregateFunction":""},
                {"sourceField":"shop_level", "targetField":"shop_level", "fieldType":"STRING", "aggregateFunction":""}
            ]
        }
    ]'::jsonb,
    1000, 'ACTIVE', 'system'
);

-- ============================================================
-- 第三步：验证
-- ============================================================

SELECT '✅ 源表数据' AS info, COUNT(*) AS 行数 FROM demo_kuaishou_orders
UNION ALL
SELECT '✅ 店铺数', COUNT(*) FROM demo_kuaishou_shop
UNION ALL
SELECT '✅ 业务配置', COUNT(*) FROM report_business_config WHERE business_code = 'KUAISHOU'
UNION ALL
SELECT '✅ 报表配置', COUNT(*) FROM report_report_config WHERE business_code = 'KUAISHOU'
UNION ALL
SELECT '✅ 字段配置', COUNT(*) FROM report_field_config WHERE business_code = 'KUAISHOU'
UNION ALL
SELECT '✅ 抽取配置', COUNT(*) FROM report_extract_config WHERE business_code = 'KUAISHOU';

SELECT '📅 数据日期范围:' AS info, MIN(created_at::date)::text || ' ~ ' || MAX(created_at::date)::text AS 范围 FROM demo_kuaishou_orders;

COMMIT;

-- ============================================================
-- 使用说明
-- ============================================================
-- 1. 启动项目（会自动创建系统表）
-- 2. 执行本脚本
-- 3. 打开 http://localhost:3013/admin/report
-- 4. 抽取配置 → 选"快手电商/shop_daily" → 点"执行抽取"
-- 5. 数据查询 → 选"快手电商/shop_daily" → 自由选维度/指标查询
-- ============================================================
--
-- 上线前清理 Demo 数据：
--   DELETE FROM report_business_config WHERE business_code = 'KUAISHOU';
--   DELETE FROM report_report_config WHERE business_code = 'KUAISHOU';
--   DELETE FROM report_field_config WHERE business_code = 'KUAISHOU';
--   DELETE FROM report_extract_config WHERE business_code = 'KUAISHOU';
--   DELETE FROM report_extract_log WHERE business_code = 'KUAISHOU';
--   DROP TABLE IF EXISTS stat_kuaishou_shop_daily CASCADE;
--   DROP TABLE IF EXISTS demo_kuaishou_orders CASCADE;
--   DROP TABLE IF EXISTS demo_kuaishou_shop CASCADE;
-- ============================================================