-- 创建用户表
CREATE TABLE IF NOT EXISTS cf_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    role VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建商品表
CREATE TABLE IF NOT EXISTS cf_product (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    shop_id VARCHAR(255),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    main_image VARCHAR(255),
    platform VARCHAR(50),
    platform_product_id VARCHAR(255),
    price DECIMAL(10,2),
    cost_price DECIMAL(10,2),
    quantity INT,
    status VARCHAR(50),
    phash VARCHAR(255),
    semantic_hash VARCHAR(255),
    vector_embedding TEXT,
    attributes JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE IF NOT EXISTS cf_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    shop_id VARCHAR(255),
    platform VARCHAR(50),
    platform_order_id VARCHAR(255),
    status VARCHAR(50),
    total_amount DECIMAL(10,2),
    currency VARCHAR(10),
    customer_info JSON,
    items JSON,
    shipping_address JSON,
    tracking_number VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建支付表
CREATE TABLE IF NOT EXISTS cf_payment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    order_id BIGINT,
    payment_method VARCHAR(50),
    amount DECIMAL(10,2),
    currency VARCHAR(10),
    status VARCHAR(50),
    transaction_id VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES cf_order(id)
);

-- 创建物流表
CREATE TABLE IF NOT EXISTS cf_logistics (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    order_id BIGINT,
    shipping_method VARCHAR(50),
    tracking_number VARCHAR(255),
    carrier VARCHAR(50),
    status VARCHAR(50),
    estimated_delivery_date DATETIME,
    actual_delivery_date DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES cf_order(id)
);

-- 创建配置表
CREATE TABLE IF NOT EXISTS cf_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(255),
    shop_id VARCHAR(255),
    config_key VARCHAR(255) NOT NULL,
    config_value VARCHAR(255) NOT NULL,
    config_type VARCHAR(50),
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建审计表
CREATE TABLE IF NOT EXISTS cf_audit (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id VARCHAR(255),
    shop_id VARCHAR(255),
    user_id BIGINT,
    action VARCHAR(255),
    resource_type VARCHAR(255),
    resource_id VARCHAR(255),
    ip_address VARCHAR(100),
    user_agent TEXT,
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_user_tenant_id ON cf_user(tenant_id);
CREATE INDEX IF NOT EXISTS idx_product_tenant_id ON cf_product(tenant_id);
CREATE INDEX IF NOT EXISTS idx_product_platform ON cf_product(platform);
CREATE INDEX IF NOT EXISTS idx_order_tenant_id ON cf_order(tenant_id);
CREATE INDEX IF NOT EXISTS idx_order_platform ON cf_order(platform);
CREATE INDEX IF NOT EXISTS idx_payment_tenant_id ON cf_payment(tenant_id);
CREATE INDEX IF NOT EXISTS idx_payment_order_id ON cf_payment(order_id);
CREATE INDEX IF NOT EXISTS idx_logistics_tenant_id ON cf_logistics(tenant_id);
CREATE INDEX IF NOT EXISTS idx_logistics_order_id ON cf_logistics(order_id);
CREATE INDEX IF NOT EXISTS idx_config_tenant_id ON cf_config(tenant_id);
CREATE INDEX IF NOT EXISTS idx_config_shop_id ON cf_config(shop_id);
CREATE INDEX IF NOT EXISTS idx_config_key ON cf_config(config_key);
CREATE INDEX IF NOT EXISTS idx_audit_tenant_id ON cf_audit(tenant_id);
CREATE INDEX IF NOT EXISTS idx_audit_shop_id ON cf_audit(shop_id);
CREATE INDEX IF NOT EXISTS idx_audit_user_id ON cf_audit(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_action ON cf_audit(action);
CREATE INDEX IF NOT EXISTS idx_audit_resource_type ON cf_audit(resource_type);
CREATE INDEX IF NOT EXISTS idx_audit_created_at ON cf_audit(created_at);