-- User Action Logging Database Schema
-- This schema provides comprehensive logging for user actions in the booking system

-- Create the user_actions_log table
CREATE TABLE user_actions_log (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    user_id NVARCHAR(255) NOT NULL,
    user_email NVARCHAR(255) NOT NULL,
    action NVARCHAR(50) NOT NULL CHECK (action IN ('SAVE', 'SAVE_AND_SEND', 'RESEND', 'PREVIEW', 'EDIT')),
    entity_type NVARCHAR(50) NOT NULL CHECK (entity_type IN ('CUSTOMER', 'PARTICIPANT', 'PAYMENT', 'PRODUCT', 'PDF', 'EMAIL')),
    entity_id NVARCHAR(255) NOT NULL,
    booking_id NVARCHAR(255) NOT NULL,
    page_id NVARCHAR(255) NOT NULL,
    brand NVARCHAR(100) NOT NULL,
    language NVARCHAR(10) NOT NULL,
    action_data NVARCHAR(MAX), -- JSON data of what was changed
    timestamp DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    ip_address NVARCHAR(45), -- IPv6 compatible
    user_agent NVARCHAR(500),
    session_id NVARCHAR(255),
    status NVARCHAR(20) NOT NULL CHECK (status IN ('SUCCESS', 'FAILED', 'PENDING')) DEFAULT 'SUCCESS',
    error_message NVARCHAR(MAX),
    version NVARCHAR(100),
    
    -- Indexes for better query performance
    INDEX idx_user_actions_log_user_id (user_id),
    INDEX idx_user_actions_log_user_email (user_email),
    INDEX idx_user_actions_log_booking_id (booking_id),
    INDEX idx_user_actions_log_action (action),
    INDEX idx_user_actions_log_entity_type (entity_type),
    INDEX idx_user_actions_log_timestamp (timestamp),
    INDEX idx_user_actions_log_status (status),
    INDEX idx_user_actions_log_user_timestamp (user_id, timestamp),
    INDEX idx_user_actions_log_booking_timestamp (booking_id, timestamp)
);

-- Create a view for common queries
CREATE VIEW v_user_actions_summary AS
SELECT 
    user_id,
    user_email,
    booking_id,
    action,
    entity_type,
    status,
    COUNT(*) as action_count,
    MIN(timestamp) as first_action,
    MAX(timestamp) as last_action,
    AVG(DATEDIFF(MINUTE, LAG(timestamp) OVER (PARTITION BY booking_id ORDER BY timestamp), timestamp)) as avg_time_between_actions
FROM user_actions_log
GROUP BY user_id, user_email, booking_id, action, entity_type, status;

-- Create a view for failed actions
CREATE VIEW v_failed_actions AS
SELECT 
    user_id,
    user_email,
    booking_id,
    action,
    entity_type,
    error_message,
    timestamp
FROM user_actions_log
WHERE status = 'FAILED'
ORDER BY timestamp DESC;

-- Create a view for user activity summary
CREATE VIEW v_user_activity_summary AS
SELECT 
    user_id,
    user_email,
    COUNT(*) as total_actions,
    COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) as successful_actions,
    COUNT(CASE WHEN status = 'FAILED' THEN 1 END) as failed_actions,
    COUNT(DISTINCT booking_id) as unique_bookings,
    MIN(timestamp) as first_action_date,
    MAX(timestamp) as last_action_date,
    DATEDIFF(DAY, MIN(timestamp), MAX(timestamp)) as days_active
FROM user_actions_log
GROUP BY user_id, user_email;

-- Create a stored procedure for cleaning old logs
CREATE PROCEDURE sp_cleanup_old_logs
    @days_to_keep INT = 90
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @cutoff_date DATETIME2 = DATEADD(DAY, -@days_to_keep, GETUTCDATE());
    
    DELETE FROM user_actions_log 
    WHERE timestamp < @cutoff_date;
    
    SELECT @@ROWCOUNT as deleted_rows;
END;

