-- Excel Uyumlu Personel Sistemi

CREATE TABLE IF NOT EXISTS `personnel` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `full_name` VARCHAR(100) NOT NULL,
  `daily_wage` DECIMAL(10,2) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `personnel_attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `personnel_id` INT NOT NULL,
  `year` INT NOT NULL,
  `month` INT NOT NULL,
  `calisan_gun` DECIMAL(5,1) DEFAULT 0,
  `yillik_izin` DECIMAL(5,1) DEFAULT 0,
  `gun_asiri_ek_mesai` DECIMAL(5,1) DEFAULT 0,
  `pazar_ek_mesai` DECIMAL(5,1) DEFAULT 0,
  `resmi_tatil_mesai` DECIMAL(5,1) DEFAULT 0,
  `yemek_gun` DECIMAL(5,1) DEFAULT 0,
  `ildisi_gun` DECIMAL(5,1) DEFAULT 0,
  `cep_masraf` DECIMAL(10,2) DEFAULT 0,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_attendance` (`personnel_id`, `year`, `month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `personnel_payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `personnel_id` INT NOT NULL,
  `payment_date` DATE NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `type` ENUM('advance','salary','deduction') DEFAULT 'advance',
  `description` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
