-- 直接创建测试数据表的 SQL 脚本
-- 用于在生产环境数据库中创建测试表

-- 表1: 销售数据表
DROP TABLE IF EXISTS test_sales_data CASCADE;

CREATE TABLE test_sales_data (
    id SERIAL PRIMARY KEY,
    order_id VARCHAR(50) NOT NULL,
    customer_name VARCHAR(100),
    product_name VARCHAR(200),
    quantity INTEGER,
    unit_price DECIMAL(10, 2),
    total_amount DECIMAL(10, 2),
    order_date DATE,
    region VARCHAR(50),
    status VARCHAR(20),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE test_sales_data IS '测试销售数据表';

-- 表2: 用户统计表
DROP TABLE IF EXISTS test_user_statistics CASCADE;

CREATE TABLE test_user_statistics (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    username VARCHAR(100),
    email VARCHAR(200),
    registration_date DATE,
    last_login_date DATE,
    total_orders INTEGER DEFAULT 0,
    total_amount DECIMAL(10, 2) DEFAULT 0,
    user_level VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE test_user_statistics IS '测试用户统计表';

-- 表3: 产品库存表
DROP TABLE IF EXISTS test_product_inventory CASCADE;

CREATE TABLE test_product_inventory (
    id SERIAL PRIMARY KEY,
    product_code VARCHAR(50) UNIQUE NOT NULL,
    product_name VARCHAR(200),
    category VARCHAR(100),
    current_stock INTEGER,
    min_stock INTEGER,
    max_stock INTEGER,
    unit_price DECIMAL(10, 2),
    supplier VARCHAR(200),
    last_restock_date DATE,
    status VARCHAR(20),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE test_product_inventory IS '测试产品库存表';