SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  force_password_change TINYINT(1) NOT NULL DEFAULT 0,
  failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value LONGTEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sources (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  source_type ENUM('google_play_browse','json_feed') NOT NULL DEFAULT 'google_play_browse',
  source_url VARCHAR(1000) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  priority SMALLINT NOT NULL DEFAULT 50,
  interval_hours SMALLINT UNSIGNED NOT NULL DEFAULT 24,
  next_run_at DATETIME NULL,
  last_run_at DATETIME NULL,
  last_success_at DATETIME NULL,
  failure_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  last_error VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_sources_url (source_url(190)),
  INDEX idx_sources_due (active,next_run_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS games (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NULL,
  package_id VARCHAR(255) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(300) NOT NULL UNIQUE,
  developer VARCHAR(255) NOT NULL DEFAULT '',
  category VARCHAR(120) NOT NULL DEFAULT 'Games',
  short_description VARCHAR(500) NOT NULL DEFAULT '',
  source_description_hash CHAR(64) NULL,
  icon_url VARCHAR(1200) NOT NULL DEFAULT '',
  play_url VARCHAR(1200) NOT NULL,
  rating DECIMAL(3,2) NULL,
  rating_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  price VARCHAR(40) NOT NULL DEFAULT '0',
  currency CHAR(3) NOT NULL DEFAULT 'INR',
  content_rating VARCHAR(80) NOT NULL DEFAULT '',
  operating_system VARCHAR(120) NOT NULL DEFAULT 'Android',
  date_published DATE NULL,
  date_modified DATE NULL,
  status ENUM('review','published','hidden','removed') NOT NULL DEFAULT 'published',
  featured TINYINT(1) NOT NULL DEFAULT 0,
  custom_title TINYINT(1) NOT NULL DEFAULT 0,
  custom_summary TINYINT(1) NOT NULL DEFAULT 0,
  page_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
  first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_checked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_games_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE SET NULL,
  INDEX idx_games_status_updated (status,updated_at),
  INDEX idx_games_category (category,status),
  INDEX idx_games_featured (featured,status),
  FULLTEXT KEY ft_games_search (title,developer,short_description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS import_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  package_id VARCHAR(255) NOT NULL UNIQUE,
  source_id INT UNSIGNED NULL,
  status ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
  priority SMALLINT NOT NULL DEFAULT 50,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_error VARCHAR(500) NULL,
  available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_queue_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE SET NULL,
  INDEX idx_queue_work (status,available_at,priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS game_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game_id BIGINT UNSIGNED NOT NULL,
  reason VARCHAR(100) NOT NULL,
  message VARCHAR(1000) NULL,
  email VARCHAR(190) NULL,
  ip_hash CHAR(64) NOT NULL,
  status ENUM('open','resolved','dismissed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reports_game FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE,
  INDEX idx_reports_status (status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  subject VARCHAR(180) NOT NULL,
  message VARCHAR(2000) NOT NULL,
  ip_hash CHAR(64) NOT NULL,
  status ENUM('new','read','closed') NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_contacts_status (status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cron_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  status ENUM('running','success','partial','failed') NOT NULL DEFAULT 'running',
  sources_checked INT UNSIGNED NOT NULL DEFAULT 0,
  urls_found INT UNSIGNED NOT NULL DEFAULT 0,
  queue_processed INT UNSIGNED NOT NULL DEFAULT 0,
  imported INT UNSIGNED NOT NULL DEFAULT 0,
  failed INT UNSIGNED NOT NULL DEFAULT 0,
  message VARCHAR(1000) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cron_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_activity_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  details VARCHAR(1000) NULL,
  ip_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_activity_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_activity_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (setting_key,setting_value) VALUES
('site_name','GamesJi Apps'),
('site_description','Discover Android games, explore useful details and visit verified official Google Play listings.'),
('home_url','https://gamesji.com/'),
('auto_publish','1'),
('games_per_page','24'),
('ads_enabled','0'),
('adsense_client',''),
('ad_slot_header',''),
('ad_slot_in_content',''),
('ad_slot_footer',''),
('google_site_verification',''),
('head_code',''),
('footer_code',''),
('contact_email',''),
('privacy_salt','gamesji-install-salt')
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);

INSERT IGNORE INTO sources (name,source_type,source_url,active,priority,interval_hours,next_run_at) VALUES
('Google Play Games','google_play_browse','https://play.google.com/store/games?hl=en_IN&gl=IN',1,100,12,NOW()),
('Action Games','google_play_browse','https://play.google.com/store/apps/category/GAME_ACTION?hl=en_IN&gl=IN',1,90,24,NOW()),
('Adventure Games','google_play_browse','https://play.google.com/store/apps/category/GAME_ADVENTURE?hl=en_IN&gl=IN',1,90,24,NOW()),
('Arcade Games','google_play_browse','https://play.google.com/store/apps/category/GAME_ARCADE?hl=en_IN&gl=IN',1,90,24,NOW()),
('Board Games','google_play_browse','https://play.google.com/store/apps/category/GAME_BOARD?hl=en_IN&gl=IN',1,80,24,NOW()),
('Card Games','google_play_browse','https://play.google.com/store/apps/category/GAME_CARD?hl=en_IN&gl=IN',1,80,24,NOW()),
('Casual Games','google_play_browse','https://play.google.com/store/apps/category/GAME_CASUAL?hl=en_IN&gl=IN',1,90,24,NOW()),
('Educational Games','google_play_browse','https://play.google.com/store/apps/category/GAME_EDUCATIONAL?hl=en_IN&gl=IN',1,80,24,NOW()),
('Puzzle Games','google_play_browse','https://play.google.com/store/apps/category/GAME_PUZZLE?hl=en_IN&gl=IN',1,90,24,NOW()),
('Racing Games','google_play_browse','https://play.google.com/store/apps/category/GAME_RACING?hl=en_IN&gl=IN',1,90,24,NOW()),
('Role Playing Games','google_play_browse','https://play.google.com/store/apps/category/GAME_ROLE_PLAYING?hl=en_IN&gl=IN',1,80,24,NOW()),
('Simulation Games','google_play_browse','https://play.google.com/store/apps/category/GAME_SIMULATION?hl=en_IN&gl=IN',1,80,24,NOW()),
('Sports Games','google_play_browse','https://play.google.com/store/apps/category/GAME_SPORTS?hl=en_IN&gl=IN',1,90,24,NOW()),
('Strategy Games','google_play_browse','https://play.google.com/store/apps/category/GAME_STRATEGY?hl=en_IN&gl=IN',1,80,24,NOW()),
('Word Games','google_play_browse','https://play.google.com/store/apps/category/GAME_WORD?hl=en_IN&gl=IN',1,80,24,NOW());

SET FOREIGN_KEY_CHECKS=1;
