SET NAMES utf8mb4;
SET time_zone = "+00:00";

CREATE TABLE `access_settings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `registration_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `manual_approval` tinyint(1) NOT NULL DEFAULT 0,
  `email_verification` tinyint(1) NOT NULL DEFAULT 0,
  `default_role` varchar(20) NOT NULL DEFAULT 'user',
  `login_required_free` tinyint(1) NOT NULL DEFAULT 0,
  `guest_download_limit` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `guest_limit_period` varchar(20) NOT NULL DEFAULT 'daily',
  `blocked_domains` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `account_delete_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','completed') NOT NULL DEFAULT 'pending',
  `admin_note` text DEFAULT NULL,
  `requested_at` datetime NOT NULL DEFAULT current_timestamp(),
  `processed_at` datetime DEFAULT NULL,
  `processed_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `admins` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(190) NOT NULL,
    `password` varchar(255) NOT NULL,
    `created_at` datetime DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT current_timestamp()
        ON UPDATE current_timestamp(),
    `last_login_at` datetime DEFAULT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB
  DEFAULT CHARSET=latin1
  COLLATE=latin1_swedish_ci;

CREATE TABLE `admin_password_resets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` int(11) NOT NULL,
  `token_hash` char(64) NOT NULL,
  `requested_ip` varchar(45) DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `advertisements` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL,
  `ad_type` enum('adsense','html','image','script','text') NOT NULL DEFAULT 'html',
  `placement_key` varchar(80) NOT NULL,
  `content` longtext DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `target_url` varchar(500) DEFAULT NULL,
  `width` varchar(30) NOT NULL DEFAULT 'auto',
  `height` varchar(30) NOT NULL DEFAULT 'auto',
  `alignment` enum('left','center','right') NOT NULL DEFAULT 'center',
  `display_desktop` tinyint(1) NOT NULL DEFAULT 1,
  `display_mobile` tinyint(1) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `start_at` datetime DEFAULT NULL,
  `end_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `auth_popup_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `logo_icon` varchar(20) DEFAULT '',
  `logo_name` varchar(255) DEFAULT 'Royal Apps',
  `grad_start` varchar(20) DEFAULT '#1a5c34',
  `grad_end` varchar(20) DEFAULT '#2ecc71',
  `login_headline` varchar(255) DEFAULT 'Welcome Back!',
  `login_subline` text DEFAULT NULL,
  `login_btn_text` varchar(255) DEFAULT 'Login to Royal Apps',
  `forgot_show` tinyint(1) NOT NULL DEFAULT 1,
  `forgot_text` varchar(255) DEFAULT 'Forgot password?',
  `signup_headline` varchar(255) DEFAULT 'Join Royal Apps',
  `signup_subline` text DEFAULT NULL,
  `signup_btn_text` varchar(255) DEFAULT 'Create Free Account',
  `name_field` tinyint(1) NOT NULL DEFAULT 1,
  `headline_size` int(11) NOT NULL DEFAULT 22,
  `input_size` int(11) NOT NULL DEFAULT 13,
  `btn_size` int(11) NOT NULL DEFAULT 15,
  `popup_width` int(11) NOT NULL DEFAULT 440,
  `popup_radius` int(11) NOT NULL DEFAULT 24,
  `btn_color` varchar(20) DEFAULT '#27ae60',
  `btn_text_color` varchar(20) DEFAULT '#ffffff',
  `input_focus_color` varchar(20) DEFAULT '#27ae60',
  `label_color` varchar(20) DEFAULT '#27ae60',
  `popup_bg` varchar(20) DEFAULT '#ffffff',
  `overlay_color` varchar(20) DEFAULT '#0a140f',
  `divider_show` tinyint(1) NOT NULL DEFAULT 1,
  `divider_text` varchar(255) DEFAULT 'or continue with',
  `google_show` tinyint(1) NOT NULL DEFAULT 1,
  `facebook_show` tinyint(1) NOT NULL DEFAULT 0,
  `instagram_show` tinyint(1) NOT NULL DEFAULT 0,
  `google_text` varchar(100) DEFAULT 'Continue with Google',
  `facebook_text` varchar(100) DEFAULT 'Continue with Facebook',
  `instagram_text` varchar(100) DEFAULT 'Continue with Instagram',
  `close_backdrop` tinyint(1) NOT NULL DEFAULT 1,
  `close_esc` tinyint(1) NOT NULL DEFAULT 1,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `os_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `color` varchar(20) DEFAULT '#3498db',
  `description` mediumtext DEFAULT NULL,
  `software_count` int(11) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `os_id` (`os_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `coupons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(64) NOT NULL,
  `title` varchar(150) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `discount_type` enum('percent','fixed') NOT NULL DEFAULT 'percent',
  `discount_value` decimal(10,2) NOT NULL DEFAULT 0.00,
  `max_discount_amount` decimal(10,2) DEFAULT NULL,
  `min_order_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `usage_limit` int(11) DEFAULT NULL,
  `used_count` int(11) NOT NULL DEFAULT 0,
  `per_user_limit` int(11) DEFAULT NULL,
  `starts_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `coupon_redemptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `payment_id` int(11) NOT NULL,
  `coupon_code` varchar(64) NOT NULL,
  `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `coupon_id` (`coupon_id`),
  KEY `user_id` (`user_id`),
  KEY `payment_id` (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `email_history` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `audience` varchar(50) NOT NULL DEFAULT 'all',
  `specific_email` text DEFAULT NULL,
  `template_key` varchar(50) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `message` mediumtext DEFAULT NULL,
  `sender_name` varchar(150) DEFAULT NULL,
  `sender_email` varchar(190) DEFAULT NULL,
  `reply_to` varchar(190) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `smtp_used` tinyint(1) NOT NULL DEFAULT 0,
  `status` varchar(20) NOT NULL DEFAULT 'draft',
  `recipient_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `last_error` text DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `email_settings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sender_name` varchar(150) DEFAULT NULL,
  `sender_email` varchar(190) DEFAULT NULL,
  `reply_to` varchar(190) DEFAULT NULL,
  `smtp_host` varchar(255) DEFAULT NULL,
  `smtp_port` int(11) DEFAULT 587,
  `smtp_username` varchar(255) DEFAULT NULL,
  `smtp_password` varchar(255) DEFAULT NULL,
  `smtp_encryption` enum('tls','ssl','none') DEFAULT 'tls',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `essentials_box_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `essentials_box_id` int(11) NOT NULL,
  `software_id` int(11) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `essentials_box_id` (`essentials_box_id`),
  KEY `software_id` (`software_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `essentials_box_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `title` varchar(255) NOT NULL DEFAULT '⭐ Essentials',
  `subtitle` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `faq_pages` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `short_description` text DEFAULT NULL,
  `page_intro` longtext DEFAULT NULL,
  `is_published` tinyint(1) NOT NULL DEFAULT 0,
  `accent_color` varchar(20) NOT NULL DEFAULT '#27ae60',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `faq_page_items` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `faq_page_id` int(10) UNSIGNED NOT NULL,
  `question` longtext NOT NULL,
  `answer` longtext NOT NULL,
  `sort_order` int(11) 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`),
  KEY `faq_page_id` (`faq_page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `footersettings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `desktopshow` tinyint(1) NOT NULL DEFAULT 1,
  `mobileshow` tinyint(1) NOT NULL DEFAULT 1,
  `bg` varchar(20) NOT NULL DEFAULT '#101c14',
  `bordershow` tinyint(1) NOT NULL DEFAULT 1,
  `bordercolor` varchar(20) NOT NULL DEFAULT '#1f3427',
  `paddingy` int(11) NOT NULL DEFAULT 18,
  `logoShow` tinyint(1) NOT NULL DEFAULT 0,
  `logoSize` int(11) NOT NULL DEFAULT 18,
  `leftshow` tinyint(1) NOT NULL DEFAULT 1,
  `lefticon` varchar(50) NOT NULL DEFAULT '',
  `lefttext` varchar(100) NOT NULL DEFAULT 'Royal Apps',
  `lefticonsize` int(11) NOT NULL DEFAULT 18,
  `lefttextsize` int(11) NOT NULL DEFAULT 14,
  `leftcolor` varchar(20) NOT NULL DEFAULT '#ffffff',
  `centershow` tinyint(1) NOT NULL DEFAULT 1,
  `centerlayout` varchar(20) NOT NULL DEFAULT 'horizontal',
  `centergap` int(11) NOT NULL DEFAULT 22,
  `linksgap` int(11) NOT NULL DEFAULT 10,
  `centergroups` longtext DEFAULT NULL,
  `sepshow` tinyint(1) NOT NULL DEFAULT 1,
  `sepicon` varchar(20) NOT NULL DEFAULT '',
  `sepsize` int(11) NOT NULL DEFAULT 14,
  `sepcolor` varchar(20) NOT NULL DEFAULT '#27ae60',
  `rightshow` tinyint(1) NOT NULL DEFAULT 1,
  `rightheart` varchar(20) NOT NULL DEFAULT '♥',
  `righttext` varchar(100) NOT NULL DEFAULT 'ROYAL APPS',
  `rightheartcolor` varchar(20) NOT NULL DEFAULT '#ff4d6d',
  `righttextcolor` varchar(20) NOT NULL DEFAULT '#ffffff',
  `righttextsize` int(11) NOT NULL DEFAULT 14,
  `align` varchar(20) NOT NULL DEFAULT 'space-between',
  `mobilelayout` varchar(20) NOT NULL DEFAULT 'stack',
  `fontfamily` varchar(120) NOT NULL DEFAULT 'Inter, sans-serif',
  `basecolor` varchar(20) NOT NULL DEFAULT '#dfeee3',
  `copytext` varchar(200) NOT NULL DEFAULT '© 2026 Royal Apps. All Rights Reserved.',
  `copycolor` varchar(20) NOT NULL DEFAULT '#ffffff',
  `block3show` tinyint(1) NOT NULL DEFAULT 1,
  `block3layout` varchar(30) NOT NULL DEFAULT 'horizontal',
  `block3bg` varchar(20) NOT NULL DEFAULT '112018',
  `copyshow` tinyint(1) NOT NULL DEFAULT 1,
  `copysize` int(11) NOT NULL DEFAULT 13,
  `bottomlinksshow` tinyint(1) NOT NULL DEFAULT 1,
  `bottomlinkslayout` varchar(30) NOT NULL DEFAULT 'horizontal',
  `bottomlinksgap` int(11) NOT NULL DEFAULT 14,
  `bottomlinkscolor` varchar(20) NOT NULL DEFAULT 'dfeee3',
  `bottomlinkshover` varchar(20) NOT NULL DEFAULT '27ae60',
  `bottomlinksfont` varchar(120) NOT NULL DEFAULT 'Inter, sans-serif',
  `bottomlinkssize` int(11) NOT NULL DEFAULT 13,
  `bottomlinksjson` longtext DEFAULT NULL,
  `top_left_json` longtext DEFAULT NULL,
  `top_center_json` longtext DEFAULT NULL,
  `middle_strip_json` longtext DEFAULT NULL,
  `bottom_left_json` longtext DEFAULT NULL,
  `bottom_center_json` longtext DEFAULT NULL,
  `bottom_social_json` longtext DEFAULT NULL,
  `bottom_right_json` longtext DEFAULT NULL,
  `top_bg` varchar(20) DEFAULT NULL,
  `top_border_show` tinyint(1) NOT NULL DEFAULT 1,
  `top_border_color` varchar(20) DEFAULT NULL,
  `top_radius` int(11) NOT NULL DEFAULT 0,
  `top_padding_y` int(11) NOT NULL DEFAULT 34,
  `top_padding_x` int(11) NOT NULL DEFAULT 26,
  `top_left_show` tinyint(1) NOT NULL DEFAULT 1,
  `top_left_logo` varchar(255) DEFAULT NULL,
  `top_left_logo_width` int(11) NOT NULL DEFAULT 56,
  `top_left_title` varchar(255) NOT NULL DEFAULT 'Royal Apps',
  `top_left_desc` text DEFAULT NULL,
  `top_left_title_color` varchar(20) NOT NULL DEFAULT 'ffffff',
  `top_left_desc_color` varchar(20) NOT NULL DEFAULT 'b7c9bd',
  `top_left_title_size` int(11) NOT NULL DEFAULT 18,
  `top_left_desc_size` int(11) NOT NULL DEFAULT 13,
  `top_left_title_font` varchar(255) NOT NULL DEFAULT 'Inter, sans-serif',
  `top_left_desc_font` varchar(255) NOT NULL DEFAULT 'Inter, sans-serif',
  `top_center_show` tinyint(1) NOT NULL DEFAULT 1,
  `top_center_groups` longtext DEFAULT NULL,
  `top_right_show` tinyint(1) NOT NULL DEFAULT 1,
  `top_right_icon` varchar(50) DEFAULT NULL,
  `top_right_text` varchar(255) NOT NULL DEFAULT 'ROYAL APPS',
  `top_right_subtext` varchar(255) DEFAULT NULL,
  `top_right_icon_color` varchar(20) NOT NULL DEFAULT 'ff4d6d',
  `top_right_text_color` varchar(20) NOT NULL DEFAULT 'ffffff',
  `top_right_subtext_color` varchar(20) NOT NULL DEFAULT 'b7c9bd',
  `top_right_text_size` int(11) NOT NULL DEFAULT 16,
  `top_right_subtext_size` int(11) NOT NULL DEFAULT 12,
  `top_right_font` varchar(255) NOT NULL DEFAULT 'Inter, sans-serif',
  `top_right_style` varchar(50) NOT NULL DEFAULT 'badge',
  `middle_bg` varchar(20) DEFAULT NULL,
  `middle_border_show` tinyint(1) NOT NULL DEFAULT 1,
  `middle_border_color` varchar(20) DEFAULT NULL,
  `middle_radius` int(11) NOT NULL DEFAULT 16,
  `middle_padding_y` int(11) NOT NULL DEFAULT 12,
  `middle_padding_x` int(11) NOT NULL DEFAULT 0,
  `middle_image` varchar(255) DEFAULT NULL,
  `middle_image_alt` varchar(255) DEFAULT NULL,
  `middle_link` varchar(255) DEFAULT NULL,
  `middle_open_new_tab` tinyint(1) NOT NULL DEFAULT 0,
  `bottom_bg` varchar(20) DEFAULT NULL,
  `bottom_border_show` tinyint(1) NOT NULL DEFAULT 1,
  `bottom_border_color` varchar(20) DEFAULT NULL,
  `bottom_radius` int(11) NOT NULL DEFAULT 0,
  `bottom_padding_y` int(11) NOT NULL DEFAULT 18,
  `bottom_padding_x` int(11) NOT NULL DEFAULT 0,
  `bottom_left_show` tinyint(1) NOT NULL DEFAULT 1,
  `bottom_left_text` varchar(255) NOT NULL DEFAULT '© 2026 Royal Apps. All Rights Reserved.',
  `bottom_left_color` varchar(20) NOT NULL DEFAULT 'ffffff',
  `bottom_left_size` int(11) NOT NULL DEFAULT 13,
  `bottom_left_font` varchar(255) NOT NULL DEFAULT 'Inter, sans-serif',
  `bottom_center_show` tinyint(1) NOT NULL DEFAULT 1,
  `bottom_center_links` longtext DEFAULT NULL,
  `bottom_center_gap` int(11) NOT NULL DEFAULT 18,
  `bottom_center_color` varchar(20) NOT NULL DEFAULT 'dfeee3',
  `bottom_center_hover_color` varchar(20) NOT NULL DEFAULT 'ffffff',
  `bottom_center_size` int(11) NOT NULL DEFAULT 13,
  `bottom_center_font` varchar(255) NOT NULL DEFAULT 'Inter, sans-serif',
  `bottom_right_show` tinyint(1) NOT NULL DEFAULT 1,
  `bottom_right_socials` longtext DEFAULT NULL,
  `bottom_right_gap` int(11) NOT NULL DEFAULT 12,
  `bottom_right_icon_size` int(11) NOT NULL DEFAULT 18,
  `bottom_right_icon_color` varchar(20) NOT NULL DEFAULT 'ffffff',
  `bottom_right_hover_color` varchar(20) NOT NULL DEFAULT '27ae60',
  `top_show` tinyint(1) DEFAULT 1,
  `middle_show` tinyint(1) DEFAULT 1,
  `bottom_show` tinyint(1) DEFAULT 1,
  `block1show` int(1) DEFAULT 1,
  `block1layout` varchar(20) DEFAULT NULL,
  `block1bg` varchar(20) NOT NULL DEFAULT '13241a',
  `block2show` int(1) DEFAULT NULL,
  `block2layout` varchar(20) DEFAULT NULL,
  `block2bg` varchar(20) NOT NULL DEFAULT '173222',
  `leftdesc` text DEFAULT NULL,
  `leftdescsize` int(11) DEFAULT NULL,
  `leftdesccolor` varchar(20) DEFAULT NULL,
  `midleftshow` int(1) DEFAULT NULL,
  `midlefticon` varchar(255) NOT NULL DEFAULT '',
  `midlefttext` varchar(255) DEFAULT NULL,
  `midleftsubtext` varchar(255) NOT NULL DEFAULT '',
  `midcentershow` tinyint(1) NOT NULL DEFAULT 1,
  `midcentericon` varchar(255) NOT NULL DEFAULT '',
  `midcentertext` varchar(255) DEFAULT NULL,
  `midcentersubtext` varchar(255) NOT NULL DEFAULT '',
  `midrightshow` tinyint(1) NOT NULL DEFAULT 1,
  `midrighticon` varchar(255) NOT NULL DEFAULT '',
  `midrighttext` varchar(255) DEFAULT NULL,
  `midfourthshow` tinyint(1) NOT NULL DEFAULT 1,
  `midfourthicon` varchar(255) NOT NULL DEFAULT '',
  `midfourthtext` varchar(255) NOT NULL DEFAULT '',
  `midfourthsubtext` varchar(255) NOT NULL DEFAULT '',
  `midrightsubtext` varchar(255) NOT NULL DEFAULT '',
  `midiconcolor` varchar(20) NOT NULL DEFAULT '27ae60',
  `midiconbg` varchar(20) NOT NULL DEFAULT 'e9f8ee',
  `midiconbgopacity` varchar(10) NOT NULL DEFAULT '1',
  `midtextcolor` varchar(20) NOT NULL DEFAULT 'ffffff',
  `midtitlecolor` varchar(20) NOT NULL DEFAULT 'ffffff',
  `midsubtitlecolor` varchar(20) NOT NULL DEFAULT 'dfeee3',
  `midfont` varchar(120) NOT NULL DEFAULT 'Inter, sans-serif',
  `midsize` int(11) NOT NULL DEFAULT 14,
  `midtitlesize` int(11) NOT NULL DEFAULT 18,
  `midsubtitlesize` int(11) NOT NULL DEFAULT 14,
  `midgap` int(11) NOT NULL DEFAULT 16,
  `middividershow` tinyint(1) NOT NULL DEFAULT 1,
  `middividercolor` varchar(20) NOT NULL DEFAULT '2b4a37',
  `middividerheight` int(11) NOT NULL DEFAULT 52,
  `middividerwidth` int(11) NOT NULL DEFAULT 1,
  `midiconboxsize` int(11) NOT NULL DEFAULT 60,
  `midiconsize` int(11) NOT NULL DEFAULT 28,
  `miditemgap` int(11) NOT NULL DEFAULT 16,
  `midtextgap` int(11) NOT NULL DEFAULT 4,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `homepage_card_box_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `box_key` varchar(50) NOT NULL,
  `software_id` int(11) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `box_key` (`box_key`),
  KEY `software_id` (`software_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `homepage_card_box_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `box_key` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `title_color_mode` varchar(20) NOT NULL DEFAULT 'solid',
  `title_color_1` varchar(20) DEFAULT '#ffffff',
  `title_color_2` varchar(20) DEFAULT '#ffffff',
  `box_color_mode` varchar(20) NOT NULL DEFAULT 'gradient',
  `box_color_1` varchar(20) DEFAULT '#6d28d9',
  `box_color_2` varchar(20) DEFAULT '#2563eb',
  `button_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `button_text` varchar(100) DEFAULT 'Download',
  `button_color_mode` varchar(20) NOT NULL DEFAULT 'solid',
  `button_color_1` varchar(20) DEFAULT '#ffffff',
  `button_color_2` varchar(20) DEFAULT '#ffffff',
  `card_color_mode` varchar(20) NOT NULL DEFAULT 'solid',
  `card_color_1` varchar(20) DEFAULT '#ffffff',
  `card_color_2` varchar(20) DEFAULT '#f8fafc',
  `items_per_row` int(11) NOT NULL DEFAULT 2,
  `total_items` int(11) NOT NULL DEFAULT 6,
  `latest_mode` varchar(20) NOT NULL DEFAULT 'latest_auto',
  `latest_type` varchar(50) DEFAULT 'all',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `box_key` (`box_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `info_dropdown_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `icon` varchar(50) DEFAULT 'i',
  `iconSize` int(11) NOT NULL DEFAULT 20,
  `iconColor` varchar(20) DEFAULT '#ffffff',
  `iconHoverColor` varchar(20) DEFAULT '#ffffff',
  `iconActiveColor` varchar(20) DEFAULT '#27ae60',
  `btnSize` int(11) NOT NULL DEFAULT 38,
  `btnBg` varchar(20) DEFAULT '#ffffff',
  `btnHoverBg` varchar(20) DEFAULT '#ffffff',
  `btnActiveBg` varchar(20) DEFAULT '#ffffff',
  `btnBorder` varchar(20) DEFAULT '#ffffff',
  `headerShow` tinyint(1) NOT NULL DEFAULT 1,
  `headerTitle` varchar(255) DEFAULT 'Quick Links',
  `links` longtext DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `payment_id` int(11) NOT NULL,
  `invoice_no` varchar(100) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) NOT NULL DEFAULT 'INR',
  `status` varchar(20) NOT NULL DEFAULT 'paid',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `plan_id` (`plan_id`),
  KEY `payment_id` (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `navigation_menu_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `label` varchar(100) NOT NULL,
  `url` varchar(255) NOT NULL DEFAULT '#',
  `icon_url` varchar(500) DEFAULT '',
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `show_desktop` tinyint(1) NOT NULL DEFAULT 1,
  `show_mobile` tinyint(1) NOT NULL DEFAULT 1,
  `mobile_direct` tinyint(1) NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `navigation_menu_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `desktop_text_color` varchar(20) DEFAULT '#ffffff',
  `desktop_hover_text_color` varchar(20) DEFAULT '#ffffff',
  `desktop_hover_bg` varchar(20) DEFAULT '#ffffff',
  `desktop_hover_opacity` decimal(5,2) DEFAULT 0.18,
  `desktop_active_text_color` varchar(20) DEFAULT '#27ae60',
  `desktop_active_bg` varchar(20) DEFAULT '#ffffff',
  `desktop_icon_color` varchar(20) DEFAULT '#ffffff',
  `desktop_active_icon_color` varchar(20) DEFAULT '#27ae60',
  `mobile_bg` varchar(20) DEFAULT '#27ae60',
  `mobile_text` varchar(20) DEFAULT '#ffffff',
  `mobile_active_bg` varchar(20) DEFAULT '#ffffff',
  `mobile_active_text` varchar(20) DEFAULT '#27ae60',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `operating_systems` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `icon` varchar(50) DEFAULT '',
  `description` text DEFAULT NULL,
  `custom_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_resets` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `email` varchar(255) NOT NULL,
  `reset_token` text NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `software_id` int(11) DEFAULT NULL,
  `software_plan_id` int(11) DEFAULT NULL,
  `invoice_no` varchar(100) NOT NULL,
  `gateway` varchar(50) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(10) NOT NULL DEFAULT 'INR',
  `coupon_code` varchar(64) DEFAULT NULL,
  `coupon_discount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `subtotal_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `final_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `payment_ref` varchar(150) DEFAULT NULL,
  `gateway_session_id` varchar(255) DEFAULT NULL,
  `gateway_customer_id` varchar(255) DEFAULT NULL,
  `gateway_subscription_id` varchar(255) DEFAULT NULL,
  `gateway_status` varchar(100) DEFAULT NULL,
  `payment_type` varchar(50) NOT NULL DEFAULT 'subscription',
  `checkout_type` varchar(30) NOT NULL DEFAULT 'subscription',
  `billing_cycle` varchar(50) DEFAULT NULL,
  `return_url` text DEFAULT NULL,
  `gateway_payload` longtext DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'paid',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `paid_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `plan_id` (`plan_id`),
  KEY `software_id` (`software_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `payment_settings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `currency` varchar(10) NOT NULL DEFAULT 'INR',
  `currency_symbol` varchar(10) NOT NULL DEFAULT '₹',
  `mode` varchar(20) NOT NULL DEFAULT 'test',
  `tax_rate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `invoice_prefix` varchar(50) DEFAULT 'RA-INV-',
  `success_url` varchar(255) DEFAULT NULL,
  `cancel_url` varchar(255) DEFAULT NULL,
  `allow_coupon` tinyint(1) NOT NULL DEFAULT 1,
  `auto_activate` tinyint(1) NOT NULL DEFAULT 1,
  `manual_review` tinyint(1) NOT NULL DEFAULT 0,
  `admin_notes` text DEFAULT NULL,
  `razorpay_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `razorpay_key` varchar(255) DEFAULT NULL,
  `razorpay_secret` varchar(255) DEFAULT NULL,
  `razorpay_webhook_secret` varchar(255) DEFAULT NULL,
  `stripe_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `stripe_key` varchar(255) DEFAULT NULL,
  `stripe_secret` varchar(255) DEFAULT NULL,
  `stripe_webhook_secret` varchar(255) DEFAULT NULL,
  `paypal_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `paypal_key` varchar(255) DEFAULT NULL,
  `paypal_secret` varchar(255) DEFAULT NULL,
  `twocheckout_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `twocheckout_key` varchar(255) DEFAULT NULL,
  `twocheckout_secret` varchar(255) DEFAULT NULL,
  `twocheckout_merchant_code` varchar(255) DEFAULT NULL,
  `paystack_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `paystack_public_key` varchar(255) DEFAULT NULL,
  `paystack_secret_key` varchar(255) DEFAULT NULL,
  `paystack_webhook_secret` varchar(255) DEFAULT NULL,
  `flutterwave_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `flutterwave_public_key` varchar(255) DEFAULT NULL,
  `flutterwave_secret_key` varchar(255) DEFAULT NULL,
  `flutterwave_webhook_secret` varchar(255) DEFAULT NULL,
  `mollie_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `mollie_api_key` varchar(255) DEFAULT NULL,
  `mollie_webhook_secret` varchar(255) DEFAULT NULL,
  `mollie_extra` varchar(255) DEFAULT NULL,
  `paypal_webhook_id` varchar(255) DEFAULT NULL,
  `bank_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `bank_key` varchar(255) DEFAULT NULL,
  `bank_secret` text DEFAULT NULL,
  `bank_extra` varchar(255) DEFAULT NULL,
  `default_gateway` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `payment_webhook_events` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `gateway` varchar(50) NOT NULL,
  `event_id` varchar(255) NOT NULL,
  `event_type` varchar(255) DEFAULT NULL,
  `payment_id` int(11) DEFAULT NULL,
  `payload` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `gateway` (`gateway`),
  KEY `payment_id` (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `popular_categories_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visible` tinyint(1) NOT NULL DEFAULT 1,
  `icon` varchar(50) DEFAULT '',
  `title` varchar(255) DEFAULT 'Popular Categories',
  `subtitle` varchar(255) DEFAULT '',
  `categories_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`categories_json`)),
  `accent` varchar(20) NOT NULL DEFAULT '#27ae60',
  `card_bg` varchar(20) NOT NULL DEFAULT '#f9fbff',
  `bg` varchar(20) NOT NULL DEFAULT '#ffffff',
  `limit` int(11) NOT NULL DEFAULT 6,
  `columns_count` int(11) NOT NULL DEFAULT 3,
  `show_count` tinyint(1) NOT NULL DEFAULT 1,
  `show_icon` tinyint(1) NOT NULL DEFAULT 1,
  `count_label` varchar(100) DEFAULT 'Softwares',
  `card_style` varchar(50) DEFAULT 'solid',
  `bg_color` varchar(20) DEFAULT '#ffffff',
  `card_bg_color` varchar(20) DEFAULT '#f9fbff',
  `title_color` varchar(20) NOT NULL DEFAULT '#1a1a2e',
  `text_color` varchar(20) NOT NULL DEFAULT '#666666',
  `accent_color` varchar(20) DEFAULT '#27ae60',
  `categories` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`categories`)),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `popup_settings` (
  `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `settings_json` longtext NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) DEFAULT NULL,
  `setting_value` text DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `site_pages` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `page_type` enum('content','faq') NOT NULL DEFAULT 'content',
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `site_page_faqs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `page_id` int(10) UNSIGNED NOT NULL,
  `question` text NOT NULL,
  `answer` longtext NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` 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`),
  KEY `page_id` (`page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `site_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(191) NOT NULL,
  `setting_value` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `site_visitors` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(64) DEFAULT NULL,
  `country` varchar(120) DEFAULT NULL,
  `source` varchar(120) DEFAULT NULL,
  `page_url` varchar(255) DEFAULT NULL,
  `visited_at` timestamp NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `softwares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `developer_name` varchar(255) DEFAULT NULL,
  `category` varchar(120) DEFAULT '',
  `subcategory` varchar(255) DEFAULT NULL,
  `os` varchar(80) DEFAULT 'Windows',
  `version` varchar(80) DEFAULT '',
  `file_size` varchar(80) DEFAULT '',
  `license_type` varchar(100) DEFAULT NULL,
  `last_update_date` date DEFAULT NULL,
  `icon_path` varchar(255) DEFAULT NULL,
  `pricing_type` varchar(50) DEFAULT 'free',
  `price` decimal(10,2) DEFAULT 0.00,
  `old_price` decimal(10,2) DEFAULT NULL,
  `buy_now_link` varchar(500) DEFAULT NULL,
  `trial_info` varchar(255) DEFAULT NULL,
  `downloads` int(11) DEFAULT 0,
  `rating` decimal(3,2) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `overview_html` longtext DEFAULT NULL,
  `features_html` longtext DEFAULT NULL,
  `system_requirements_html` longtext DEFAULT NULL,
  `changelog_html` longtext DEFAULT NULL,
  `download_links` longtext DEFAULT NULL,
  `pricing_mode` varchar(50) DEFAULT NULL,
  `pricing_data` longtext DEFAULT NULL,
  `screenshots` longtext DEFAULT NULL,
  `faq_text` longtext DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `singleprices` longtext DEFAULT NULL,
  `subscriptionplans` longtext DEFAULT NULL,
  `subscription_plan_ids` longtext DEFAULT NULL,
  `pricetitle` varchar(255) DEFAULT NULL,
  `badgetext` varchar(255) DEFAULT NULL,
  `featurestext` longtext DEFAULT NULL,
  `billingperiod` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `slug` (`slug`),
  KEY `os` (`os`),
  KEY `is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `software_purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `softwareid` int(11) NOT NULL,
  `paymentid` int(11) NOT NULL,
  `pricingtype` varchar(50) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT 0.00,
  `status` varchar(50) DEFAULT 'paid',
  `createdat` datetime DEFAULT current_timestamp(),
  `updatedat` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `softwareid` (`softwareid`),
  KEY `paymentid` (`paymentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `software_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `software_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(120) NOT NULL,
  `user_email` varchar(190) DEFAULT NULL,
  `rating` tinyint(4) NOT NULL DEFAULT 5,
  `review_text` text NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'approved',
  `source` enum('user','admin') NOT NULL DEFAULT 'user',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `software_id` (`software_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `metric` varchar(50) DEFAULT NULL,
  `value` bigint(20) DEFAULT 0,
  `updated_at` timestamp NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `stats_bar_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated_text` varchar(255) DEFAULT 'Updated one minute ago',
  `strip_bg` varchar(20) DEFAULT '#f0faf5',
  `badge_bg` varchar(20) DEFAULT '#edfbf3',
  `badge_color` varchar(20) DEFAULT '#27ae60',
  `served_mode` varchar(20) DEFAULT 'auto',
  `served_label` varchar(100) DEFAULT 'Served downloads',
  `served_manual` bigint(20) DEFAULT 3528391816,
  `served_base` bigint(20) DEFAULT 3528391816,
  `served_inc` bigint(20) DEFAULT 1200,
  `served_start_date` date DEFAULT NULL,
  `today_mode` varchar(20) DEFAULT 'auto',
  `today_label` varchar(100) DEFAULT 'New items today',
  `today_manual` bigint(20) DEFAULT 600,
  `today_base` bigint(20) DEFAULT 600,
  `today_inc` bigint(20) DEFAULT 25,
  `today_start_date` date DEFAULT NULL,
  `total_mode` varchar(20) DEFAULT 'auto',
  `total_label` varchar(100) DEFAULT 'Items total',
  `total_manual` bigint(20) DEFAULT 1819102,
  `total_base` bigint(20) DEFAULT 1819102,
  `total_inc` bigint(20) DEFAULT 15,
  `total_start_date` date DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subcategories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `os_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(150) NOT NULL,
  `description` mediumtext DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `os_id` (`os_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subscription_plans` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `plan_name` varchar(150) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `old_price` decimal(10,2) DEFAULT NULL,
  `billing_period` varchar(20) NOT NULL DEFAULT 'monthly',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `free_trial` varchar(100) DEFAULT NULL,
  `badge_text` varchar(100) DEFAULT NULL,
  `features_text` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `subscription_settings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `default_badge` varchar(100) DEFAULT 'Most Popular',
  `show_savings` tinyint(1) NOT NULL DEFAULT 1,
  `show_trial` tinyint(1) NOT NULL DEFAULT 1,
  `admin_notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `support_chat_conversations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `last_message_at` datetime DEFAULT current_timestamp(),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `support_chat_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(11) NOT NULL,
  `sender_type` enum('user','admin') NOT NULL,
  `sender_id` int(11) DEFAULT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `conversation_id` (`conversation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `support_faqs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `faq_type` enum('pricing','general') NOT NULL DEFAULT 'pricing',
  `question` text NOT NULL,
  `answer` text NOT NULL,
  `sort_order` int(11) 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `support_tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT 'General',
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `message` text DEFAULT NULL,
  `status` enum('open','inprogress','closed') DEFAULT 'open',
  `admin_reply` text DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `replied_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `topdownloadsettings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visible` tinyint(1) NOT NULL DEFAULT 1,
  `title` varchar(255) NOT NULL DEFAULT 'Top Downloads',
  `selectedIds` longtext DEFAULT NULL,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `translations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `field_name` varchar(100) NOT NULL,
  `language_code` varchar(10) NOT NULL,
  `source_text` text NOT NULL,
  `translated_text` text NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `entity_type` (`entity_type`),
  KEY `entity_id` (`entity_id`),
  KEY `language_code` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `trending_download_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visible` tinyint(1) NOT NULL DEFAULT 1,
  `icon` varchar(50) DEFAULT '',
  `title` varchar(255) DEFAULT 'Trending Downloads',
  `titleCharLimit` int(11) NOT NULL DEFAULT 38,
  `titleSize` int(11) NOT NULL DEFAULT 15,
  `titleFont` varchar(100) DEFAULT 'Poppins, sans-serif',
  `titleWeight` varchar(10) DEFAULT '700',
  `titleColor` varchar(20) DEFAULT '#1a7a3c',
  `titleGrad` tinyint(1) NOT NULL DEFAULT 1,
  `titleGrad1` varchar(20) DEFAULT '#1a7a3c',
  `titleGrad2` varchar(20) DEFAULT '#27ae60',
  `titleGrad3` varchar(20) DEFAULT '#f5a623',
  `bg1` varchar(20) DEFAULT '#edfbf3',
  `bg2` varchar(20) DEFAULT '#f4fff8',
  `bg3` varchar(20) DEFAULT '#e6f7ed',
  `borderColor` varchar(20) DEFAULT '#27ae60',
  `borderWidth` int(11) NOT NULL DEFAULT 4,
  `ribShow` tinyint(1) NOT NULL DEFAULT 1,
  `rib1` varchar(20) DEFAULT '#27ae60',
  `rib2` varchar(20) DEFAULT '#2ecc71',
  `ribText` varchar(20) DEFAULT '#ffffff',
  `ribFontSize` int(11) NOT NULL DEFAULT 8,
  `ribFont` varchar(100) DEFAULT 'Inter, sans-serif',
  `ribWeight` varchar(10) DEFAULT '700',
  `ribPy` int(11) NOT NULL DEFAULT 3,
  `ribPx` int(11) NOT NULL DEFAULT 24,
  `ribTop` int(11) NOT NULL DEFAULT 6,
  `ribLeft` int(11) NOT NULL DEFAULT -18,
  `ribRight` int(11) DEFAULT NULL,
  `ribRotate` int(11) NOT NULL DEFAULT -35,
  `selectedIds` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`selectedIds`)),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `software_id` int(11) DEFAULT NULL,
  `original_filename` varchar(255) DEFAULT NULL,
  `uploaded_filename` varchar(255) DEFAULT NULL,
  `file_size` int(11) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `upload_date` timestamp NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `software_id` (`software_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `role` enum('user','premium','admin') DEFAULT NULL,
  `downloads` int(11) DEFAULT 0,
  `purchases` int(11) DEFAULT 0,
  `joined` date DEFAULT NULL,
  `promo_email` tinyint(1) DEFAULT 1,
  `status` enum('active','blocked','pending') DEFAULT 'active',
  `email_verified` tinyint(1) NOT NULL DEFAULT 0,
  `email_verification_token` varchar(100) DEFAULT NULL,
  `email_verification_sent_at` datetime DEFAULT NULL,
  `email_verified_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `password_hash` varchar(255) DEFAULT NULL,
  `auth_provider` varchar(50) DEFAULT NULL,
  `provider_user_id` varchar(191) DEFAULT NULL,
  `avatar_url` varchar(255) DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `subscription_plan` varchar(100) DEFAULT 'Premium',
  `subscription_renewal` varchar(50) DEFAULT NULL,
  `subscription_price` varchar(50) DEFAULT NULL,
  `authprovider` varchar(50) DEFAULT NULL,
  `provideruserid` varchar(191) DEFAULT NULL,
  `avatarurl` text DEFAULT NULL,
  `lastloginat` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `user_download_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `software_name` varchar(150) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `version` varchar(50) DEFAULT NULL,
  `source` enum('RoyalApps','External') NOT NULL DEFAULT 'RoyalApps',
  `source_url` varchar(255) DEFAULT NULL,
  `icon_bg` varchar(30) DEFAULT NULL,
  `icon_emoji` varchar(20) DEFAULT NULL,
  `product_icon` varchar(255) DEFAULT NULL,
  `downloaded_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `notification_type` varchar(30) DEFAULT 'system',
  `source` varchar(30) DEFAULT 'RoyalApps',
  `purchase_source` varchar(30) DEFAULT 'RoyalApps',
  `icon` varchar(20) DEFAULT NULL,
  `icon_bg` varchar(30) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `message` text DEFAULT NULL,
  `created_label` varchar(50) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_notification_preferences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `promo_email` tinyint(1) NOT NULL DEFAULT 1,
  `update_alerts` tinyint(1) NOT NULL DEFAULT 1,
  `weekly_newsletter` tinyint(1) NOT NULL DEFAULT 1,
  `purchase_confirmations` tinyint(1) NOT NULL DEFAULT 1,
  `security_alerts` tinyint(1) NOT NULL DEFAULT 1,
  `push_new_software` tinyint(1) NOT NULL DEFAULT 1,
  `push_wishlist_sale` tinyint(1) NOT NULL DEFAULT 1,
  `push_download_complete` tinyint(1) NOT NULL DEFAULT 0,
  `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 `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `country` varchar(100) DEFAULT 'India',
  `language` varchar(50) DEFAULT 'English',
  `bio` text DEFAULT NULL,
  `avatar` varchar(255) 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 `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_purchase_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `software_id` int(11) DEFAULT NULL,
  `subscription_plan_id` int(11) DEFAULT NULL,
  `software_name` varchar(150) NOT NULL,
  `plan_name` varchar(100) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `source` enum('RoyalApps','External') NOT NULL DEFAULT 'RoyalApps',
  `status` varchar(50) NOT NULL DEFAULT 'Active',
  `icon_bg` varchar(30) DEFAULT NULL,
  `icon_emoji` varchar(20) DEFAULT NULL,
  `purchased_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `software_id` (`software_id`),
  KEY `subscription_plan_id` (`subscription_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_security_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `two_factor_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `two_factor_method` enum('authenticator','sms') DEFAULT 'authenticator',
  `two_factor_secret` varchar(255) DEFAULT NULL,
  `two_factor_secret_pending` varchar(255) DEFAULT NULL,
  `two_factor_verified_at` datetime DEFAULT NULL,
  `two_factor_phone` varchar(30) DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_token` char(64) DEFAULT NULL,
  `device` varchar(100) DEFAULT NULL,
  `meta` varchar(255) DEFAULT NULL,
  `ipaddress` varchar(45) DEFAULT NULL,
  `useragent` varchar(500) DEFAULT NULL,
  `is_current` tinyint(1) NOT NULL DEFAULT 0,
  `last_seen` datetime NOT NULL DEFAULT current_timestamp(),
  `logged_out_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `session_token` (`session_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `software_id` int(11) DEFAULT NULL,
  `plan_id` int(11) NOT NULL,
  `payment_id` int(11) NOT NULL,
  `gateway` varchar(50) DEFAULT NULL,
  `billing_cycle` varchar(50) DEFAULT NULL,
  `subscription_ref` varchar(255) DEFAULT NULL,
  `cancel_at_period_end` tinyint(1) NOT NULL DEFAULT 0,
  `meta_json` longtext DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `plan_id` (`plan_id`),
  KEY `payment_id` (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_support_tickets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `subject` varchar(150) NOT NULL,
  `category` varchar(100) DEFAULT 'General',
  `priority` varchar(30) DEFAULT 'medium',
  `message` text NOT NULL,
  `adminreply` text DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'open',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_twofactor_recovery_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `codehash` varchar(255) NOT NULL,
  `isused` tinyint(1) NOT NULL DEFAULT 0,
  `usedat` datetime DEFAULT NULL,
  `createdat` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `user_wishlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `software_name` varchar(150) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `price` varchar(50) DEFAULT NULL,
  `old_price` varchar(50) DEFAULT NULL,
  `sale_badge` varchar(30) DEFAULT NULL,
  `type` enum('download','buy') NOT NULL DEFAULT 'download',
  `action_url` varchar(255) DEFAULT NULL,
  `icon_emoji` varchar(20) DEFAULT NULL,
  `product_icon` varchar(500) NOT NULL DEFAULT '',
  `card_style` varchar(30) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- NO ALTER TABLE statements needed
-- All PRIMARY KEYs and UNIQUE KEYs are defined inside CREATE TABLE
--