scripts/create_feedback_events_table.sql

-- Migration: Create feedback_events table
-- Run this script against Azure SQL to create the table

-- Check if table exists, create if not
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='feedback_events' and xtype='U')
BEGIN
    CREATE TABLE feedback_events (
        id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
        event_type VARCHAR(50) NOT NULL,  -- plan_saved, poi_removed, rating_positive, etc.
        session_id VARCHAR(255) NULL,
        user_hash VARCHAR(64) NULL,       -- Anonymized user ID for privacy
        context NVARCHAR(MAX) NOT NULL DEFAULT '{}',  -- Trip preferences at event time (JSON)
        itinerary_snapshot NVARCHAR(MAX) NOT NULL DEFAULT '[]',  -- POIs at event time (JSON)
        modifications NVARCHAR(MAX) NOT NULL DEFAULT '[]',  -- What changed (JSON)
        reward DECIMAL(3, 1) NOT NULL DEFAULT 0,  -- Computed reward signal
        created_at DATETIME2 NOT NULL DEFAULT GETUTCDATE()
    );

    -- Indexes for efficient querying
    CREATE INDEX IX_feedback_events_event_type ON feedback_events(event_type);
    CREATE INDEX IX_feedback_events_session_id ON feedback_events(session_id);
    CREATE INDEX IX_feedback_events_created_at ON feedback_events(created_at);

    PRINT 'Created feedback_events table with indexes';
END
ELSE
BEGIN
    PRINT 'feedback_events table already exists';
END
GO