-- ============================================
-- WILDANET ADMIN DASHBOARD - COMPLETE SETUP
-- ============================================
-- Run this entire script on your server:
-- mysql -u quiz_user -p'Quiz2024!Secure' quiz_db < COMPLETE_SETUP.sql
-- ============================================

USE quiz_db;

-- ============================================
-- 1. CREATE PRODUCTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    guid VARCHAR(64) UNIQUE NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    product_speed_down_mbps INT NOT NULL,
    product_speed_up_mbps INT NOT NULL,
    monthly_price_gbp DECIMAL(10,2) NOT NULL,
    setup_fee_gbp DECIMAL(10,2) DEFAULT NULL,
    contract_months INT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_guid (guid),
    INDEX idx_active (is_active),
    INDEX idx_display_order (display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default products
INSERT INTO products (guid, product_name, product_speed_down_mbps, product_speed_up_mbps, monthly_price_gbp, setup_fee_gbp, contract_months, is_active, display_order) VALUES
('prod_basic_150', 'Basic 150', 150, 150, 29.99, 0.00, 12, 1, 1),
('prod_standard_300', 'Standard 300', 300, 300, 34.99, 0.00, 12, 1, 2),
('prod_fast_500', 'Fast 500', 500, 500, 39.99, 0.00, 12, 1, 3),
('prod_superfast_900', 'Superfast 900', 900, 900, 44.99, 0.00, 12, 1, 4),
('prod_ultrafast_1200', 'Ultrafast 1200', 1200, 1200, 49.99, 0.00, 12, 1, 5),
('prod_gigafast_2500', 'Gigafast 2500', 2500, 2500, 59.99, 0.00, 12, 1, 6)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- ============================================
-- 2. CREATE QUIZ_SESSIONS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS quiz_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    session_guid VARCHAR(64) UNIQUE NOT NULL,
    postcode VARCHAR(16) DEFAULT NULL,
    started_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    completed_at DATETIME DEFAULT NULL,
    completed_sale TINYINT(1) DEFAULT 0,
    selected_product_guid VARCHAR(64) DEFAULT NULL,
    user_email VARCHAR(255) DEFAULT NULL,
    user_name VARCHAR(255) DEFAULT NULL,
    user_phone VARCHAR(50) DEFAULT NULL,
    address_line1 VARCHAR(255) DEFAULT NULL,
    address_line2 VARCHAR(255) DEFAULT NULL,
    town VARCHAR(100) DEFAULT NULL,
    source VARCHAR(100) DEFAULT NULL,
    medium VARCHAR(100) DEFAULT NULL,
    campaign VARCHAR(100) DEFAULT NULL,
    term VARCHAR(100) DEFAULT NULL,
    content VARCHAR(100) DEFAULT NULL,
    landing_page VARCHAR(255) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    actioned TINYINT(1) DEFAULT 0,
    actioned_by VARCHAR(255) DEFAULT NULL,
    actioned_at DATETIME DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    INDEX idx_session_guid (session_guid),
    INDEX idx_postcode (postcode),
    INDEX idx_started_at (started_at),
    INDEX idx_completed_at (completed_at),
    INDEX idx_completed_sale (completed_sale),
    INDEX idx_source (source),
    INDEX idx_medium (medium),
    INDEX idx_campaign (campaign),
    INDEX idx_actioned (actioned),
    INDEX idx_product (selected_product_guid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. CREATE QUIZ_EVENTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS quiz_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    session_guid VARCHAR(64) NOT NULL,
    event_time DATETIME NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    quiz_stage VARCHAR(50) DEFAULT NULL,
    event_value VARCHAR(255) DEFAULT NULL,
    page_url VARCHAR(255) DEFAULT NULL,
    referrer VARCHAR(255) DEFAULT NULL,
    INDEX idx_session_guid (session_guid),
    INDEX idx_event_time (event_time),
    INDEX idx_event_type (event_type),
    INDEX idx_quiz_stage (quiz_stage),
    INDEX idx_composite (session_guid, event_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. CREATE ADMIN_USERS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'marketing') NOT NULL DEFAULT 'marketing',
    is_active TINYINT(1) DEFAULT 1,
    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_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. CREATE DEFAULT ADMIN USERS
-- ============================================
-- Password for both: Admin123!
-- Generated using: password_hash('Admin123!', PASSWORD_BCRYPT)
INSERT INTO admin_users (email, password_hash, role, is_active) VALUES
('admin@wildanet.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 1),
('marketing@wildanet.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'marketing', 1)
ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;

-- ============================================
-- 6. VERIFY TABLES
-- ============================================
SELECT 'Setup Complete! Tables created:' AS status;
SHOW TABLES LIKE 'products';
SHOW TABLES LIKE 'quiz_sessions';
SHOW TABLES LIKE 'quiz_events';
SHOW TABLES LIKE 'admin_users';

SELECT 'Default products count:' AS status, COUNT(*) AS count FROM products;
SELECT 'Admin users count:' AS status, COUNT(*) AS count FROM admin_users;
