CREATE DATABASE IF NOT EXISTS serdaryanik_servis
  CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE serdaryanik_servis;

-- Kullanıcılar (admin/koordinator/teknisyen)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','koordinator','teknisyen') NOT NULL DEFAULT 'teknisyen',
  phone VARCHAR(30),
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Kişiler (müşteri/bağlantı)
CREATE TABLE IF NOT EXISTS contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  company VARCHAR(150),
  email VARCHAR(190),
  phone VARCHAR(30),
  phone_alt VARCHAR(30),
  address VARCHAR(255),
  note TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Projeler
CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  contact_id INT,
  status ENUM('yeni','aktif','beklemede','tamamlandi','iptal') NOT NULL DEFAULT 'yeni',
  start_date DATE,
  end_date DATE,
  description TEXT,
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Proje Atamaları (projeye teknisyen atanması)
CREATE TABLE IF NOT EXISTS project_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  technician_id INT NOT NULL,
  role ENUM('lead','support') NOT NULL DEFAULT 'support',
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_assignment (project_id, technician_id),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (technician_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Planlama Takvimi (görev/ziyaret/kurulum)
CREATE TABLE IF NOT EXISTS schedule_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  project_id INT,
  contact_id INT,
  technician_id INT,
  type ENUM('gorev','ziyaret','kurulum','bakim','diger') NOT NULL DEFAULT 'gorev',
  status ENUM('planlandi','devam','tamamlandi','iptal') NOT NULL DEFAULT 'planlandi',
  start_at DATETIME NOT NULL,
  end_at DATETIME NULL,
  all_day TINYINT(1) NOT NULL DEFAULT 0,
  location VARCHAR(200),
  notes TEXT,
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (technician_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_tech_time (technician_id, start_at, end_at),
  INDEX idx_project_time (project_id, start_at)
) ENGINE=InnoDB;

-- Telefon Arama Kayıtları (ayrı takvimde gösterilecek)
CREATE TABLE IF NOT EXISTS call_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT,
  project_id INT,
  technician_id INT,
  phone_number VARCHAR(30) NOT NULL,
  direction ENUM('giden','gelen','bilinmiyor') NOT NULL DEFAULT 'bilinmiyor',
  call_time DATETIME NOT NULL,
  duration_sec INT DEFAULT 0,
  outcome ENUM('yanitlandi','mesgul','cevapsiz','mesaj','diger') NOT NULL DEFAULT 'diger',
  notes TEXT,
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
  FOREIGN KEY (technician_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_call_time (call_time),
  INDEX idx_call_tech (technician_id, call_time)
) ENGINE=InnoDB;

-- Resmi Tatiller (bilgilendirici, planlamayı engellemez)
CREATE TABLE IF NOT EXISTS holidays (
  id INT AUTO_INCREMENT PRIMARY KEY,
  holiday_date DATE NOT NULL,
  name VARCHAR(150) NOT NULL,
  scope ENUM('ulusal','kurumsal') NOT NULL DEFAULT 'ulusal',
  UNIQUE KEY uniq_hday (holiday_date, scope)
) ENGINE=InnoDB;