-- CYNX License System Database Schema
-- Version: 2.1 - Fixed for hosting deployment
-- Updated: 2025-12-06

-- Create Database
CREATE DATABASE IF NOT EXISTS sieuthi7_abc123 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sieuthi7_abc123;

-- =====================================================
-- USERS TABLE (Admin & Client Users)
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    avatar VARCHAR(255),
    role ENUM('admin', 'user') DEFAULT 'user',
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- LICENSE KEYS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS license_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_code VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    type ENUM('trial', 'monthly', 'yearly', 'lifetime') DEFAULT 'trial',
    status ENUM('active', 'expired', 'banned', 'inactive') DEFAULT 'active',
    uses INT DEFAULT 0,
    max_uses INT DEFAULT 0,
    expires_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    notes TEXT,
    
    INDEX idx_key_code (key_code),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- USAGE LOGS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS usage_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    action VARCHAR(50),
    status ENUM('success', 'failed', 'error') DEFAULT 'success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (key_id) REFERENCES license_keys(id) ON DELETE CASCADE,
    INDEX idx_key_id (key_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CATEGORIES TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PRODUCTS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(12, 2) DEFAULT 0,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_category_id (category_id),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ORDERS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    product_name VARCHAR(255),
    quantity INT DEFAULT 1,
    total_amount DECIMAL(12, 2),
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- RECHARGES TABLE (Bank Transfers)
-- =====================================================
CREATE TABLE IF NOT EXISTS recharges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    user_email VARCHAR(255),
    method ENUM('bank', 'card') DEFAULT 'bank',
    amount DECIMAL(12, 2),
    status ENUM('pending', 'completed', 'rejected', 'refunded') DEFAULT 'pending',
    transaction_id VARCHAR(100),
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- RECHARGE CARDS TABLE (Phone Card Top-ups)
-- =====================================================
CREATE TABLE IF NOT EXISTS recharge_cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    user_email VARCHAR(255),
    provider ENUM('viettel', 'vinfone', 'mobifone', 'other') DEFAULT 'viettel',
    card_code VARCHAR(100),
    card_serial VARCHAR(100),
    amount DECIMAL(12, 2),
    status ENUM('pending', 'completed', 'failed', 'rejected') DEFAULT 'pending',
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_provider (provider),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SETTINGS TABLE
-- =====================================================
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- AUTOMATIONS TABLE (Fixed: backtick on 'interval')
-- =====================================================
CREATE TABLE IF NOT EXISTS automations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    enabled BOOLEAN DEFAULT 1,
    `interval` INT DEFAULT 60,
    last_run DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SAMPLE DATA
-- =====================================================

-- Insert sample admin user (Password: Admin@123)
INSERT INTO users (name, email, password, phone, role, status) VALUES
('Admin User', 'admin@cynx.local', '$2y$10$dXjsUZJlkIhfZD3c.L5Ky.6nK8XqvCqvCqvCqvCqvCqvCqvCqvCq', '0987654321', 'admin', 'active');

-- Insert sample client users
INSERT INTO users (name, email, password, phone, role, status) VALUES
('John Doe', 'john@example.com', '$2y$10$dXjsUZJlkIhfZD3c.L5Ky.6nK8XqvCqvCqvCqvCqvCqvCqvCqvCq', '0901234567', 'user', 'active'),
('Jane Smith', 'jane@example.com', '$2y$10$dXjsUZJlkIhfZD3c.L5Ky.6nK8XqvCqvCqvCqvCqvCqvCqvCqvCq', '0912345678', 'user', 'active');

-- Insert sample license keys
INSERT INTO license_keys (key_code, email, type, status, expires_at) VALUES
('CYNX-TRIAL-2025001', 'john@example.com', 'trial', 'active', DATE_ADD(NOW(), INTERVAL 7 DAY)),
('CYNX-MONTH-2025001', 'jane@example.com', 'monthly', 'active', DATE_ADD(NOW(), INTERVAL 30 DAY)),
('CYNX-YEAR-2025001', 'admin@cynx.local', 'yearly', 'active', DATE_ADD(NOW(), INTERVAL 365 DAY)),
('CYNX-LIFE-2025001', 'john@example.com', 'lifetime', 'active', '2099-12-31 23:59:59'),
('CYNX-TEST-EXPIRED', 'test@example.com', 'trial', 'expired', DATE_SUB(NOW(), INTERVAL 1 DAY));

-- Insert sample categories
INSERT INTO categories (name, description) VALUES
('Software', 'Software licenses and tools'),
('Services', 'Premium services and support'),
('Training', 'Training and certification programs');

-- Insert sample products
INSERT INTO products (category_id, name, description, price, stock) VALUES
(1, 'CYNX VipPlayer Pro', 'Professional video player with advanced features', 99000, 100),
(1, 'CYNX Media Suite', 'Complete media management solution', 199000, 50),
(2, 'Premium Support 1 Year', '24/7 technical support', 350000, 200),
(3, 'Basic Training Course', 'Learn CYNX basics in 4 weeks', 500000, 75);

-- Insert sample orders
INSERT INTO orders (user_id, product_id, product_name, quantity, total_amount, status, payment_method) VALUES
(2, 1, 'CYNX VipPlayer Pro', 1, 99000, 'completed', 'card'),
(3, 2, 'CYNX Media Suite', 1, 199000, 'pending', 'bank');

-- Insert sample settings
INSERT INTO settings (setting_key, setting_value) VALUES
('app_name', 'CYNX License System'),
('app_url', 'http://localhost:8000'),
('currency', 'VND'),
('admin_email', 'admin@cynx.local'),
('theme', 'light'),
('accent_color', '#667eea'),
('maintenance_mode', '0'),
('maintenance_message', 'Xin lỗi, hệ thống đang bảo trì. Sẽ sớm quay lại.');

-- Insert automations (Fixed: backtick on 'interval')
INSERT INTO automations (name, description, enabled, `interval`) VALUES
('License Expiration', 'Automatically mark expired licenses as inactive', 1, 60),
('Email Notifications', 'Send email notifications for expired licenses', 1, 1440),
('Database Cleanup', 'Clean old logs and temporary data', 1, 10080),
('Usage Sync', 'Synchronize usage data from API logs', 1, 120);

-- =====================================================
-- USEFUL VIEWS
-- =====================================================

-- Active licenses view
CREATE OR REPLACE VIEW v_active_licenses AS
SELECT id, key_code, email, type, status, created_at, expires_at
FROM license_keys
WHERE status = 'active' AND (expires_at > NOW() OR type = 'lifetime');

-- Expired licenses view
CREATE OR REPLACE VIEW v_expired_licenses AS
SELECT id, key_code, email, type, expires_at
FROM license_keys
WHERE status = 'expired' OR (expires_at < NOW() AND type != 'lifetime');

-- License statistics view
CREATE OR REPLACE VIEW v_license_stats AS
SELECT 
    type as license_type,
    COUNT(*) as total,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
    SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) as expired,
    SUM(CASE WHEN status = 'banned' THEN 1 ELSE 0 END) as banned
FROM license_keys
GROUP BY type;

-- User activity view
CREATE OR REPLACE VIEW v_user_activity AS
SELECT 
    u.id, u.name, u.email, 
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.role = 'user'
GROUP BY u.id;
