-- =========================================================
--  Proje Yönetim - Veritabanı Şeması (MySQL 8 / MariaDB 10.6+, InnoDB)
-- =========================================================
CREATE DATABASE IF NOT EXISTS proje_yonetim
  CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci;
USE proje_yonetim;

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS proje_detay;
DROP TABLE IF EXISTS projeler;
DROP TABLE IF EXISTS proje_extra;
DROP TABLE IF EXISTS proje_kimlik;
SET FOREIGN_KEY_CHECKS = 1;

-- 1) PROJE KİMLİK
CREATE TABLE proje_kimlik (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  isim        VARCHAR(191) NOT NULL,
  sira        INT UNSIGNED 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),
  KEY idx_kimlik_sira (sira)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- 2) PROJE EXTRA / VARIANT  (proje_kimlik'e bağlı)
CREATE TABLE proje_extra (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  kimlik_id   INT UNSIGNED NOT NULL,
  isim        VARCHAR(191) NOT NULL,
  sira        INT UNSIGNED 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),
  KEY idx_extra_kimlik (kimlik_id, sira),
  CONSTRAINT fk_extra_kimlik FOREIGN KEY (kimlik_id)
    REFERENCES proje_kimlik (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- 3) PROJELER  (ana proje bilgileri)
CREATE TABLE projeler (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  proje_ismi        VARCHAR(255) NOT NULL,
  baslangic_tarihi  DATE NULL,
  teslim_tarihi     DATE NULL,
  konum             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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- 3b) PROJE DETAY  (projenin kimlik / extra değerleri — projeler tablosunun alt tablosu)
--     Her proje + kimlik için tek satır.
--     Kimliğin extra seçenekleri varsa extra_id dolu, yoksa serbest metin "deger" dolu.
CREATE TABLE proje_detay (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  proje_id   INT UNSIGNED NOT NULL,
  kimlik_id  INT UNSIGNED NOT NULL,
  extra_id   INT UNSIGNED NULL,
  deger      VARCHAR(255) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_proje_kimlik (proje_id, kimlik_id),
  KEY idx_detay_kimlik (kimlik_id),
  KEY idx_detay_extra (extra_id),
  CONSTRAINT fk_detay_proje  FOREIGN KEY (proje_id)  REFERENCES projeler (id)     ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_detay_kimlik FOREIGN KEY (kimlik_id) REFERENCES proje_kimlik (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_detay_extra  FOREIGN KEY (extra_id)  REFERENCES proje_extra (id)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;