-- LangRank initial schema
-- Run on a fresh MySQL database. Strip CREATE DATABASE / USE before importing on cPanel.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────────
-- users : people who own SaaS accounts
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(190) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `name` VARCHAR(120) DEFAULT NULL,
  `plan` ENUM('free','starter','pro') NOT NULL DEFAULT 'free',
  `monthly_word_quota` INT UNSIGNED NOT NULL DEFAULT 5000,
  `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 `users_email_uq` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- sites : a WP install owned by a user. API key auth uses these.
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sites` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `domain` VARCHAR(255) NOT NULL,
  `api_key` CHAR(48) NOT NULL,
  `default_lang` CHAR(5) NOT NULL DEFAULT 'en',
  `enabled_langs` JSON NOT NULL,
  `auto_translate` TINYINT(1) NOT NULL DEFAULT 1,
  `auto_redirect`  TINYINT(1) NOT NULL DEFAULT 0,
  `show_branding`  TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sites_api_key_uq` (`api_key`),
  KEY `sites_user_idx` (`user_id`),
  CONSTRAINT `sites_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- translations : centralized translation cache (the moat).
-- Same source_hash + target_lang is shared across ALL sites globally,
-- so every "Book now" string is translated once for everyone.
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `translations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `source_hash` CHAR(64) NOT NULL,
  `source_lang` CHAR(5) NOT NULL,
  `target_lang` CHAR(5) NOT NULL,
  `source_text` MEDIUMTEXT NOT NULL,
  `target_text` MEDIUMTEXT NOT NULL,
  `engine` VARCHAR(32) NOT NULL,
  `char_count` INT 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 `translations_global_uq` (`source_hash`, `source_lang`, `target_lang`),
  KEY `translations_lang_idx` (`source_lang`, `target_lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- site_overrides : per-site manual edits to a translation.
-- Falls back to translations table when no override exists.
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `site_overrides` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_id` BIGINT UNSIGNED NOT NULL,
  `source_hash` CHAR(64) NOT NULL,
  `source_lang` CHAR(5) NOT NULL,
  `target_lang` CHAR(5) NOT NULL,
  `source_text` MEDIUMTEXT NOT NULL,
  `target_text` MEDIUMTEXT NOT 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 `overrides_uq` (`site_id`, `source_hash`, `source_lang`, `target_lang`),
  CONSTRAINT `overrides_site_fk` FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- url_slugs : translated URL slugs per site (e.g., /our-barbers ↔ /fr/nos-barbiers)
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `url_slugs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_id` BIGINT UNSIGNED NOT NULL,
  `source_slug` VARCHAR(500) NOT NULL,
  `target_lang` CHAR(5) NOT NULL,
  `target_slug` VARCHAR(500) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug_uq` (`site_id`, `source_slug`(255), `target_lang`),
  CONSTRAINT `slug_site_fk` FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- usage_monthly : per-site monthly char/word counters for quotas
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `usage_monthly` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_id` BIGINT UNSIGNED NOT NULL,
  `period` CHAR(7) NOT NULL,
  `chars_translated` INT UNSIGNED NOT NULL DEFAULT 0,
  `words_translated` INT UNSIGNED NOT NULL DEFAULT 0,
  `requests` INT UNSIGNED NOT NULL DEFAULT 0,
  `cache_hits` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `usage_uq` (`site_id`, `period`),
  CONSTRAINT `usage_site_fk` FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────
-- sessions : simple DB-backed session store for the dashboard
-- ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sessions` (
  `token` CHAR(64) NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`token`),
  KEY `sessions_user_idx` (`user_id`),
  CONSTRAINT `sessions_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;