-- cmdsharp Trial System Database Schema
-- Safe to apply to existing database - no existing table modifications

-- Trials table - main trial tracking
CREATE TABLE IF NOT EXISTS trials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trial_id VARCHAR(32) UNIQUE NOT NULL,
    device_hash VARCHAR(64) NOT NULL,
    status ENUM('active', 'expired', 'converted') DEFAULT 'active',
    trial_start DATETIME NOT NULL,
    trial_end DATETIME NOT NULL,
    converted_at DATETIME NULL,
    license_key VARCHAR(50) NULL, -- Links to existing licenses table when converted
    app_version VARCHAR(20) NULL,
    platform ENUM('android', 'ios', 'windows', 'mac', 'web') NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX idx_trial_id (trial_id),
    INDEX idx_device_hash (device_hash),
    INDEX idx_status (status),
    INDEX idx_trial_end (trial_end),
    INDEX idx_created_at (created_at),
    INDEX idx_status_trial_end (status, trial_end),
    
    -- Foreign key to existing licenses table (optional, when converted)
    FOREIGN KEY (license_key) REFERENCES licenses(license_key) ON DELETE SET NULL
);

-- Trial events table - comprehensive analytics tracking
CREATE TABLE IF NOT EXISTS trial_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trial_id VARCHAR(32) NOT NULL,
    event_type ENUM('started', 'app_opened', 'invoice_created', 'feature_used', 'expired', 'converted', 'validation_check') NOT NULL,
    event_data JSON NULL, -- Flexible data storage for analytics
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes for analytics queries
    INDEX idx_trial_id (trial_id),
    INDEX idx_event_type (event_type),
    INDEX idx_created_at (created_at),
    INDEX idx_trial_event_type (trial_id, event_type),
    INDEX idx_event_type_created (event_type, created_at),
    
    -- Foreign key to trials table
    FOREIGN KEY (trial_id) REFERENCES trials(trial_id) ON DELETE CASCADE
);

-- Trial analytics cache table - for dashboard performance
CREATE TABLE IF NOT EXISTS trial_analytics_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cache_key VARCHAR(100) UNIQUE NOT NULL,
    cache_data JSON NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_cache_key (cache_key),
    INDEX idx_expires_at (expires_at)
);

-- Add trial-related activity log types to support existing activity logging
-- This is just for documentation - no schema changes needed
-- New activity log entries will use existing activity_logs table with these actions:
-- - 'trial_started', 'trial_validated', 'trial_expired', 'trial_converted'

-- Create a view for easier trial analytics queries
CREATE OR REPLACE VIEW trial_summary AS
SELECT 
    t.trial_id,
    t.device_hash,
    t.status,
    t.trial_start,
    t.trial_end,
    t.platform,
    t.app_version,
    t.converted_at,
    t.license_key,
    DATEDIFF(t.trial_end, t.trial_start) as trial_length_days,
    DATEDIFF(NOW(), t.trial_start) as days_since_start,
    GREATEST(0, DATEDIFF(t.trial_end, NOW())) as days_remaining,
    COUNT(te.id) as total_events,
    SUM(CASE WHEN te.event_type = 'app_opened' THEN 1 ELSE 0 END) as app_opens,
    SUM(CASE WHEN te.event_type = 'invoice_created' THEN 1 ELSE 0 END) as invoices_created,
    MAX(te.created_at) as last_activity
FROM trials t
LEFT JOIN trial_events te ON t.trial_id = te.trial_id
GROUP BY t.id;

-- Insert initial cache entries for common dashboard queries
INSERT INTO trial_analytics_cache (cache_key, cache_data, expires_at) VALUES
('dashboard_stats_daily', '{}', DATE_ADD(NOW(), INTERVAL 1 HOUR)),
('conversion_rates_weekly', '{}', DATE_ADD(NOW(), INTERVAL 6 HOUR)),
('platform_breakdown_daily', '{}', DATE_ADD(NOW(), INTERVAL 2 HOUR))
ON DUPLICATE KEY UPDATE expires_at = DATE_ADD(NOW(), INTERVAL 1 HOUR);

-- Validation: Verify tables were created successfully
SELECT 
    'trials' as table_name, 
    COUNT(*) as row_count,
    'Created successfully' as status
FROM trials
UNION ALL
SELECT 
    'trial_events' as table_name,
    COUNT(*) as row_count, 
    'Created successfully' as status
FROM trial_events
UNION ALL
SELECT 
    'trial_analytics_cache' as table_name,
    COUNT(*) as row_count,
    'Created successfully' as status  
FROM trial_analytics_cache;
