:setvar DemoDatabase "CustomerAIDemo2022"
:setvar CsvPath "D:\DSA\Demo_DBMS\data\customer_feedback.csv"

IF DB_ID(N'$(DemoDatabase)') IS NULL
BEGIN
    EXEC(N'CREATE DATABASE [$(DemoDatabase)]');
END
GO

USE [$(DemoDatabase)];
GO

-- Drop dependent tables first to avoid FK conflicts when reseeding.
IF OBJECT_ID(N'dbo.RealFeedbackEmbedding', N'U') IS NOT NULL
    DROP TABLE dbo.RealFeedbackEmbedding;
GO

IF OBJECT_ID(N'dbo.RealEmbeddingMetadata', N'U') IS NOT NULL
    DROP TABLE dbo.RealEmbeddingMetadata;
GO

IF OBJECT_ID(N'dbo.FeedbackEmbedding', N'U') IS NOT NULL
    DROP TABLE dbo.FeedbackEmbedding;
GO

IF OBJECT_ID(N'dbo.CustomerFeedback', N'U') IS NOT NULL
    DROP TABLE dbo.CustomerFeedback;
GO

IF OBJECT_ID(N'dbo.CustomerFeedbackStage', N'U') IS NOT NULL
    DROP TABLE dbo.CustomerFeedbackStage;
GO

IF OBJECT_ID(N'dbo.QueryEmbedding', N'U') IS NOT NULL
    DROP TABLE dbo.QueryEmbedding;
GO

CREATE TABLE dbo.CustomerFeedback
(
    FeedbackId       INT IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_CustomerFeedback PRIMARY KEY CLUSTERED,
    MaskedCustomerId NVARCHAR(30)  NOT NULL,
    Product          NVARCHAR(100) NOT NULL,
    CustomerSegment  NVARCHAR(50)  NOT NULL,
    Region           NVARCHAR(50)  NOT NULL,
    Channel          NVARCHAR(50)  NULL,
    RiskLevel        NVARCHAR(20)  NULL,
    CreatedAt        DATETIME2(0)  NOT NULL,
    SourceIssueGroup NVARCHAR(80)  NOT NULL,
    FeedbackText     NVARCHAR(MAX) NOT NULL
);
GO

CREATE INDEX IX_CustomerFeedback_BusinessFilters
ON dbo.CustomerFeedback
(
    CustomerSegment,
    RiskLevel,
    Product,
    CreatedAt
)
INCLUDE (Channel, Region, SourceIssueGroup);
GO

CREATE TABLE dbo.CustomerFeedbackStage
(
    MaskedCustomerId NVARCHAR(30)  NOT NULL,
    Product          NVARCHAR(100) NOT NULL,
    CustomerSegment  NVARCHAR(50)  NOT NULL,
    Region           NVARCHAR(50)  NOT NULL,
    Channel          NVARCHAR(50)  NULL,
    RiskLevel        NVARCHAR(20)  NULL,
    CreatedAt        NVARCHAR(40)  NOT NULL,
    SourceIssueGroup NVARCHAR(80)  NOT NULL,
    FeedbackText     NVARCHAR(MAX) NOT NULL
);
GO

BULK INSERT dbo.CustomerFeedbackStage
FROM '$(CsvPath)'
WITH
(
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDQUOTE = '"',
    CODEPAGE = '65001',
    ROWTERMINATOR = '0x0d0a',
    TABLOCK
);
GO

INSERT INTO dbo.CustomerFeedback
(
    MaskedCustomerId, Product, CustomerSegment, Region, Channel,
    RiskLevel, CreatedAt, SourceIssueGroup, FeedbackText
)
SELECT
    MaskedCustomerId, Product, CustomerSegment, Region, Channel,
    RiskLevel, CONVERT(DATETIME2(0), CreatedAt, 126),
    SourceIssueGroup, FeedbackText
FROM dbo.CustomerFeedbackStage;
GO

DROP TABLE dbo.CustomerFeedbackStage;
GO

SELECT
    COUNT(*) AS imported_rows,
    MIN(CreatedAt) AS min_created_at,
    MAX(CreatedAt) AS max_created_at,
    COUNT(DISTINCT Product) AS distinct_products,
    SUM(CASE WHEN RiskLevel = N'Critical' THEN 1 ELSE 0 END) AS critical_count
FROM dbo.CustomerFeedback;
GO

PRINT 'CSV import into SQL Server 2022-compatible schema complete.';
PRINT 'Next: scripts\build_real_embeddings_ollama.ps1 -Database $(DemoDatabase) -Model bge-m3';
GO