-- =====================================================
-- Gregory.AI Usage Limits & Tracking Migration
-- Adds file upload limits, message limits, and usage tracking
-- =====================================================

USE `securitisys_greg`;

-- 1. Create usage_tracking table for daily limits
CREATE TABLE IF NOT EXISTS usage_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    usage_date DATE NOT NULL,
    message_count INT NOT NULL DEFAULT 0,
    file_upload_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_date (user_id, usage_date),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Create email_notifications table for tracking sent emails
CREATE TABLE IF NOT EXISTS email_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    notification_type ENUM('account_created', 'plan_changed', 'payment_received', 'payment_failed', 'subscription_canceled', 'account_updated') NOT NULL,
    email_to VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('sent', 'failed', 'pending') NOT NULL DEFAULT 'pending',
    error_message TEXT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_type (notification_type),
    INDEX idx_sent_at (sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Create user_activity_log for admin tracking
CREATE TABLE IF NOT EXISTS user_activity_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    activity_type ENUM('message', 'file_upload', 'login', 'logout', 'plan_change', 'payment') NOT NULL,
    details JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_type (activity_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Update subscription_plans to include file_upload_limit and message_limit
-- Note: MySQL doesn't support IF NOT EXISTS for ADD COLUMN, so we use a procedure
DELIMITER //
DROP PROCEDURE IF EXISTS add_subscription_columns//
CREATE PROCEDURE add_subscription_columns()
BEGIN
    -- Add file_upload_limit if not exists
    IF NOT EXISTS (SELECT * FROM information_schema.columns 
                   WHERE table_schema = DATABASE() 
                   AND table_name = 'subscription_plans' 
                   AND column_name = 'file_upload_limit') THEN
        ALTER TABLE subscription_plans ADD COLUMN file_upload_limit INT DEFAULT 3 COMMENT 'Daily file upload limit, NULL means unlimited';
    END IF;
    
    -- Add message_limit if not exists
    IF NOT EXISTS (SELECT * FROM information_schema.columns 
                   WHERE table_schema = DATABASE() 
                   AND table_name = 'subscription_plans' 
                   AND column_name = 'message_limit') THEN
        ALTER TABLE subscription_plans ADD COLUMN message_limit INT DEFAULT NULL COMMENT 'Daily message limit, NULL means unlimited';
    END IF;
END//
DELIMITER ;
CALL add_subscription_columns();
DROP PROCEDURE IF EXISTS add_subscription_columns;

-- 5. Update existing plans with limits
UPDATE subscription_plans SET 
    file_upload_limit = 3,
    message_limit = 5,
    features = JSON_SET(COALESCE(features, '{}'), '$.file_uploads_per_day', 3, '$.messages_per_day', 5)
WHERE slug = 'free';

UPDATE subscription_plans SET 
    file_upload_limit = 20,
    message_limit = 100,
    features = JSON_SET(COALESCE(features, '{}'), '$.file_uploads_per_day', 20, '$.messages_per_day', 100)
WHERE slug = 'silver';

UPDATE subscription_plans SET 
    file_upload_limit = 50,
    message_limit = 500,
    features = JSON_SET(COALESCE(features, '{}'), '$.file_uploads_per_day', 50, '$.messages_per_day', 500)
WHERE slug = 'gold';

UPDATE subscription_plans SET 
    file_upload_limit = NULL,
    message_limit = NULL,
    features = JSON_SET(COALESCE(features, '{}'), '$.file_uploads_per_day', 'unlimited', '$.messages_per_day', 'unlimited')
WHERE slug = 'premium';

-- 6. Create view for admin financial tracking (only if payment_history exists)
-- Note: Run this after payment_history table is created
DROP VIEW IF EXISTS admin_financial_summary;
CREATE VIEW admin_financial_summary AS
SELECT 
    DATE(created_at) as date,
    COUNT(*) as transaction_count,
    SUM(CASE WHEN status = 'succeeded' THEN amount_cents ELSE 0 END) as revenue_cents,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_count,
    SUM(CASE WHEN status = 'refunded' THEN amount_cents ELSE 0 END) as refunded_cents
FROM payment_history
GROUP BY DATE(created_at);

-- 7. Create view for admin user activity summary
CREATE OR REPLACE VIEW admin_user_activity_summary AS
SELECT 
    u.id as user_id,
    u.email,
    u.username,
    COALESCE(sp.name, 'Free') as plan_name,
    COALESCE(sp.slug, 'free') as plan_slug,
    (SELECT COUNT(*) FROM messages m 
     JOIN conversations c ON m.conversation_id = c.id 
     WHERE c.user_id = u.id) as total_messages,
    (SELECT COUNT(*) FROM messages m 
     JOIN conversations c ON m.conversation_id = c.id 
     WHERE c.user_id = u.id AND DATE(m.created_at) = CURDATE()) as messages_today,
    (SELECT COUNT(*) FROM usage_tracking ut 
     WHERE ut.user_id = u.id AND ut.usage_date = CURDATE()) as file_uploads_today,
    (SELECT SUM(ph.amount_cents) FROM payment_history ph 
     WHERE ph.user_id = u.id AND ph.status = 'succeeded') as total_paid_cents,
    u.created_at,
    u.last_active
FROM users u
LEFT JOIN user_subscriptions us ON u.id = us.user_id AND us.status = 'active'
LEFT JOIN subscription_plans sp ON us.plan_id = sp.id;

-- =====================================================
-- Run this migration after the main schema.sql
-- =====================================================