-- MySQL şeması: kategoriler, içerikler, sponsorlar ve dosya yüklemeleri

-- Kategoriler (menu/submenu yapısı için self-referential)
CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NOT NULL,
  slug VARCHAR(160) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NOT NULL UNIQUE,
  note TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  is_hidden TINYINT(1) NOT NULL DEFAULT 0,
  is_admin_only TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Ayarlar (anahtar-değer)
CREATE TABLE IF NOT EXISTS settings (
  name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci PRIMARY KEY,
  value TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Sponsorlar (üstte 2x2 ızgara)
CREATE TABLE IF NOT EXISTS sponsors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NOT NULL,
  link_url VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  image_url VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  image_path VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  note TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- İçerikler (ikon+isim+link+not, opsiyonel kategoriye bağlı)
CREATE TABLE IF NOT EXISTS contents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NOT NULL,
  link_url VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  open_in_new_tab TINYINT(1) NOT NULL DEFAULT 0,
  view_mode TINYINT(1) NOT NULL DEFAULT 0,
  card_size TINYINT(1) NOT NULL DEFAULT 0,
  html_content MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  image_url VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  image_path VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  note TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  is_hidden TINYINT(1) NOT NULL DEFAULT 0,
  is_admin_only TINYINT(1) NOT NULL DEFAULT 0,
  is_sponsor TINYINT(1) NOT NULL DEFAULT 0,
  sponsor_show_name TINYINT(1) NOT NULL DEFAULT 1,
  sponsor_big_image_path VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  sponsor_small_image_path VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  sponsor_big_named_image_path VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_contents_parent FOREIGN KEY (parent_id) REFERENCES contents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Alt menü ögeleri (max 5 kayıt kullanılacak - ilk ikon sabit: Ana Sayfa)
CREATE TABLE IF NOT EXISTS bottom_menu_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NOT NULL,
  content_id INT UNSIGNED NULL,
  is_home TINYINT(1) NOT NULL DEFAULT 0,
  icon_color VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  image_url VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  image_path VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_bottom_menu_content FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_turkish_ci;

-- Ayarlar (tema renkleri ve seçenekler)
CREATE TABLE IF NOT EXISTS settings (
  name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci PRIMARY KEY,
  value TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_turkish_ci;

-- Örnek veriler (opsiyonel)
INSERT INTO categories (parent_id, name, slug, note, sort_order) VALUES
  (NULL, 'Ana Menü', 'ana-menu', NULL, 1)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Kullanıcılar
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NULL,
  email VARCHAR(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NULL,
  last_name VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  country VARCHAR(100) NULL,
  profile_image_path VARCHAR(500) NULL,
  hometown VARCHAR(100) NULL,
  vehicle_brand VARCHAR(100) NULL,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  is_superadmin TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  theme_mode TINYINT(1) NOT NULL DEFAULT 0,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- SılaTakip ek tabloları
CREATE TABLE IF NOT EXISTS st_locations (
  user_id INT UNSIGNED NOT NULL,
  lat DOUBLE NOT NULL,
  lng DOUBLE NOT NULL,
  timestamp INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id),
  INDEX idx_time (timestamp),
  CONSTRAINT fk_stloc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS st_messages (
  msg_id VARCHAR(60) PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  message TEXT NOT NULL,
  timestamp INT UNSIGNED NOT NULL,
  is_deleted TINYINT(1) NOT NULL DEFAULT 0,
  INDEX idx_user_time (user_id, timestamp),
  CONSTRAINT fk_stmsg_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS st_mentions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mentioned_user_id INT UNSIGNED NOT NULL,
  from_user_id INT UNSIGNED NOT NULL,
  from_username VARCHAR(100) NULL,
  message TEXT NOT NULL,
  msg_id VARCHAR(60) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_mentioned (mentioned_user_id),
  CONSTRAINT fk_stm_user1 FOREIGN KEY (mentioned_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_stm_user2 FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS st_typing_status (
  user_id INT UNSIGNED PRIMARY KEY,
  is_typing TINYINT(1) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sttype_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS st_banned_ips (
  ip_address VARCHAR(64) PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Admin tercihleri: konum görünürlüğü
CREATE TABLE IF NOT EXISTS st_admin_prefs (
  user_id INT UNSIGNED PRIMARY KEY,
  show_location TINYINT(1) NOT NULL DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_stap_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;