CREATE DATABASE IF NOT EXISTS novabuilder CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE novabuilder;
CREATE TABLE IF NOT EXISTS users(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password VARCHAR(255) NOT NULL,role ENUM('admin','editor','author','user') NOT NULL DEFAULT 'user',status ENUM('active','blocked') NOT NULL DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL);
CREATE TABLE IF NOT EXISTS auth_login_attempts(identifier CHAR(64) PRIMARY KEY,attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,last_attempt DATETIME NOT NULL,locked_until DATETIME NULL);
CREATE TABLE IF NOT EXISTS pages(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,slug VARCHAR(190) NOT NULL UNIQUE,content LONGTEXT NULL,builder_json LONGTEXT NULL,status ENUM('draft','published') NOT NULL DEFAULT 'draft',seo_title VARCHAR(190) NULL,seo_description VARCHAR(320) NULL,seo_keywords VARCHAR(500) NULL,og_image VARCHAR(255) NULL,language VARCHAR(12) NOT NULL DEFAULT 'fa',created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL);
CREATE TABLE IF NOT EXISTS media(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NULL,file_name VARCHAR(255) NOT NULL,file_path VARCHAR(500) NOT NULL,mime_type VARCHAR(120) NULL,file_size INT UNSIGNED DEFAULT 0,alt_text VARCHAR(255) NULL,created_at DATETIME NOT NULL,INDEX(user_id));
CREATE TABLE IF NOT EXISTS menus(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,location VARCHAR(80) NOT NULL DEFAULT 'header',items_json LONGTEXT NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,UNIQUE KEY uq_menu_name_location(name,location));
CREATE TABLE IF NOT EXISTS forms(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,slug VARCHAR(160) NOT NULL UNIQUE,fields_json LONGTEXT NOT NULL,success_message VARCHAR(255) NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL);
CREATE TABLE IF NOT EXISTS form_submissions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,form_id INT UNSIGNED NOT NULL,payload_json LONGTEXT NOT NULL,ip_address VARCHAR(64) NULL,created_at DATETIME NOT NULL,INDEX(form_id));
CREATE TABLE IF NOT EXISTS themes(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,slug VARCHAR(120) NOT NULL UNIQUE,is_active TINYINT(1) NOT NULL DEFAULT 0,settings_json LONGTEXT NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL);
CREATE TABLE IF NOT EXISTS settings(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,`key` VARCHAR(190) NOT NULL UNIQUE,value LONGTEXT NULL,autoload TINYINT(1) NOT NULL DEFAULT 1,updated_at DATETIME NOT NULL);
CREATE TABLE IF NOT EXISTS languages(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(12) NOT NULL UNIQUE,name VARCHAR(80) NOT NULL,direction ENUM('rtl','ltr') NOT NULL DEFAULT 'ltr',is_default TINYINT(1) NOT NULL DEFAULT 0,is_active TINYINT(1) NOT NULL DEFAULT 1);
CREATE TABLE IF NOT EXISTS domains(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,domain VARCHAR(190) NOT NULL UNIQUE,is_primary TINYINT(1) NOT NULL DEFAULT 0,status ENUM('pending','active','disabled') NOT NULL DEFAULT 'pending',ssl_enabled TINYINT(1) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL);

CREATE TABLE IF NOT EXISTS blog_categories(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,slug VARCHAR(120) NOT NULL UNIQUE);
CREATE TABLE IF NOT EXISTS blog_posts(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,slug VARCHAR(190) NOT NULL UNIQUE,excerpt TEXT,content LONGTEXT,featured_image VARCHAR(255),category_id INT UNSIGNED NULL,status ENUM('draft','published') DEFAULT 'draft',seo_title VARCHAR(190),seo_description VARCHAR(320),created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL);

-- Bilingual editable content storage (Persian remains the source language; English overlays are editable in CMS).
CREATE TABLE IF NOT EXISTS page_translations(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_id INT UNSIGNED NOT NULL,
    language VARCHAR(12) NOT NULL,
    title VARCHAR(190) NOT NULL,
    content LONGTEXT NULL,
    builder_json LONGTEXT NULL,
    status ENUM('draft','published') NOT NULL DEFAULT 'draft',
    seo_title VARCHAR(190) NULL,
    seo_description VARCHAR(320) NULL,
    seo_keywords VARCHAR(500) NULL,
    og_image VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_page_translation(page_id,language),
    INDEX idx_page_translation_language(language)
);
CREATE TABLE IF NOT EXISTS blog_post_translations(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    language VARCHAR(12) NOT NULL,
    title VARCHAR(190) NOT NULL,
    excerpt TEXT NULL,
    content LONGTEXT NULL,
    status ENUM('draft','published') NOT NULL DEFAULT 'draft',
    seo_title VARCHAR(190) NULL,
    seo_description VARCHAR(320) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_blog_post_translation(post_id,language),
    INDEX idx_blog_post_translation_language(language)
);
CREATE TABLE IF NOT EXISTS blog_category_translations(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    language VARCHAR(12) NOT NULL,
    name VARCHAR(120) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_blog_category_translation(category_id,language)
);
CREATE TABLE IF NOT EXISTS menu_translations(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_id INT UNSIGNED NOT NULL,
    language VARCHAR(12) NOT NULL,
    items_json LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_menu_translation(menu_id,language)
);