-- TikSnip CMS schema
-- Import this file via phpMyAdmin on cPanel

CREATE TABLE IF NOT EXISTS admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS languages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  native_name VARCHAR(100) NOT NULL DEFAULT '',
  rtl TINYINT(1) NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(255) NOT NULL UNIQUE,
  status ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
  author VARCHAR(255) NOT NULL DEFAULT '',
  preview_token VARCHAR(64) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_translations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  page_id INT UNSIGNED NOT NULL,
  locale VARCHAR(10) NOT NULL,
  title VARCHAR(500) NOT NULL DEFAULT '',
  content LONGTEXT NOT NULL,
  meta_title VARCHAR(500) NOT NULL DEFAULT '',
  meta_description TEXT NOT NULL,
  og_image VARCHAR(500) NOT NULL DEFAULT '',
  canonical_url VARCHAR(500) NOT NULL DEFAULT '',
  json_ld JSON NULL,
  noindex TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_page_locale (page_id, locale),
  CONSTRAINT fk_page_translations_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE,
  CONSTRAINT fk_page_translations_locale FOREIGN KEY (locale) REFERENCES languages(code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_faqs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  page_id INT UNSIGNED NOT NULL,
  locale VARCHAR(10) NOT NULL,
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_page_faqs_page FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE,
  CONSTRAINT fk_page_faqs_locale FOREIGN KEY (locale) REFERENCES languages(code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS navigation_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  locale VARCHAR(10) NOT NULL,
  label VARCHAR(255) NOT NULL,
  url VARCHAR(500) NOT NULL,
  location ENUM('header', 'footer') NOT NULL DEFAULT 'header',
  sort_order INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_navigation_locale FOREIGN KEY (locale) REFERENCES languages(code) ON DELETE CASCADE,
  INDEX idx_nav_locale_location (locale, location, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS footer_sections (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  locale VARCHAR(10) NOT NULL,
  title VARCHAR(255) NOT NULL DEFAULT '',
  content TEXT NOT NULL DEFAULT '',
  sort_order INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_footer_locale FOREIGN KEY (locale) REFERENCES languages(code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS footer_section_links (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  section_id INT UNSIGNED NOT NULL,
  label VARCHAR(255) NOT NULL,
  url VARCHAR(500) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_footer_link_section FOREIGN KEY (section_id) REFERENCES footer_sections(id) ON DELETE CASCADE,
  INDEX idx_footer_link_section (section_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS homepage_translations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  locale VARCHAR(10) NOT NULL UNIQUE,
  meta_title VARCHAR(500) NOT NULL DEFAULT '',
  meta_description TEXT NOT NULL,
  og_image VARCHAR(500) NOT NULL DEFAULT '',
  canonical_url VARCHAR(500) NOT NULL DEFAULT '',
  json_ld JSON NULL,
  noindex TINYINT(1) NOT NULL DEFAULT 0,
  content JSON NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_homepage_locale FOREIGN KEY (locale) REFERENCES languages(code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed languages
INSERT INTO languages (code, name, native_name, rtl, enabled, sort_order) VALUES
  ('en', 'English', 'English', 0, 1, 1),
  ('ur', 'Urdu', 'اردو', 1, 1, 2),
  ('ru', 'Russian', 'Русский', 0, 1, 3),
  ('he', 'Hebrew', 'עברית', 1, 1, 4),
  ('ar', 'Arabic', 'العربية', 1, 1, 5)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Default admin account: run /admin/setup.php?token=setup-once after import

-- Sample published page
INSERT INTO pages (slug, status, author, preview_token) VALUES
  ('about', 'published', 'Admin', SHA2(CONCAT('about', RAND()), 256))
ON DUPLICATE KEY UPDATE slug = slug;

SET @about_id = (SELECT id FROM pages WHERE slug = 'about' LIMIT 1);

INSERT INTO page_translations (page_id, locale, title, content, meta_title, meta_description, canonical_url) VALUES
  (@about_id, 'en', 'About TikSnip',
   '<h2>About TikSnip</h2><p>TikSnip is a free TikTok video downloader. Save videos without watermark quickly and easily.</p>',
   'About TikSnip | Free TikTok Downloader',
   'Learn about TikSnip, the free TikTok video downloader without watermark.',
   '/en/about/')
ON DUPLICATE KEY UPDATE title = VALUES(title);

INSERT INTO page_faqs (page_id, locale, question, answer, sort_order) VALUES
  (@about_id, 'en', 'Is TikSnip free?', 'Yes, TikSnip is completely free to use.', 1),
  (@about_id, 'en', 'Do I need an account?', 'No account is required.', 2);

INSERT INTO navigation_items (locale, label, url, location, sort_order) VALUES
  ('en', 'Home', '/en/', 'header', 1),
  ('en', 'About', '/en/about/', 'header', 2);

INSERT INTO footer_sections (locale, title, content, sort_order) VALUES
  ('en', 'Legal', '', 1),
  ('en', '', 'Copyright 2025-2027 TikSnip. All rights reserved.', 2);

SET @legal_section_id = (SELECT id FROM footer_sections WHERE locale = 'en' AND title = 'Legal' LIMIT 1);

INSERT INTO footer_section_links (section_id, label, url, sort_order) VALUES
  (@legal_section_id, 'Privacy Policy', '/en/privacy-policy/', 1),
  (@legal_section_id, 'Terms & Conditions', '/en/terms/', 2);