-- Super Admin System and Activity Log Migration
-- Run this migration to add super admin features, activity logging, and enhanced plan management

-- =====================================================
-- 1. SUPER ADMIN TABLES
-- =====================================================

-- Super admin users table (separate from regular users for security)
CREATE TABLE IF NOT EXISTS super_admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('owner', 'admin', 'support', 'finance') DEFAULT 'support',
    is_active TINYINT(1) DEFAULT 1,
    last_login_at DATETIME NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES super_admins(id) ON DELETE SET NULL
);

-- Super admin permissions
CREATE TABLE IF NOT EXISTS super_admin_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    permission_key VARCHAR(100) NOT NULL,
    allowed TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES super_admins(id) ON DELETE CASCADE,
    UNIQUE KEY unique_admin_permission (admin_id, permission_key)
);

-- Super admin sessions
CREATE TABLE IF NOT EXISTS super_admin_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    session_token VARCHAR(255) NOT NULL UNIQUE,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES super_admins(id) ON DELETE CASCADE
);

-- Super admin activity log
CREATE TABLE IF NOT EXISTS super_admin_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NULL,
    entity_id INT NULL,
    details JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES super_admins(id) ON DELETE CASCADE,
    INDEX idx_admin_activity (admin_id, created_at),
    INDEX idx_entity (entity_type, entity_id)
);

-- =====================================================
-- 2. PLATFORM SETTINGS TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS platform_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    category VARCHAR(50) DEFAULT 'general',
    description TEXT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES super_admins(id) ON DELETE SET NULL
);

-- Insert default platform settings
INSERT INTO platform_settings (setting_key, setting_value, setting_type, category, description) VALUES
-- Payment Gateway Settings
('stripe_enabled', 'true', 'boolean', 'payments', 'Enable Stripe payments'),
('stripe_public_key', '', 'string', 'payments', 'Stripe publishable key'),
('stripe_secret_key', '', 'string', 'payments', 'Stripe secret key'),
('stripe_webhook_secret', '', 'string', 'payments', 'Stripe webhook signing secret'),
('razorpay_enabled', 'true', 'boolean', 'payments', 'Enable Razorpay payments'),
('razorpay_key_id', '', 'string', 'payments', 'Razorpay Key ID'),
('razorpay_key_secret', '', 'string', 'payments', 'Razorpay Key Secret'),
('razorpay_webhook_secret', '', 'string', 'payments', 'Razorpay webhook secret'),

-- Currency Settings
('default_currency', 'USD', 'string', 'billing', 'Default currency'),
('supported_currencies', '["USD", "INR"]', 'json', 'billing', 'Supported currencies'),
('usd_to_inr_rate', '83', 'number', 'billing', 'USD to INR conversion rate'),

-- Trial Settings
('trial_enabled', 'true', 'boolean', 'billing', 'Enable trial period for new users'),
('trial_days', '14', 'number', 'billing', 'Number of trial days'),
('trial_plan_id', '1', 'number', 'billing', 'Plan ID to use during trial'),

-- Discount Settings
('discount_enabled', 'false', 'boolean', 'billing', 'Enable discount codes'),
('yearly_discount_percent', '20', 'number', 'billing', 'Discount percentage for yearly billing')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

-- =====================================================
-- 3. ENHANCED SUBSCRIPTION PLANS
-- =====================================================

-- Add currency-specific pricing columns to subscription_plans (ignore errors if columns exist)
-- Run these one at a time to handle existing columns gracefully
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'subscription_plans' AND COLUMN_NAME = 'price_monthly_inr') = 0,
    'ALTER TABLE subscription_plans ADD COLUMN price_monthly_inr DECIMAL(10,2) NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'subscription_plans' AND COLUMN_NAME = 'price_yearly_inr') = 0,
    'ALTER TABLE subscription_plans ADD COLUMN price_yearly_inr DECIMAL(10,2) NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'subscription_plans' AND COLUMN_NAME = 'trial_days') = 0,
    'ALTER TABLE subscription_plans ADD COLUMN trial_days INT DEFAULT 0',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'subscription_plans' AND COLUMN_NAME = 'is_popular') = 0,
    'ALTER TABLE subscription_plans ADD COLUMN is_popular TINYINT(1) DEFAULT 0',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'subscription_plans' AND COLUMN_NAME = 'description') = 0,
    'ALTER TABLE subscription_plans ADD COLUMN description TEXT NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Update existing plans with INR prices (83 INR per USD)
UPDATE subscription_plans SET 
    price_monthly_inr = price_monthly * 83,
    price_yearly_inr = price_yearly * 83
WHERE price_monthly_inr IS NULL;

-- =====================================================
-- 4. DISCOUNT CODES TABLE
-- =====================================================

