-- PesoTax CMS — Initial Database Schema
-- Run via installer. All tables use InnoDB, utf8mb4.

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ── USERS ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}users` (
  `id`                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`                 VARCHAR(100) NOT NULL,
  `email`                VARCHAR(191) NOT NULL,
  `password`             VARCHAR(255) NOT NULL,
  `role`                 ENUM('super_admin','admin','editor','seo_manager','writer','marketing','developer','viewer') NOT NULL DEFAULT 'viewer',
  `avatar`               VARCHAR(512) DEFAULT NULL,
  `is_active`            TINYINT(1) NOT NULL DEFAULT 1,
  `two_factor_enabled`   TINYINT(1) NOT NULL DEFAULT 0,
  `two_factor_secret`    VARCHAR(64)  DEFAULT NULL,
  `email_verified_at`    DATETIME DEFAULT NULL,
  `last_login_at`        DATETIME DEFAULT NULL,
  `last_login_ip`        VARCHAR(45) DEFAULT NULL,
  `password_changed_at`  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 `uq_email` (`email`),
  KEY `idx_role` (`role`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SESSIONS ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}sessions` (
  `id`         VARCHAR(191) NOT NULL,
  `user_id`    INT UNSIGNED DEFAULT NULL,
  `ip_address` VARCHAR(45)  DEFAULT NULL,
  `user_agent` TEXT,
  `payload`    LONGTEXT NOT NULL,
  `last_active` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_last_active` (`last_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── REMEMBER TOKENS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}remember_tokens` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_token` (`token_hash`),
  KEY `idx_user` (`user_id`),
  CONSTRAINT `fk_rt_user` FOREIGN KEY (`user_id`) REFERENCES `{prefix}users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── LOGIN HISTORY ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}login_history` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED DEFAULT NULL,
  `email`      VARCHAR(191) DEFAULT NULL,
  `ip_address` VARCHAR(45)  NOT NULL,
  `user_agent` VARCHAR(512) DEFAULT NULL,
  `status`     ENUM('success','failed','locked','2fa') NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_ip` (`ip_address`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── LOGIN ATTEMPTS (Brute Force) ───────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}login_attempts` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `attempt_key`  VARCHAR(64) NOT NULL,
  `identifier`   VARCHAR(191) NOT NULL,
  `ip_address`   VARCHAR(45)  NOT NULL,
  `attempts`     SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  `last_attempt` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_key` (`attempt_key`),
  KEY `idx_ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── IP BLOCKLIST ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}ip_blocklist` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_address` VARCHAR(45) NOT NULL,
  `reason`     VARCHAR(255) DEFAULT NULL,
  `blocked_by` INT UNSIGNED DEFAULT NULL,
  `blocked_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ip` (`ip_address`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── IP WHITELIST ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}ip_whitelist` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_address` VARCHAR(45)  NOT NULL,
  `label`      VARCHAR(100) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AUDIT LOG ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}audit_log` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED DEFAULT NULL,
  `action`     VARCHAR(100) NOT NULL,
  `context`    JSON DEFAULT NULL,
  `ip_address` VARCHAR(45)  DEFAULT NULL,
  `user_agent` VARCHAR(512) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── API KEYS ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}api_keys` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     INT UNSIGNED NOT NULL,
  `name`        VARCHAR(100) NOT NULL,
  `key_hash`    VARCHAR(64) NOT NULL,
  `key_prefix`  VARCHAR(10) NOT NULL,
  `permissions` JSON DEFAULT NULL,
  `is_active`   TINYINT(1) NOT NULL DEFAULT 1,
  `usage_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_used_at` DATETIME DEFAULT NULL,
  `expires_at`  DATETIME DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hash` (`key_hash`),
  KEY `idx_user` (`user_id`),
  CONSTRAINT `fk_ak_user` FOREIGN KEY (`user_id`) REFERENCES `{prefix}users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SETTINGS ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}settings` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group`       VARCHAR(50)  NOT NULL DEFAULT 'general',
  `key`         VARCHAR(100) NOT NULL,
  `value`       LONGTEXT DEFAULT NULL,
  `type`        ENUM('string','int','bool','json','encrypted') NOT NULL DEFAULT 'string',
  `label`       VARCHAR(200) DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `is_public`   TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_group_key` (`group`,`key`),
  KEY `idx_group` (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ADS ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}ads` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`           VARCHAR(200) NOT NULL,
  `slot`           VARCHAR(100) NOT NULL,
  `type`           ENUM('html','adsense','ezoic','nitropay','medianet','mediavine','raptive','image','custom') NOT NULL DEFAULT 'html',
  `content`        LONGTEXT DEFAULT NULL,
  `image_url`      VARCHAR(512) DEFAULT NULL,
  `click_url`      VARCHAR(512) DEFAULT NULL,
  `is_active`      TINYINT(1) NOT NULL DEFAULT 1,
  `device_target`  SET('desktop','mobile','tablet') NOT NULL DEFAULT 'desktop,mobile,tablet',
  `countries`      JSON DEFAULT NULL,
  `priority`       TINYINT UNSIGNED NOT NULL DEFAULT 5,
  `lazy_load`      TINYINT(1) NOT NULL DEFAULT 1,
  `starts_at`      DATETIME DEFAULT NULL,
  `ends_at`        DATETIME DEFAULT NULL,
  `impressions`    BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `clicks`         BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `revenue`        DECIMAL(10,4) NOT NULL DEFAULT 0,
  `created_by`     INT UNSIGNED 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`),
  KEY `idx_slot` (`slot`),
  KEY `idx_active` (`is_active`),
  KEY `idx_priority` (`priority`),
  KEY `idx_schedule` (`starts_at`, `ends_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AD SLOTS (positions) ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}ad_slots` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `slug`        VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `pages`       JSON DEFAULT NULL,
  `is_active`   TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AD AB TESTS ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}ad_tests` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `ad_a_id`     INT UNSIGNED NOT NULL,
  `ad_b_id`     INT UNSIGNED NOT NULL,
  `split`       TINYINT UNSIGNED NOT NULL DEFAULT 50,
  `is_active`   TINYINT(1) NOT NULL DEFAULT 0,
  `winner_id`   INT UNSIGNED DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── BLOG POSTS ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}posts` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`           VARCHAR(512) NOT NULL,
  `slug`            VARCHAR(512) NOT NULL,
  `excerpt`         TEXT DEFAULT NULL,
  `content`         LONGTEXT DEFAULT NULL,
  `featured_image`  VARCHAR(512) DEFAULT NULL,
  `author_id`       INT UNSIGNED DEFAULT NULL,
  `category_id`     INT UNSIGNED DEFAULT NULL,
  `status`          ENUM('draft','published','scheduled','archived') NOT NULL DEFAULT 'draft',
  `published_at`    DATETIME DEFAULT NULL,
  `scheduled_at`    DATETIME DEFAULT NULL,
  `seo_title`       VARCHAR(512) DEFAULT NULL,
  `seo_description` VARCHAR(512) DEFAULT NULL,
  `seo_keywords`    VARCHAR(512) DEFAULT NULL,
  `canonical_url`   VARCHAR(512) DEFAULT NULL,
  `og_image`        VARCHAR(512) DEFAULT NULL,
  `schema_markup`   JSON DEFAULT NULL,
  `reading_time`    TINYINT UNSIGNED DEFAULT NULL,
  `view_count`      INT UNSIGNED NOT NULL DEFAULT 0,
  `is_featured`     TINYINT(1) NOT NULL DEFAULT 0,
  `allow_comments`  TINYINT(1) NOT NULL DEFAULT 1,
  `robots`          ENUM('index,follow','noindex,nofollow','noindex,follow','index,nofollow') NOT NULL DEFAULT 'index,follow',
  `revision_count`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `word_count`      INT UNSIGNED 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 `uq_slug` (`slug`(191)),
  KEY `idx_status` (`status`),
  KEY `idx_author` (`author_id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_published` (`published_at`),
  KEY `idx_featured` (`is_featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── POST REVISIONS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}post_revisions` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`     INT UNSIGNED NOT NULL,
  `title`       VARCHAR(512) NOT NULL,
  `content`     LONGTEXT NOT NULL,
  `saved_by`    INT UNSIGNED DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_post` (`post_id`),
  CONSTRAINT `fk_rev_post` FOREIGN KEY (`post_id`) REFERENCES `{prefix}posts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CATEGORIES ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}categories` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `slug`        VARCHAR(100) NOT NULL,
  `parent_id`   INT UNSIGNED DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `seo_title`   VARCHAR(255) DEFAULT NULL,
  `seo_desc`    VARCHAR(512) DEFAULT NULL,
  `sort_order`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── TAGS ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}tags` (
  `id`    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`  VARCHAR(100) NOT NULL,
  `slug`  VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{prefix}post_tags` (
  `post_id` INT UNSIGNED NOT NULL,
  `tag_id`  INT UNSIGNED NOT NULL,
  PRIMARY KEY (`post_id`, `tag_id`),
  CONSTRAINT `fk_pt_post` FOREIGN KEY (`post_id`) REFERENCES `{prefix}posts`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pt_tag`  FOREIGN KEY (`tag_id`)  REFERENCES `{prefix}tags`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── PAGES ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}pages` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`           VARCHAR(512) NOT NULL,
  `slug`            VARCHAR(512) NOT NULL,
  `content`         LONGTEXT DEFAULT NULL,
  `template`        VARCHAR(100) NOT NULL DEFAULT 'default',
  `parent_id`       INT UNSIGNED DEFAULT NULL,
  `status`          ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `published_at`    DATETIME DEFAULT NULL,
  `seo_title`       VARCHAR(512) DEFAULT NULL,
  `seo_description` VARCHAR(512) DEFAULT NULL,
  `og_image`        VARCHAR(512) DEFAULT NULL,
  `canonical_url`   VARCHAR(512) DEFAULT NULL,
  `robots`          ENUM('index,follow','noindex,nofollow','noindex,follow','index,nofollow') NOT NULL DEFAULT 'index,follow',
  `schema_markup`   JSON DEFAULT NULL,
  `sort_order`      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `no_ads`          TINYINT(1) NOT NULL DEFAULT 0,
  `created_by`      INT UNSIGNED 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 `uq_slug` (`slug`(191)),
  KEY `idx_status` (`status`),
  KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CALCULATORS ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}calculators` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `slug`        VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `type`        VARCHAR(50) NOT NULL,
  `config`      JSON DEFAULT NULL,
  `formula`     LONGTEXT DEFAULT NULL,
  `is_active`   TINYINT(1) NOT NULL DEFAULT 1,
  `usage_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order`  SMALLINT UNSIGNED 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 `uq_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── TAX RATES (Government Rates) ───────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}tax_rates` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency`         ENUM('BIR','SSS','PhilHealth','PagIBIG','GSIS','DOLE') NOT NULL,
  `name`           VARCHAR(100) NOT NULL,
  `rate_type`      ENUM('percentage','fixed','bracket','formula') NOT NULL,
  `rate_data`      JSON NOT NULL,
  `effective_from` DATE NOT NULL,
  `effective_to`   DATE DEFAULT NULL,
  `legal_basis`    VARCHAR(255) DEFAULT NULL,
  `notes`          TEXT DEFAULT NULL,
  `is_active`      TINYINT(1) NOT NULL DEFAULT 1,
  `updated_by`     INT UNSIGNED 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`),
  KEY `idx_agency` (`agency`),
  KEY `idx_active` (`is_active`),
  KEY `idx_effective` (`effective_from`, `effective_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── MEDIA ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}media` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `filename`    VARCHAR(255) NOT NULL,
  `original`    VARCHAR(255) NOT NULL,
  `path`        VARCHAR(512) NOT NULL,
  `url`         VARCHAR(512) NOT NULL,
  `type`        VARCHAR(100) NOT NULL,
  `size`        INT UNSIGNED NOT NULL,
  `width`       SMALLINT UNSIGNED DEFAULT NULL,
  `height`      SMALLINT UNSIGNED DEFAULT NULL,
  `alt`         VARCHAR(255) DEFAULT NULL,
  `title`       VARCHAR(255) DEFAULT NULL,
  `caption`     TEXT DEFAULT NULL,
  `webp_path`   VARCHAR(512) DEFAULT NULL,
  `uploaded_by` INT UNSIGNED DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_user` (`uploaded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── REDIRECTS ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}redirects` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_url`    VARCHAR(512) NOT NULL,
  `to_url`      VARCHAR(512) NOT NULL,
  `type`        SMALLINT UNSIGNED NOT NULL DEFAULT 301,
  `is_active`   TINYINT(1) NOT NULL DEFAULT 1,
  `hit_count`   INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_from` (`from_url`(191)),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── MENUS ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}menus` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(100) NOT NULL,
  `location`   VARCHAR(50) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_location` (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `{prefix}menu_items` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `menu_id`     INT UNSIGNED NOT NULL,
  `parent_id`   INT UNSIGNED DEFAULT NULL,
  `title`       VARCHAR(100) NOT NULL,
  `url`         VARCHAR(512) DEFAULT NULL,
  `icon`        VARCHAR(50)  DEFAULT NULL,
  `target`      VARCHAR(10)  NOT NULL DEFAULT '_self',
  `sort_order`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_menu` (`menu_id`),
  KEY `idx_parent` (`parent_id`),
  CONSTRAINT `fk_mi_menu` FOREIGN KEY (`menu_id`) REFERENCES `{prefix}menus`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SEO META ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}seo_meta` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `page_type`   VARCHAR(50) NOT NULL,
  `page_id`     INT UNSIGNED DEFAULT NULL,
  `url_pattern` VARCHAR(512) DEFAULT NULL,
  `title`       VARCHAR(512) DEFAULT NULL,
  `description` VARCHAR(512) DEFAULT NULL,
  `keywords`    VARCHAR(512) DEFAULT NULL,
  `og_title`    VARCHAR(512) DEFAULT NULL,
  `og_desc`     VARCHAR(512) DEFAULT NULL,
  `og_image`    VARCHAR(512) DEFAULT NULL,
  `tw_card`     VARCHAR(50)  DEFAULT 'summary_large_image',
  `robots`      VARCHAR(100) DEFAULT 'index,follow',
  `schema`      JSON DEFAULT NULL,
  `canonical`   VARCHAR(512) DEFAULT NULL,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_page` (`page_type`, `page_id`),
  KEY `idx_url` (`url_pattern`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SECURITY SETTINGS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}security_settings` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `feature`     VARCHAR(100) NOT NULL,
  `is_enabled`  TINYINT(1) NOT NULL DEFAULT 1,
  `config`      JSON DEFAULT NULL,
  `risk_level`  ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `last_checked` DATETIME DEFAULT NULL,
  `updated_by`  INT UNSIGNED DEFAULT NULL,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_feature` (`feature`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ANALYTICS DAILY ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}analytics_daily` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `date`           DATE NOT NULL,
  `page_url`       VARCHAR(512) NOT NULL,
  `page_title`     VARCHAR(512) DEFAULT NULL,
  `sessions`       INT UNSIGNED NOT NULL DEFAULT 0,
  `pageviews`      INT UNSIGNED NOT NULL DEFAULT 0,
  `bounce_rate`    DECIMAL(5,2) DEFAULT NULL,
  `avg_duration`   SMALLINT UNSIGNED DEFAULT NULL,
  `source`         VARCHAR(100) DEFAULT NULL,
  `medium`         VARCHAR(100) DEFAULT NULL,
  `country`        VARCHAR(50)  DEFAULT NULL,
  `device`         ENUM('desktop','mobile','tablet') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`date`),
  KEY `idx_url` (`page_url`(191)),
  KEY `idx_source` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CALCULATOR USAGE ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}calculator_usage` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `calculator_slug` VARCHAR(100) NOT NULL,
  `ip_hash`         VARCHAR(64)  DEFAULT NULL,
  `inputs`          JSON DEFAULT NULL,
  `session_id`      VARCHAR(100) DEFAULT NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_slug` (`calculator_slug`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── BACKUPS ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}backups` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `filename`    VARCHAR(255) NOT NULL,
  `type`        ENUM('full','database','files') NOT NULL,
  `size`        BIGINT UNSIGNED DEFAULT NULL,
  `status`      ENUM('running','completed','failed') NOT NULL DEFAULT 'running',
  `path`        VARCHAR(512) DEFAULT NULL,
  `notes`       TEXT DEFAULT NULL,
  `created_by`  INT UNSIGNED DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── NOTIFICATIONS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}notifications` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     INT UNSIGNED DEFAULT NULL,
  `type`        VARCHAR(50) NOT NULL,
  `title`       VARCHAR(255) NOT NULL,
  `message`     TEXT NOT NULL,
  `data`        JSON DEFAULT NULL,
  `severity`    ENUM('info','warning','error','critical') NOT NULL DEFAULT 'info',
  `is_read`     TINYINT(1) NOT NULL DEFAULT 0,
  `read_at`     DATETIME DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_read` (`is_read`),
  KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CONTACTS ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}contacts` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `email`       VARCHAR(191) NOT NULL,
  `subject`     VARCHAR(255) DEFAULT NULL,
  `message`     TEXT NOT NULL,
  `ip_address`  VARCHAR(45)  DEFAULT NULL,
  `status`      ENUM('new','read','replied','spam') NOT NULL DEFAULT 'new',
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SUBSCRIBERS ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}subscribers` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email`         VARCHAR(191) NOT NULL,
  `name`          VARCHAR(100) DEFAULT NULL,
  `status`        ENUM('pending','active','unsubscribed') NOT NULL DEFAULT 'pending',
  `confirm_token` VARCHAR(64) DEFAULT NULL,
  `subscribed_at` DATETIME DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CRON JOBS ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}cron_jobs` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(100) NOT NULL,
  `command`      VARCHAR(255) NOT NULL,
  `schedule`     VARCHAR(100) NOT NULL,
  `is_active`    TINYINT(1) NOT NULL DEFAULT 1,
  `last_run_at`  DATETIME DEFAULT NULL,
  `next_run_at`  DATETIME DEFAULT NULL,
  `last_status`  ENUM('success','failed','running') DEFAULT NULL,
  `last_output`  TEXT DEFAULT NULL,
  `run_count`    INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FEATURE FLAGS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}feature_flags` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `slug`        VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `is_enabled`  TINYINT(1) NOT NULL DEFAULT 1,
  `updated_by`  INT UNSIGNED DEFAULT NULL,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SEARCH TERMS ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}search_terms` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `term`       VARCHAR(255) NOT NULL,
  `source`     VARCHAR(50)  NOT NULL DEFAULT 'organic',
  `count`      INT UNSIGNED NOT NULL DEFAULT 1,
  `date`       DATE NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_term` (`term`(100)),
  KEY `idx_date` (`date`),
  KEY `idx_source` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SECURITY LOG ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}security_log` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_type`  VARCHAR(100) NOT NULL,
  `severity`    ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `ip_address`  VARCHAR(45)  NOT NULL,
  `user_agent`  VARCHAR(512) DEFAULT NULL,
  `details`     JSON DEFAULT NULL,
  `is_blocked`  TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`event_type`),
  KEY `idx_ip` (`ip_address`),
  KEY `idx_severity` (`severity`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;

-- ── TWO-FACTOR BACKUP CODES ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `{prefix}two_factor_backup_codes` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED NOT NULL,
  `code_hash`  VARCHAR(64) NOT NULL,
  `is_used`    TINYINT(1) NOT NULL DEFAULT 0,
  `used_at`    DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_used` (`is_used`),
  CONSTRAINT `fk_tfbc_user` FOREIGN KEY (`user_id`) REFERENCES `{prefix}users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;