-- ST Tools Database Schema
-- Version 1.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `value` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Default settings data
--

INSERT INTO `settings` (`setting_key`, `value`) VALUES
('site_name', 'ST Tools'),
('site_description', 'Professional Online Tools & Generators Platform'),
('site_keywords', 'online tools, password generator, username generator, image tools, seo tools'),
('site_logo', ''),
('site_favicon', ''),
('footer_text', '© 2024 ST Tools. All rights reserved.'),
('analytics_code', ''),
('enable_ads', '1'),
('theme_mode', 'light');

-- --------------------------------------------------------

--
-- Table structure for table `admin_users`
--

CREATE TABLE `admin_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','moderator') DEFAULT 'admin',
  `status` enum('active','inactive') DEFAULT 'active',
  `last_login` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `advertisements`
--

CREATE TABLE `advertisements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ad_name` varchar(100) NOT NULL,
  `ad_position` enum('header','sidebar','footer','between_sections','sticky_bottom','custom') NOT NULL,
  `ad_code` text NOT NULL,
  `ad_size` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `display_order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `tools`
--

CREATE TABLE `tools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tool_name` varchar(100) NOT NULL,
  `tool_slug` varchar(100) NOT NULL,
  `tool_description` text,
  `tool_icon` varchar(100) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `display_order` int(11) DEFAULT 0,
  `meta_title` varchar(200) DEFAULT NULL,
  `meta_description` varchar(300) DEFAULT NULL,
  `meta_keywords` varchar(300) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tool_slug` (`tool_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Default tools data
--

INSERT INTO `tools` (`tool_name`, `tool_slug`, `tool_description`, `tool_icon`, `category`, `status`, `display_order`, `meta_title`, `meta_description`) VALUES
('Password Generator', 'password-generator', 'Generate strong and secure passwords', 'fa-lock', 'Security', 'active', 1, 'Password Generator - Create Strong Passwords', 'Generate strong, random passwords with customizable options'),
('Bulk Password Generator', 'bulk-password-generator', 'Generate multiple passwords at once', 'fa-lock', 'Security', 'active', 2, 'Bulk Password Generator', 'Generate multiple strong passwords at once'),
('Username Generator', 'username-generator', 'Generate unique usernames', 'fa-user', 'Generator', 'active', 3, 'Username Generator - Create Unique Usernames', 'Generate creative and unique usernames'),
('Full Name Generator', 'name-generator', 'Generate random full names', 'fa-id-card', 'Generator', 'active', 4, 'Name Generator - Random Name Generator', 'Generate random full names for any purpose'),
('Nickname Generator', 'nickname-generator', 'Generate cool nicknames', 'fa-star', 'Generator', 'active', 5, 'Nickname Generator', 'Generate cool and creative nicknames'),
('2FA/TOTP Generator', 'totp-generator', 'Generate 2FA/TOTP codes', 'fa-shield-alt', 'Security', 'active', 6, '2FA/TOTP Generator', 'Generate two-factor authentication codes'),
('QR Code Generator', 'qr-code-generator', 'Generate QR codes', 'fa-qrcode', 'Generator', 'active', 7, 'QR Code Generator', 'Generate QR codes for any text or URL'),
('Image Resize Tool', 'image-resize', 'Resize images online', 'fa-expand-arrows-alt', 'Image Tools', 'active', 8, 'Image Resize Tool', 'Resize images online for free'),
('Image Compressor', 'image-compressor', 'Compress images', 'fa-compress', 'Image Tools', 'active', 9, 'Image Compressor', 'Compress images without losing quality'),
('Image Converter', 'image-converter', 'Convert image formats', 'fa-exchange-alt', 'Image Tools', 'active', 10, 'Image Converter', 'Convert images between JPG, PNG, WEBP formats'),
('Base64 Encoder/Decoder', 'base64-tool', 'Encode and decode Base64', 'fa-code', 'Developer Tools', 'active', 11, 'Base64 Encoder/Decoder', 'Encode and decode Base64 strings'),
('JSON Formatter', 'json-formatter', 'Format and validate JSON', 'fa-brackets-curly', 'Developer Tools', 'active', 12, 'JSON Formatter', 'Format, validate and beautify JSON'),
('URL Encoder/Decoder', 'url-encoder', 'Encode and decode URLs', 'fa-link', 'Developer Tools', 'active', 13, 'URL Encoder/Decoder', 'Encode and decode URLs'),
('Word Counter', 'word-counter', 'Count words and characters', 'fa-file-word', 'Utility Tools', 'active', 14, 'Word Counter', 'Count words, characters, and sentences'),
('Meta Tag Generator', 'meta-tag-generator', 'Generate meta tags', 'fa-tags', 'SEO Tools', 'active', 15, 'Meta Tag Generator', 'Generate SEO meta tags'),
('Hashtag Generator', 'hashtag-generator', 'Generate hashtags', 'fa-hashtag', 'Social Media', 'active', 16, 'Hashtag Generator', 'Generate trending hashtags'),
('Keyword Research Tool', 'keyword-research', 'Research keywords', 'fa-search', 'SEO Tools', 'active', 17, 'Keyword Research Tool', 'Find the best keywords for SEO'),
('Text Generator', 'text-generator', 'Generate random text', 'fa-text', 'Generator', 'active', 18, 'Text Generator', 'Generate random text and lorem ipsum'),
('Random Code Generator', 'random-code-generator', 'Generate random codes', 'fa-random', 'Generator', 'active', 19, 'Random Code Generator', 'Generate random codes and strings');

-- --------------------------------------------------------

--
-- Table structure for table `custom_pages`
--

CREATE TABLE `custom_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `html_content` longtext,
  `php_code` text,
  `meta_title` varchar(200) DEFAULT NULL,
  `meta_description` varchar(300) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `show_in_menu` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `analytics`
--

CREATE TABLE `analytics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `page_url` varchar(500) DEFAULT NULL,
  `tool_name` varchar(100) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `referer` varchar(500) DEFAULT NULL,
  `device_type` varchar(20) DEFAULT NULL,
  `browser` varchar(50) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `visited_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_visited_at` (`visited_at`),
  KEY `idx_tool_name` (`tool_name`),
  KEY `idx_page_url` (`page_url`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_selections`
--

CREATE TABLE `user_selections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(100) NOT NULL,
  `selected_tools` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Default admin user (username: admin, password: admin123)
-- CHANGE THIS PASSWORD AFTER INSTALLATION!
--

INSERT INTO `admin_users` (`username`, `email`, `password`, `role`, `status`) VALUES
('admin', 'admin@sttools.com', '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqR4XQPQR2', 'admin', 'active');