CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
  `key` VARCHAR(120) NOT NULL,
  `value` TEXT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(120) NOT NULL,
  title_ar VARCHAR(190) NOT NULL,
  title_en VARCHAR(190) NOT NULL,
  content_ar MEDIUMTEXT NOT NULL,
  content_en MEDIUMTEXT NOT NULL,
  meta_title_ar VARCHAR(190) NULL,
  meta_title_en VARCHAR(190) NULL,
  meta_description_ar VARCHAR(300) NULL,
  meta_description_en VARCHAR(300) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_pages_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE services (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(120) NOT NULL,
  cover_image VARCHAR(255) NULL,
  title_ar VARCHAR(190) NOT NULL,
  title_en VARCHAR(190) NOT NULL,
  excerpt_ar VARCHAR(500) NULL,
  excerpt_en VARCHAR(500) NULL,
  content_ar MEDIUMTEXT NULL,
  content_en MEDIUMTEXT NULL,
  meta_title_ar VARCHAR(190) NULL,
  meta_title_en VARCHAR(190) NULL,
  meta_description_ar VARCHAR(300) NULL,
  meta_description_en VARCHAR(300) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_services_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE portfolio_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug VARCHAR(120) NOT NULL,
  cover_image VARCHAR(255) NULL,
  title_ar VARCHAR(190) NOT NULL,
  title_en VARCHAR(190) NOT NULL,
  description_ar VARCHAR(1200) NULL,
  description_en VARCHAR(1200) NULL,
  client VARCHAR(190) NULL,
  year VARCHAR(20) NULL,
  category VARCHAR(120) NULL,
  project_url VARCHAR(300) NULL,
  meta_title_ar VARCHAR(190) NULL,
  meta_title_en VARCHAR(190) NULL,
  meta_description_ar VARCHAR(300) NULL,
  meta_description_en VARCHAR(300) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_portfolio_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contact_messages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(60) NULL,
  subject VARCHAR(160) NOT NULL,
  message TEXT NOT NULL,
  ip VARCHAR(64) NOT NULL,
  user_agent VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_contact_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE service_requests (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(60) NULL,
  service_slug VARCHAR(120) NOT NULL,
  budget VARCHAR(120) NULL,
  details TEXT NOT NULL,
  ip VARCHAR(64) NOT NULL,
  user_agent VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_request_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;