-- Create a stored procedure for getting user action history
CREATE PROCEDURE sp_get_user_action_history
    @user_email NVARCHAR(255),
    @start_date DATETIME2 = NULL,
    @end_date DATETIME2 = NULL,
    @action_type NVARCHAR(50) = NULL,
    @entity_type NVARCHAR(50) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        id,
        user_id,
        user_email,
        action,
        entity_type,
        entity_id,
        booking_id,
        page_id,
        brand,
        language,
        action_data,
        timestamp,
        ip_address,
        user_agent,
        session_id,
        status,
        error_message,
        version
    FROM user_actions_log
    WHERE user_email = @user_email
        AND (@start_date IS NULL OR timestamp >= @start_date)
        AND (@end_date IS NULL OR timestamp <= @end_date)
        AND (@action_type IS NULL OR action = @action_type)
        AND (@entity_type IS NULL OR entity_type = @entity_type)
    ORDER BY timestamp DESC;
END;

-- Create a stored procedure for getting booking action history
CREATE PROCEDURE sp_get_booking_action_history
    @booking_id NVARCHAR(255),
    @start_date DATETIME2 = NULL,
    @end_date DATETIME2 = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        id,
        user_id,
        user_email,
        action,
        entity_type,
        entity_id,
        booking_id,
        page_id,
        brand,
        language,
        action_data,
        timestamp,
        ip_address,
        user_agent,
        session_id,
        status,
        error_message,
        version
    FROM user_actions_log
    WHERE booking_id = @booking_id
        AND (@start_date IS NULL OR timestamp >= @start_date)
        AND (@end_date IS NULL OR timestamp <= @end_date)
    ORDER BY timestamp DESC;
END;

-- Create a function to get action statistics
CREATE FUNCTION fn_get_action_statistics
(
    @start_date DATETIME2,
    @end_date DATETIME2
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        action,
        entity_type,
        status,
        COUNT(*) as action_count,
        COUNT(DISTINCT user_id) as unique_users,
        COUNT(DISTINCT booking_id) as unique_bookings
    FROM user_actions_log
    WHERE timestamp BETWEEN @start_date AND @end_date
    GROUP BY action, entity_type, status
);

-- Insert sample data for testing (optional)
-- INSERT INTO user_actions_log (user_id, user_email, action, entity_type, entity_id, booking_id, page_id, brand, language, action_data, status)
-- VALUES 
-- ('user123', 'user@example.com', 'SAVE', 'CUSTOMER', 'booking123', 'booking123', 'page1', 'brand1', 'en', '{"customerData": {"name": "John Doe"}}', 'SUCCESS'),
-- ('user123', 'user@example.com', 'SAVE_AND_SEND', 'PDF', 'booking123', 'booking123', 'page1', 'brand1', 'en', '{"pdfVersion": "1.0"}', 'SUCCESS');

-- Comments on the schema design:
/*
1. **Primary Key**: Auto-incrementing BIGINT for performance and scalability
2. **User Identification**: Both user_id and user_email for flexibility
3. **Action Types**: Enumerated values for consistency and performance
4. **Entity Types**: Categorized by what type of data was modified
5. **Action Data**: JSON field to store detailed information about changes
6. **Timestamps**: UTC timestamps for consistency across timezones
7. **Status Tracking**: Success/failure status with error messages
8. **Audit Trail**: IP address, user agent, and session tracking
9. **Indexes**: Optimized for common query patterns
10. **Views**: Pre-built queries for common reporting needs
11. **Stored Procedures**: Encapsulated business logic for data access
12. **Data Retention**: Procedure for cleaning old logs
13. **Statistics**: Function for generating action statistics

Recommended data retention policy:
- Keep logs for 90 days by default
- Archive older logs to separate storage if needed
- Consider data privacy regulations (GDPR, etc.)

Performance considerations:
- Monitor index usage and adjust as needed
- Consider partitioning by date for large datasets
- Regular maintenance of statistics and indexes
*/