-- =====================================================
-- Gregory.AI Usage Limits & Tracking Migration (Simple Version)
-- Run each section separately in phpMyAdmin if needed
-- =====================================================

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;

-- =====================================================
-- RUN THESE SEPARATELY - Add columns to subscription_plans
-- If column already exists, you'll get an error - that's OK, just skip it
-- =====================================================


-- =====================================================
-- Update existing plans with limits
-- =====================================================

UPDATE subscription_plans SET 
    file_upload_limit = 3,
    message_limit = 5
WHERE slug = 'free';

UPDATE subscription_plans SET 
    file_upload_limit = 20,
    message_limit = 100
WHERE slug = 'silver';

UPDATE subscription_plans SET 
    file_upload_limit = 50,
    message_limit = 500
WHERE slug = 'gold';

UPDATE subscription_plans SET 
    file_upload_limit = NULL,
    message_limit = NULL
WHERE slug = 'premium';

-- =====================================================
-- Create views (run after payment_history table exists)
-- =====================================================

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);

DROP VIEW IF EXISTS admin_user_activity_summary;
CREATE 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,
    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;
