IF OBJECT_ID(N'app.pdd_chat_ai_analysis_history', N'U') IS NULL
BEGIN
  CREATE TABLE [app].[pdd_chat_ai_analysis_history] (
    id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    analysis_id NVARCHAR(64) NOT NULL,
    status NVARCHAR(20) NOT NULL,
    goods_id NVARCHAR(80) NOT NULL,
    goods_name NVARCHAR(500) NULL,
    shop_name NVARCHAR(255) NULL,
    product_image_url NVARCHAR(2000) NULL,
    chat_start_date DATE NOT NULL,
    chat_end_date DATE NOT NULL,
    source_message_count INT NOT NULL DEFAULT 0,
    source_customer_count INT NOT NULL DEFAULT 0,
    product_metrics_json NVARCHAR(MAX) NULL,
    request_prompt NVARCHAR(MAX) NULL,
    ai_provider NVARCHAR(80) NULL,
    ai_model NVARCHAR(120) NULL,
    ai_response_json NVARCHAR(MAX) NULL,
    ai_raw_response_json NVARCHAR(MAX) NULL,
    conversation_type NVARCHAR(30) NULL,
    analysis_json NVARCHAR(MAX) NULL,
    error_message NVARCHAR(2000) NULL,
    duration_ms INT NULL,
    prompt_tokens INT NULL,
    completion_tokens INT NULL,
    total_tokens INT NULL,
    created_by_user_id NVARCHAR(100) NULL,
    created_by_name NVARCHAR(100) NULL,
    created_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'UX_pdd_chat_ai_analysis_history_analysis_id' AND object_id = OBJECT_ID(N'app.pdd_chat_ai_analysis_history'))
  CREATE UNIQUE INDEX UX_pdd_chat_ai_analysis_history_analysis_id ON [app].[pdd_chat_ai_analysis_history](analysis_id);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_pdd_chat_ai_analysis_history_goods_date' AND object_id = OBJECT_ID(N'app.pdd_chat_ai_analysis_history'))
  CREATE INDEX IX_pdd_chat_ai_analysis_history_goods_date ON [app].[pdd_chat_ai_analysis_history](goods_id, chat_start_date, chat_end_date, created_at DESC);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_pdd_chat_ai_analysis_history_created_at' AND object_id = OBJECT_ID(N'app.pdd_chat_ai_analysis_history'))
  CREATE INDEX IX_pdd_chat_ai_analysis_history_created_at ON [app].[pdd_chat_ai_analysis_history](created_at DESC);
GO