-- Movie Links Website - Repair & Setup Script
-- This script will create missing tables and rows without deleting your existing data.

SET FOREIGN_KEY_CHECKS = 0;

-- Table Structures

-- Admin Users Table
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin') DEFAULT 'admin',
    status ENUM('active', 'inactive') DEFAULT 'inactive',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Categories Table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Movies/Links Table
CREATE TABLE IF NOT EXISTS movies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    category_id INT,
    thumbnail VARCHAR(255),
    thumbnail_blurred VARCHAR(255),
    blur_settings TEXT,
    links JSON,
    embed_links TEXT,
    featured ENUM('yes', 'no') DEFAULT 'no',
    status ENUM('active', 'inactive', 'pending') DEFAULT 'active',
    views INT DEFAULT 0,
    clicks INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Click Tracking Table
CREATE TABLE IF NOT EXISTS click_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    movie_id INT NOT NULL,
    link_index INT DEFAULT 0,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    referrer VARCHAR(255),
    country VARCHAR(50),
    city VARCHAR(50),
    device_type VARCHAR(20),
    browser VARCHAR(50),
    clicked_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE
);

-- Traffic Analytics Table
CREATE TABLE IF NOT EXISTS traffic_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    total_visitors INT DEFAULT 0,
    total_clicks INT DEFAULT 0,
    unique_visitors INT DEFAULT 0,
    page_views INT DEFAULT 0,
    bounce_rate DECIMAL(5,2) DEFAULT 0,
    avg_time_on_site INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_date (date)
);

-- Ad Placements Table
CREATE TABLE IF NOT EXISTS ad_placements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50) NOT NULL,
    ad_type ENUM('adsterra', 'admob', 'custom', 'none') DEFAULT 'none',
    ad_code TEXT,
    ad_script TEXT,
    ad_size VARCHAR(50),
    enabled ENUM('yes', 'no') DEFAULT 'yes',
    impressions INT DEFAULT 0,
    clicks INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 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 DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Activity Log Table
CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
);

-- Broken Links Report Table
CREATE TABLE IF NOT EXISTS broken_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    movie_id INT NOT NULL,
    reporter_ip VARCHAR(45),
    message TEXT,
    status ENUM('pending', 'fixed', 'ignored') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (movie_id) REFERENCES movies(id) ON DELETE CASCADE
);

-- Insert default admin (will not overwrite if already exists)
INSERT IGNORE INTO admins (username, email, password, role, status) VALUES 
('admin', 'admin@movilink.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin', 'active');

-- Insert default settings (will only add missing keys)
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES 
('site_title', 'Movie Links'),
('site_logo', ''),
('site_description', 'Watch movies online for free'),
('site_keywords', 'movies, free movies, online streaming'),
('header_ad_code', ''),
('footer_ad_code', ''),
('left_sidebar_ad_code', ''),
('right_sidebar_ad_code', ''),
('adsterra_publisher_id', ''),
('admob_app_id', ''),
('admob_ad_unit_id', ''),
('facebook_link', ''),
('twitter_link', ''),
('instagram_link', ''),
('telegram_link', ''),
('youtube_link', ''),
('footer_text', '© 2026 Movie Links. All rights reserved.'),
('items_per_page', '20'),
('enable_social_share', 'yes'),
('enable_countdown', 'no'),
('countdown_seconds', '5'),
('enable_registration', 'no'),
('maintenance_mode', 'no');

-- Insert default ad placements
INSERT IGNORE INTO ad_placements (name, position, ad_type, ad_size, enabled) VALUES 
('Header Ad', 'header', 'none', '728x90', 'yes'),
('Footer Ad', 'footer', 'none', '728x90', 'yes'),
('Left Sidebar Top', 'left_sidebar_top', 'none', '300x250', 'yes'),
('Left Sidebar Middle', 'left_sidebar_middle', 'none', '300x600', 'yes'),
('Left Sidebar Bottom', 'left_sidebar_bottom', 'none', '300x250', 'yes'),
('Right Sidebar Top', 'right_sidebar_top', 'none', '300x250', 'yes'),
('Right Sidebar Middle', 'right_sidebar_middle', 'none', '300x600', 'yes'),
('Right Sidebar Bottom', 'right_sidebar_bottom', 'none', '160x600', 'yes'),
('Between Movies', 'between_movies', 'none', '300x250', 'yes'),
('Popup Ad', 'popup', 'none', 'fullscreen', 'no');

-- Insert default categories
INSERT IGNORE INTO categories (name, slug, icon, status) VALUES 
('Bollywood', 'bollywood', 'film', 'active'),
('Hollywood', 'hollywood', 'globe', 'active'),
('South Indian', 'south-indian', 'star', 'active'),
('Web Series', 'web-series', 'tv', 'active'),
('TV Shows', 'tv-shows', 'monitor', 'active'),
('Anime', 'anime', 'zap', 'active'),
('Documentary', 'documentary', 'book-open', 'active');

INSERT IGNORE INTO ad_placements (position, ad_type, enabled) VALUES ('modal', 'none', 'no');
SET FOREIGN_KEY_CHECKS = 1;