-- Big Boyz Body — CMS tables (applied via npm run db:migrate)

CREATE TABLE IF NOT EXISTS cms_settings (
  `key` VARCHAR(128) NOT NULL,
  value JSON NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_navigation (
  id CHAR(36) NOT NULL,
  location ENUM('main', 'footer', 'dashboard') NOT NULL,
  label VARCHAR(128) NOT NULL,
  href VARCHAR(512) NOT NULL,
  hint VARCHAR(256) NULL,
  position INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  parent_id CHAR(36) NULL,
  PRIMARY KEY (id),
  INDEX idx_nav_location_pos (location, position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_pages (
  id CHAR(36) NOT NULL,
  slug VARCHAR(256) NOT NULL,
  title VARCHAR(512) NOT NULL,
  template VARCHAR(64) NOT NULL DEFAULT 'default',
  status ENUM('draft', 'published') NOT NULL DEFAULT 'published',
  sort_order INT 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 uk_cms_pages_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_page_sections (
  id CHAR(36) NOT NULL,
  page_id CHAR(36) NOT NULL,
  section_type VARCHAR(64) NOT NULL,
  title VARCHAR(256) NULL,
  content JSON NOT NULL,
  position INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  layout VARCHAR(64) NULL,
  PRIMARY KEY (id),
  INDEX idx_sections_page (page_id, position),
  CONSTRAINT fk_sections_page FOREIGN KEY (page_id) REFERENCES cms_pages (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_media (
  id CHAR(36) NOT NULL,
  filename VARCHAR(512) NOT NULL,
  url VARCHAR(1024) NOT NULL,
  alt_text VARCHAR(512) NULL,
  mime_type VARCHAR(64) NOT NULL,
  width INT NULL,
  height INT NULL,
  size_bytes INT NOT NULL,
  focal_x DECIMAL(5, 4) NULL DEFAULT 0.5000,
  focal_y DECIMAL(5, 4) NULL DEFAULT 0.5000,
  variants JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_media_created (created_at),
  INDEX idx_media_filename (filename(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_seo (
  entity_type VARCHAR(64) NOT NULL,
  entity_id VARCHAR(256) NOT NULL,
  meta_title VARCHAR(512) NULL,
  meta_description TEXT NULL,
  og_image VARCHAR(1024) NULL,
  canonical_url VARCHAR(1024) NULL,
  robots VARCHAR(128) NULL DEFAULT 'index,follow',
  schema_json JSON NULL,
  keywords VARCHAR(512) NULL,
  PRIMARY KEY (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_faq (
  id CHAR(36) NOT NULL,
  question TEXT NOT NULL,
  answer TEXT NOT NULL,
  position INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  INDEX idx_faq_position (position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_testimonials (
  id CHAR(36) NOT NULL,
  name VARCHAR(256) NOT NULL,
  role VARCHAR(256) NOT NULL,
  quote TEXT NOT NULL,
  result VARCHAR(512) NOT NULL,
  image VARCHAR(1024) NOT NULL,
  media_id CHAR(36) NULL,
  position INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  INDEX idx_testimonials_position (position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_transformations (
  id CHAR(36) NOT NULL,
  title VARCHAR(256) NOT NULL,
  timeframe VARCHAR(128) NOT NULL,
  client_name VARCHAR(256) NULL,
  description TEXT NULL,
  collage_src VARCHAR(1024) NULL,
  before_src VARCHAR(1024) NULL,
  after_src VARCHAR(1024) NULL,
  position INT NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  INDEX idx_transformations_position (position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_products (
  id VARCHAR(64) NOT NULL,
  slug VARCHAR(256) NOT NULL,
  title VARCHAR(512) NOT NULL,
  description TEXT NOT NULL,
  price_inr INT NOT NULL,
  type ENUM('program', 'ebook', 'subscription', 'coaching') NOT NULL,
  image VARCHAR(1024) NOT NULL,
  features JSON NOT NULL,
  is_premium TINYINT(1) NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  position INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_cms_products_slug (slug),
  INDEX idx_products_position (position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_blog_posts (
  id CHAR(36) NOT NULL,
  slug VARCHAR(256) NOT NULL,
  title VARCHAR(512) NOT NULL,
  excerpt TEXT NULL,
  content LONGTEXT NOT NULL,
  cover_image VARCHAR(1024) NULL,
  category VARCHAR(128) NULL,
  tags JSON NULL,
  status ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
  published_at DATETIME NULL,
  author_email VARCHAR(255) 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 uk_cms_blog_slug (slug),
  INDEX idx_blog_status_published (status, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_redirects (
  id CHAR(36) NOT NULL,
  from_path VARCHAR(512) NOT NULL,
  to_path VARCHAR(512) NOT NULL,
  status_code SMALLINT NOT NULL DEFAULT 301,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uk_cms_redirects_from (from_path(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_content_versions (
  id CHAR(36) NOT NULL,
  entity_type VARCHAR(64) NOT NULL,
  entity_id VARCHAR(256) NOT NULL,
  snapshot JSON NOT NULL,
  created_by VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_versions_entity (entity_type, entity_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cms_content_blocks (
  id CHAR(36) NOT NULL,
  name VARCHAR(256) NOT NULL,
  block_type VARCHAR(64) NOT NULL,
  content JSON 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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;