-- ============================================
-- Wallpaper Application - Database Schema
-- ============================================

CREATE DATABASE IF NOT EXISTS `wallpaper_app` 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

USE `wallpaper_app`;

-- ============================================
-- Admins Table
-- ============================================
CREATE TABLE `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) DEFAULT NULL,
  `avatar_url` VARCHAR(500) DEFAULT NULL,
  `role` ENUM('super_admin', 'admin', 'editor') NOT NULL DEFAULT 'admin',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admins_username` (`username`),
  UNIQUE KEY `uk_admins_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Users Table (Device-based)
-- ============================================
CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL,
  `fcm_token` VARCHAR(500) DEFAULT NULL,
  `device_model` VARCHAR(100) DEFAULT NULL,
  `device_brand` VARCHAR(100) DEFAULT NULL,
  `os_version` VARCHAR(20) DEFAULT NULL,
  `app_version` VARCHAR(20) DEFAULT NULL,
  `screen_width` INT DEFAULT NULL,
  `screen_height` INT DEFAULT NULL,
  `country` VARCHAR(5) DEFAULT NULL,
  `language` VARCHAR(10) DEFAULT NULL,
  `first_seen` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_users_device_id` (`device_id`),
  INDEX `idx_users_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Categories Table
-- ============================================
CREATE TABLE `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `thumbnail_url` VARCHAR(500) DEFAULT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `wallpaper_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_categories_slug` (`slug`),
  INDEX `idx_categories_sort` (`sort_order`),
  INDEX `idx_categories_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Wallpapers Table
-- ============================================
CREATE TABLE `wallpapers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `image_url` VARCHAR(500) NOT NULL,
  `thumbnail_url` VARCHAR(500) NOT NULL,
  `original_filename` VARCHAR(255) DEFAULT NULL,
  `width` INT UNSIGNED DEFAULT NULL,
  `height` INT UNSIGNED DEFAULT NULL,
  `file_size` BIGINT UNSIGNED DEFAULT NULL,
  `mime_type` VARCHAR(50) DEFAULT NULL,
  `color_hex` VARCHAR(7) DEFAULT NULL,
  `tags` VARCHAR(500) DEFAULT NULL,
  `download_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `view_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `like_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `is_premium` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_wallpapers_category` (`category_id`),
  INDEX `idx_wallpapers_featured` (`is_featured`),
  INDEX `idx_wallpapers_active` (`is_active`),
  INDEX `idx_wallpapers_downloads` (`download_count` DESC),
  INDEX `idx_wallpapers_created` (`created_at` DESC),
  FULLTEXT INDEX `ft_wallpapers_search` (`title`, `tags`),
  CONSTRAINT `fk_wallpapers_category` 
    FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) 
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Favorites Table
-- ============================================
CREATE TABLE `favorites` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `wallpaper_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_favorites_user_wallpaper` (`user_id`, `wallpaper_id`),
  INDEX `idx_favorites_user` (`user_id`),
  INDEX `idx_favorites_wallpaper` (`wallpaper_id`),
  CONSTRAINT `fk_favorites_user` 
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_favorites_wallpaper` 
    FOREIGN KEY (`wallpaper_id`) REFERENCES `wallpapers` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Downloads Table
-- ============================================
CREATE TABLE `downloads` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `wallpaper_id` INT UNSIGNED NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `downloaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_downloads_user` (`user_id`),
  INDEX `idx_downloads_wallpaper` (`wallpaper_id`),
  INDEX `idx_downloads_date` (`downloaded_at`),
  CONSTRAINT `fk_downloads_user` 
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) 
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_downloads_wallpaper` 
    FOREIGN KEY (`wallpaper_id`) REFERENCES `wallpapers` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Settings Table
-- ============================================
CREATE TABLE `settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `setting_type` ENUM('string', 'number', 'boolean', 'json') NOT NULL DEFAULT 'string',
  `description` VARCHAR(255) DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_settings_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Default Data
-- ============================================

-- Default Admin (password: admin123 — CHANGE IN PRODUCTION!)
INSERT INTO `admins` (`username`, `email`, `password_hash`, `full_name`, `role`) VALUES
('admin', 'admin@wallpaperapp.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Admin', 'super_admin');

-- Default Settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('app_name', 'Wallpaper App', 'string', 'Application name'),
('api_key', 'wapp_api_key_change_me_in_production', 'string', 'API key for mobile app authentication'),
('items_per_page', '20', 'number', 'Default pagination limit'),
('max_upload_size_mb', '10', 'number', 'Maximum upload file size in MB'),
('allowed_extensions', 'jpg,jpeg,png,webp', 'string', 'Allowed image file extensions'),
('thumbnail_width', '400', 'number', 'Thumbnail width in pixels'),
('thumbnail_quality', '80', 'number', 'Thumbnail JPEG quality (1-100)'),
('admob_banner_id', '', 'string', 'AdMob Banner Ad Unit ID'),
('admob_interstitial_id', '', 'string', 'AdMob Interstitial Ad Unit ID'),
('latest_app_version', '1.0.0', 'string', 'Latest app version for force update check'),
('force_update', '0', 'boolean', 'Force users to update the app'),
('maintenance_mode', '0', 'boolean', 'Enable maintenance mode');

-- Sample Categories
INSERT INTO `categories` (`name`, `slug`, `sort_order`) VALUES
('Nature', 'nature', 1),
('Abstract', 'abstract', 2),
('Animals', 'animals', 3),
('Cars', 'cars', 4),
('City', 'city', 5),
('Dark', 'dark', 6),
('Flowers', 'flowers', 7),
('Minimal', 'minimal', 8),
('Space', 'space', 9),
('Technology', 'technology', 10);