CREATE TABLE IF NOT EXISTS discount_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT NULL,
    discount_type ENUM('percent', 'fixed') DEFAULT 'percent',
    discount_value DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NULL COMMENT 'NULL means applies to all currencies',
    applies_to ENUM('all', 'monthly', 'yearly') DEFAULT 'all',
    plan_ids JSON NULL COMMENT 'NULL means applies to all plans',
    max_uses INT NULL COMMENT 'NULL means unlimited',
    used_count INT DEFAULT 0,
    valid_from DATETIME NULL,
    valid_until DATETIME NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES super_admins(id) ON DELETE SET NULL
);

-- Discount code usage tracking
CREATE TABLE IF NOT EXISTS discount_code_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    discount_code_id INT NOT NULL,
    company_id INT NOT NULL,
    user_id INT NOT NULL,
    subscription_id INT NULL,
    discount_amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (discount_code_id) REFERENCES discount_codes(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =====================================================
-- 5. WORKSPACE ACTIVITY LOG
-- =====================================================

CREATE TABLE IF NOT EXISTS workspace_activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NULL,
    entity_id INT NULL,
    entity_name VARCHAR(255) NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_company_activity (company_id, created_at),
    INDEX idx_user_activity (user_id, created_at),
    INDEX idx_entity (entity_type, entity_id)
);

-- =====================================================
-- 6. USER TRACKING ENHANCEMENTS
-- =====================================================

-- Add columns to users table for better tracking
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_banned') = 0,
    'ALTER TABLE users ADD COLUMN is_banned TINYINT(1) DEFAULT 0',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'banned_at') = 0,
    'ALTER TABLE users ADD COLUMN banned_at DATETIME NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'banned_reason') = 0,
    'ALTER TABLE users ADD COLUMN banned_reason TEXT NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'preferred_currency') = 0,
    'ALTER TABLE users ADD COLUMN preferred_currency VARCHAR(3) DEFAULT \'USD\'',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add columns to companies table for better tracking
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'is_banned') = 0,
    'ALTER TABLE companies ADD COLUMN is_banned TINYINT(1) DEFAULT 0',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'banned_at') = 0,
    'ALTER TABLE companies ADD COLUMN banned_at DATETIME NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'banned_reason') = 0,
    'ALTER TABLE companies ADD COLUMN banned_reason TEXT NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'preferred_currency') = 0,
    'ALTER TABLE companies ADD COLUMN preferred_currency VARCHAR(3) DEFAULT \'USD\'',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'trial_ends_at') = 0,
    'ALTER TABLE companies ADD COLUMN trial_ends_at DATETIME NULL',
    'SELECT 1'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- 7. LIVE USER TRACKING
-- =====================================================

CREATE TABLE IF NOT EXISTS user_presence (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    company_id INT NULL,
    page_url VARCHAR(500) NULL,
    last_activity_at DATETIME NOT NULL,
    session_id VARCHAR(255) NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
    UNIQUE KEY unique_user_presence (user_id),
    INDEX idx_last_activity (last_activity_at)
);

-- =====================================================
-- 8. INSERT DEFAULT SUPER ADMIN
-- =====================================================

-- Create default super admin (password: admin123 - CHANGE THIS!)
INSERT INTO super_admins (name, email, password_hash, role) VALUES
('Super Admin', 'admin@keelance.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'owner')
ON DUPLICATE KEY UPDATE email = email;

-- Grant all permissions to the owner
INSERT INTO super_admin_permissions (admin_id, permission_key, allowed)
SELECT 
    (SELECT id FROM super_admins WHERE role = 'owner' LIMIT 1),
    permission_key,
    1
FROM (
    SELECT 'users_view' AS permission_key UNION ALL
    SELECT 'users_manage' UNION ALL
    SELECT 'users_ban' UNION ALL
    SELECT 'users_delete' UNION ALL
    SELECT 'workspaces_view' UNION ALL
    SELECT 'workspaces_manage' UNION ALL
    SELECT 'workspaces_ban' UNION ALL
    SELECT 'workspaces_delete' UNION ALL
    SELECT 'workspaces_login' UNION ALL
    SELECT 'billing_view' UNION ALL
    SELECT 'billing_manage' UNION ALL
    SELECT 'plans_view' UNION ALL
    SELECT 'plans_manage' UNION ALL
    SELECT 'discounts_view' UNION ALL
    SELECT 'discounts_manage' UNION ALL
    SELECT 'settings_view' UNION ALL
    SELECT 'settings_manage' UNION ALL
    SELECT 'admins_view' UNION ALL
    SELECT 'admins_manage' UNION ALL
    SELECT 'analytics_view' UNION ALL
    SELECT 'activity_log_view'
) AS permissions
ON DUPLICATE KEY UPDATE allowed = 1;

-- =====================================================
-- 9. INDEXES FOR PERFORMANCE (ignore errors if indexes exist)
-- =====================================================

-- Note: These may fail if indexes already exist, which is fine
