-- SAYTEG Güvenlik tam kurulum SQL v7
-- Hata düzeltildi: ana tablolar (roles, users, projects, personnel) önce oluşturulur.
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role_key VARCHAR(50) NOT NULL UNIQUE,
  role_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role_id INT NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  phone VARCHAR(40),
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_name VARCHAR(180) NOT NULL,
  company_name VARCHAR(180),
  city VARCHAR(80),
  district VARCHAR(80),
  address TEXT,
  status TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS personnel (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  project_id INT NOT NULL,
  tc_no VARCHAR(20),
  card_no VARCHAR(60),
  card_expire_date DATE NULL,
  start_date DATE NULL,
  vehicle_plate VARCHAR(20),
  blood_type VARCHAR(10),
  address TEXT,
  emergency_contact VARCHAR(150),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_personnel_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_personnel_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  location_name VARCHAR(180) NOT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  address TEXT,
  qr_code VARCHAR(150),
  CONSTRAINT fk_locations_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS shifts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shift_name VARCHAR(80) NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS shift_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT NOT NULL,
  project_id INT NOT NULL,
  shift_id INT NOT NULL,
  shift_date DATE NOT NULL,
  status VARCHAR(30) DEFAULT 'planlandi',
  CONSTRAINT fk_sa_personnel FOREIGN KEY (personnel_id) REFERENCES personnel(id) ON DELETE CASCADE,
  CONSTRAINT fk_sa_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_sa_shift FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS incident_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  project_id INT NOT NULL,
  title VARCHAR(180) NOT NULL,
  incident_date DATE NOT NULL,
  description TEXT,
  file_path VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_inc_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_inc_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS inspections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  inspector_id INT NOT NULL,
  inspection_date DATE NOT NULL,
  score INT DEFAULT 0,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ins_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_ins_user FOREIGN KEY (inspector_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  file_path VARCHAR(255),
  role_scope VARCHAR(50) DEFAULT 'all',
  expire_date DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT NOT NULL,
  project_id INT NOT NULL,
  work_month CHAR(7) NOT NULL,
  work_days INT DEFAULT 0,
  work_hours DECIMAL(8,2) DEFAULT 0,
  overtime_hours DECIMAL(8,2) DEFAULT 0,
  missing_hours DECIMAL(8,2) DEFAULT 0,
  total_hours DECIMAL(8,2) DEFAULT 0,
  meal_days INT DEFAULT 0,
  meal_amount DECIMAL(10,2) DEFAULT 0,
  road_days INT DEFAULT 0,
  road_amount DECIMAL(10,2) DEFAULT 0,
  meal_total DECIMAL(10,2) DEFAULT 0,
  road_total DECIMAL(10,2) DEFAULT 0,
  payment_total DECIMAL(10,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_att_personnel FOREIGN KEY (personnel_id) REFERENCES personnel(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS leaves (
  id INT AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT NOT NULL,
  project_id INT NOT NULL,
  leave_type ENUM('Yıllık İzin','Günlük İzin','Hastalık İzni','İdari İzin','Mazeret İzni') NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  days INT DEFAULT 1,
  replacement_person VARCHAR(150),
  status VARCHAR(30) DEFAULT 'Onaylandı',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_lv_personnel FOREIGN KEY (personnel_id) REFERENCES personnel(id) ON DELETE CASCADE,
  CONSTRAINT fk_lv_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

INSERT IGNORE INTO roles (id, role_key, role_name) VALUES
(1,'admin','Sistem Yöneticisi'),(2,'operation','Operasyon'),(3,'staff','Personel'),(4,'project_manager','Proje Sorumlusu');

INSERT IGNORE INTO users (id, role_id, full_name, email, password_hash, phone, status) VALUES
(1,1,'Sayteg Admin','admin@sayteg.local','$2y$12$1roP9uCkUCFHz17LrV6KH.QO/P7RnbxZpQnc.KJYrvvphorNHAr6C','05320000000',1),
(2,2,'Operasyon Kullanıcısı','operasyon@sayteg.local','$2y$12$1roP9uCkUCFHz17LrV6KH.QO/P7RnbxZpQnc.KJYrvvphorNHAr6C','05330000000',1),
(3,3,'Ahmet Kılıç','personel@sayteg.local','$2y$12$1roP9uCkUCFHz17LrV6KH.QO/P7RnbxZpQnc.KJYrvvphorNHAr6C','05340000000',1);

INSERT IGNORE INTO projects (id, project_name, company_name, city, district, address, status) VALUES
(1,'Merkez Kampüs Güvenlik','SAYTEG Güvenlik','İstanbul','Kadıköy','Merkez lokasyon',1),
(2,'Lojistik Depo Projesi','SAYTEG Güvenlik','Kocaeli','Gebze','Depo güvenliği',1);

INSERT IGNORE INTO personnel (id,user_id,project_id,tc_no,card_no,card_expire_date,start_date,vehicle_plate,blood_type,address,emergency_contact) VALUES
(1,3,1,'11111111111','OGG-2026-001',DATE_ADD(CURDATE(), INTERVAL 25 DAY),CURDATE(),'34 ABC 001','A+','İstanbul','05350000000');

INSERT IGNORE INTO locations (id, project_id, location_name, latitude, longitude, address, qr_code) VALUES
(1,1,'Ana Giriş',40.9923000,29.0244000,'Ana kapı','QR-ANA-GIRIS'),
(2,2,'Depo Nizamiyesi',40.8077000,29.4306000,'Depo giriş','QR-DEPO');

INSERT IGNORE INTO shifts (id, shift_name, start_time, end_time) VALUES
(1,'Gündüz','08:00:00','20:00:00'),(2,'Gece','20:00:00','08:00:00');

INSERT IGNORE INTO shift_assignments (id, personnel_id, project_id, shift_id, shift_date, status) VALUES
(1,1,1,1,CURDATE(),'planlandi');

INSERT IGNORE INTO attendance (id,personnel_id, project_id, work_month, work_days, work_hours, overtime_hours, missing_hours, total_hours, meal_days, meal_amount, road_days, road_amount, meal_total, road_total, payment_total) VALUES
(1,1,1,'2026-07',22,176,12,0,188,22,150,22,80,3300,1760,5060),(2,1,1,'2026-06',21,168,8,2,174,21,150,21,80,3150,1680,4830);

INSERT IGNORE INTO leaves (id,personnel_id, project_id, leave_type, start_date, end_date, days, replacement_person, status, notes) VALUES
(1,1,1,'Yıllık İzin',CURDATE(),DATE_ADD(CURDATE(), INTERVAL 7 DAY),8,'Burak Can','Onaylandı','Vardiya planı geçici personel ile kapatıldı');

INSERT IGNORE INTO documents (id,title,file_path,role_scope,expire_date) VALUES
(1,'Örnek Sozlesme / Evrak Bitis Takibi',NULL,'all',DATE_ADD(CURDATE(), INTERVAL 20 DAY));


-- v7 tam kurulum: ana tablolar, harita, puantaj yemek/yol ve örnek veriler tek dosyada kurulur.
SET FOREIGN_KEY_CHECKS=1;