CREATE TABLE IF NOT EXISTS restaurants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  fat_score TINYINT DEFAULT 8,
  night_score TINYINT DEFAULT 8,
  priority_score TINYINT DEFAULT 8,
  sort_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS guestbook (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80),
  city VARCHAR(80),
  emoji VARCHAR(12),
  message TEXT NOT NULL,
  is_approved BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS poll_votes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  option_name VARCHAR(120) NOT NULL UNIQUE,
  vote_count INT DEFAULT 0
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  setting_value TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS flight_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  flight_number VARCHAR(32),
  segment_code VARCHAR(32),
  status_label VARCHAR(80),
  plane_x DECIMAL(6,2),
  plane_y DECIMAL(6,2),
  global_percent TINYINT DEFAULT 0,
  segment_percent TINYINT DEFAULT 0,
  altitude VARCHAR(40),
  speed VARCHAR(40),
  raw_json JSON,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

INSERT IGNORE INTO poll_votes (option_name, vote_count) VALUES
('Özçelik Aspava', 42),
('Cino Kokoreç', 36),
('Ciğerci Apo', 28),
('322 Hatay Döner', 31);

INSERT INTO restaurants (name, fat_score, night_score, priority_score, sort_order) VALUES
('322 Hatay Döner', 9, 8, 10, 1),
('Peçenek', 8, 7, 9, 2),
('Apiloğlu', 8, 7, 8, 3),
('Midyeci Yalçın', 7, 10, 8, 4),
('Örnek Pide Kebap', 8, 6, 8, 5),
('Kaliruha', 9, 7, 9, 6),
('Keko', 8, 8, 8, 7),
('Mutlu Kebap', 8, 7, 8, 8),
('Koç Çağ', 10, 8, 10, 9),
('Alaturka', 7, 7, 7, 10),
('Bizim Köfteci', 7, 8, 8, 11),
('Ciğerci Apo', 9, 9, 10, 12),
('Kanatçı Tarık', 8, 10, 9, 13),
('Ege İşkembecisi', 6, 10, 9, 14),
('Özçelik Aspava', 9, 10, 10, 15),
('Meşhur Konyalı', 8, 8, 8, 16),
('Cino Kokoreç', 10, 10, 10, 17);