-- ATC Elektronik - Veritabanı Şeması (Türkçe)
-- MySQL 8+


CREATE TABLE IF NOT EXISTS roller (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rol_adi VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS kullanicilar (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad_soyad VARCHAR(120) NOT NULL,
  rol_id INT UNSIGNED NOT NULL,
  kullanici_adi VARCHAR(60) NOT NULL UNIQUE,
  sifre VARCHAR(255) NOT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  prim_orani DECIMAL(5,2) DEFAULT 0.00,
  tarih TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_kullanicilar_rol FOREIGN KEY (rol_id) REFERENCES roller(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS personel_evraklar (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kullanici_id INT UNSIGNED NOT NULL,
  evrak_turu ENUM('kimlik_on','kimlik_arka','vergi_levhasi','imza_sirkusu') NOT NULL,
  dosya_adi VARCHAR(200) NOT NULL,
  dosya_yolu VARCHAR(300) NOT NULL,
  tarih TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_evrak_kullanici FOREIGN KEY (kullanici_id) REFERENCES kullanicilar(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS urunler (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  urun_adi VARCHAR(150) NOT NULL,
  model VARCHAR(100) DEFAULT NULL,
  alis_maliyeti DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  montaj_ucreti DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  gsm VARCHAR(50) DEFAULT NULL,
  satis_fiyati DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  tarih TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stoklar (
  urun_id INT UNSIGNED PRIMARY KEY,
  miktar INT NOT NULL DEFAULT 0,
  minimum_esik INT NOT NULL DEFAULT 0,
  tarih TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_stok_urun FOREIGN KEY (urun_id) REFERENCES urunler(id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS siparisler (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  olusturan_id INT UNSIGNED NOT NULL,
  musteri_adi VARCHAR(150) NOT NULL,
  toplam_tutar DECIMAL(12,2) NOT NULL,
  durum ENUM('beklemede','onaylandi','reddedildi') NOT NULL DEFAULT 'beklemede',
  yonetici_id INT UNSIGNED DEFAULT NULL,
  aciklama TEXT,
  tarih TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_siparis_olusturan FOREIGN KEY (olusturan_id) REFERENCES kullanicilar(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_siparis_yonetici FOREIGN KEY (yonetici_id) REFERENCES kullanicilar(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS siparis_urunleri (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  siparis_id INT UNSIGNED NOT NULL,
  urun_id INT UNSIGNED NOT NULL,
  miktar INT NOT NULL DEFAULT 1,
  birim_fiyat DECIMAL(12,2) NOT NULL,
  CONSTRAINT fk_su_siparis FOREIGN KEY (siparis_id) REFERENCES siparisler(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_su_urun FOREIGN KEY (urun_id) REFERENCES urunler(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS primler (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personel_id INT UNSIGNED NOT NULL,
  urun_id INT UNSIGNED NOT NULL,
  miktar INT NOT NULL DEFAULT 1,
  prim_tutari DECIMAL(12,2) NOT NULL,
  durum ENUM('beklemede','odendi') NOT NULL DEFAULT 'beklemede',
  tarih TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_prim_personel FOREIGN KEY (personel_id) REFERENCES kullanicilar(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_prim_urun FOREIGN KEY (urun_id) REFERENCES urunler(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS giderler (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gider_adi VARCHAR(150) NOT NULL,
  tutar DECIMAL(12,2) NOT NULL,
  sebep VARCHAR(255) DEFAULT NULL,
  tarih DATE NOT NULL,
  ekleyen_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_gider_ekleyen FOREIGN KEY (ekleyen_id) REFERENCES kullanicilar(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS islem_kayitlari (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kullanici_id INT UNSIGNED,
  eylem VARCHAR(100) NOT NULL,
  aciklama TEXT,
  ip_adresi VARCHAR(45),
  tarih TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_log_kullanici FOREIGN KEY (kullanici_id) REFERENCES kullanicilar(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Rol verileri (örnek)
INSERT IGNORE INTO roller (id, rol_adi) VALUES (1,'patron'),(2,'yonetici'),(3,'personel');

-- Admin kullanıcı (şifreyi daha sonra password_hash ile güncelleyin)
-- INSERT INTO kullanicilar (ad_soyad, rol_id, kullanici_adi, sifre) VALUES ('Sistem Yöneticisi', 1, 'patron', '<password_hash>');

