SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','editor') NOT NULL DEFAULT 'admin',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  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_unicode_ci;

CREATE TABLE IF NOT EXISTS site_settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value MEDIUMTEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS home_sections (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  section_key VARCHAR(80) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL DEFAULT '',
  eyebrow VARCHAR(255) NOT NULL DEFAULT '',
  body TEXT NULL,
  image_path VARCHAR(255) NULL,
  button_text VARCHAR(100) NULL,
  button_link VARCHAR(255) NULL,
  is_visible TINYINT(1) NOT NULL DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS projects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  summary TEXT NULL,
  body MEDIUMTEXT NULL,
  location VARCHAR(180) NULL,
  project_year VARCHAR(20) NULL,
  project_status ENUM('ongoing','completed','upcoming') NOT NULL DEFAULT 'ongoing',
  category VARCHAR(100) NULL,
  technical_details TEXT NULL,
  cover_image VARCHAR(255) NULL,
  map_url VARCHAR(500) NULL,
  brochure_url VARCHAR(500) NULL,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  seo_title VARCHAR(255) NULL,
  seo_description VARCHAR(320) NULL,
  og_image VARCHAR(255) NULL,
  published_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_unicode_ci;

CREATE TABLE IF NOT EXISTS project_images (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id BIGINT UNSIGNED NOT NULL,
  image_path VARCHAR(255) NOT NULL,
  alt_text VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_project_images_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(50) NULL,
  subject VARCHAR(200) NULL,
  message TEXT NOT NULL,
  consent TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('new','read','archived','spam') NOT NULL DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  file_path VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size INT UNSIGNED NOT NULL,
  alt_text VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(150) NOT NULL,
  target_type VARCHAR(100) NULL,
  target_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO site_settings (setting_key, setting_value) VALUES
('company_name', 'SAMRİ YAPI'),
('company_tagline', 'Güvenle yükselir'),
('phone', '+90 212 000 00 00'),
('whatsapp', '+90 532 000 00 00'),
('email', 'info@samriyapi.com'),
('address', 'İstanbul, Türkiye'),
('working_hours', 'Pazartesi - Cumartesi: 09.00 - 18.00'),
('instagram', ''),
('linkedin', ''),
('hero_image', '/assets/images/samri-hero.png'),
('hero_eyebrow', 'GÜVENLE YÜKSELİR'),
('hero_title', 'SAMRİ YAPI İLE<br>GELECEĞİ İNŞA EDİYORUZ'),
('hero_text', 'Kaliteli malzeme, güçlü mühendislik ve zamanında teslim anlayışıyla hayallerinizi gerçeğe dönüştürüyoruz.'),
('hero_button_text', 'PROJELERİMİZİ İNCELE'),
('hero_button_link', '/projeler'),
('stat_1_value', '20+'), ('stat_1_label', 'YILLIK DENEYİM'),
('stat_2_value', '48'), ('stat_2_label', 'TAMAMLANAN PROJE'),
('stat_3_value', '12'), ('stat_3_label', 'DEVAM EDEN PROJE'),
('stat_4_value', '%100'), ('stat_4_label', 'MEMNUNİYET ODAKLI')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

INSERT INTO home_sections (section_key, title, eyebrow, body, image_path, button_text, button_link, is_visible) VALUES
('about', 'Yaşam alanlarından<br>kalıcı değerlere.', 'SAMRİ YAPI', 'Her projeyi sadece bir yapı değil, hayatın en değerli anlarına ev sahipliği yapan bir alan olarak görüyoruz. Tasarım, mühendislik ve güveni aynı çizgide buluşturuyoruz.', '/assets/images/samri-hero.png', 'BİZİ TANIYIN', '/hakkimizda', 1),
('services', 'Her aşamada<br>titiz bir yaklaşım.', 'HİZMETLERİMİZ', 'Arsa geliştirmeden anahtar teslim uygulamaya kadar, nitelikli yaşam alanları üretiyoruz.', '', 'HİZMETLERİ KEŞFEDİN', '/hizmetler', 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);