-- ═══════════════════════════════════════════════════════════════════
-- 🔥 DIDON PANEL - Complete Database Setup
-- ═══════════════════════════════════════════════════════════════════
-- Based on reverse-engineered API structure
-- Compatible with DIDON App v2.6
-- ═══════════════════════════════════════════════════════════════════

-- Create database
CREATE DATABASE IF NOT EXISTS didon_panel CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE didon_panel;

-- ═══════════════════════════════════════════════════════════════════
-- USERS TABLE
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    android_id VARCHAR(100),
    mac_ethernet VARCHAR(50),
    mac_wifi VARCHAR(50),
    expiry_date DATE,
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME,
    last_ip VARCHAR(45),
    login_count INT DEFAULT 0,
    status ENUM('active', 'expired', 'disabled') DEFAULT 'active',
    notes TEXT,

    INDEX idx_code (code),
    INDEX idx_android_id (android_id),
    INDEX idx_status (status),
    INDEX idx_expiry (expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- CATEGORIES TABLE
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255),
    icon VARCHAR(500),
    type ENUM('live', 'vod', 'series', 'radio') NOT NULL,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_type (type),
    INDEX idx_active (is_active),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- CHANNELS TABLE
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS channels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    logo VARCHAR(500),
    stream_url VARCHAR(1000) NOT NULL,
    epg_id VARCHAR(100),
    category_id INT NOT NULL,
    type ENUM('live', 'vod', 'series', 'radio') NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    view_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_category (category_id),
    INDEX idx_type (type),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- BOUQUETS TABLE
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS bouquets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    icon VARCHAR(500),
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_active (is_active),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- INTRO SLIDES TABLE
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS intro_slides (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    image_url VARCHAR(500),
    video_url VARCHAR(500),
    type ENUM('image', 'video') DEFAULT 'image',
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_active (is_active),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- MAIN BANNERS TABLE
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS main_banners (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    image_url VARCHAR(500) NOT NULL,
    link_type ENUM('url', 'category', 'channel', 'none') DEFAULT 'none',
    link_value VARCHAR(500),
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_active (is_active),
    INDEX idx_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- LOGIN LOGS TABLE
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS login_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    android_id VARCHAR(100),
    code_used VARCHAR(50),
    ip_address VARCHAR(45),
    user_agent TEXT,
    success BOOLEAN DEFAULT TRUE,
    login_time DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_time (login_time),
    INDEX idx_success (success)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- NETWORK LOGS TABLE (for Method 17)
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS network_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    ip_address VARCHAR(45),
    carrier VARCHAR(255),
    country VARCHAR(100),
    latitude VARCHAR(50),
    longitude VARCHAR(50),
    app_version VARCHAR(20),
    logged_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_time (logged_at),
    INDEX idx_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════
-- SAMPLE DATA
-- ═══════════════════════════════════════════════════════════════════

-- Insert test user
INSERT INTO users (username, code, expiry_date, status) VALUES
('TestUser', '123456', DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 'active'),
('Admin', 'ADMIN123', NULL, 'active');

-- Insert categories
INSERT INTO categories (name, name_ar, type, display_order, is_active) VALUES
('Live TV', 'البث المباشر', 'live', 1, TRUE),
('Movies', 'أفلام', 'vod', 2, TRUE),
('Series', 'مسلسلات', 'series', 3, TRUE),
('Radio', 'راديو', 'radio', 4, TRUE);

-- Insert sample channels
INSERT INTO channels (name, logo, stream_url, category_id, type, is_active) VALUES
('Channel 1', 'http://example.com/logo1.png', 'http://example.com/stream1.m3u8', 1, 'live', TRUE),
('Channel 2', 'http://example.com/logo2.png', 'http://example.com/stream2.m3u8', 1, 'live', TRUE),
('Movie Channel', 'http://example.com/movie.png', 'http://example.com/movie.mp4', 2, 'vod', TRUE);

-- Insert sample bouquet
INSERT INTO bouquets (name, description, display_order, is_active) VALUES
('Premium Package', 'All premium channels', 1, TRUE),
('Basic Package', 'Basic channels only', 2, TRUE);

-- Insert sample intro slide
INSERT INTO intro_slides (title, image_url, type, display_order, is_active) VALUES
('Welcome', 'http://example.com/welcome.jpg', 'image', 1, TRUE),
('Features', 'http://example.com/features.jpg', 'image', 2, TRUE);

-- Insert sample main banner
INSERT INTO main_banners (title, image_url, link_type, display_order, is_active) VALUES
('Featured Content', 'http://example.com/banner1.jpg', 'none', 1, TRUE),
('New Movies', 'http://example.com/banner2.jpg', 'category', 2, TRUE);

-- ═══════════════════════════════════════════════════════════════════
-- SHOW SUMMARY
-- ═══════════════════════════════════════════════════════════════════
SELECT '✅ Database created successfully!' AS status;
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(*) AS total_categories FROM categories;
SELECT COUNT(*) AS total_channels FROM channels;

-- Show test credentials
SELECT
    '📌 TEST CREDENTIALS' AS info,
    code AS 'Activation Code',
    username AS 'Username',
    expiry_date AS 'Expires',
    status AS 'Status'
FROM users;
