-- Personel Yönetim Sistemi
-- 1) Personel Puantaj (Alt kullanıcı erişebilir)
-- 2) Personel Maaş (Sadece admin erişebilir)

-- Personeller Tablosu
CREATE TABLE IF NOT EXISTS `personnel` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `full_name` VARCHAR(100) NOT NULL,
  `position` VARCHAR(100),
  `base_salary` DECIMAL(15,2) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `hire_date` DATE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Puantaj Tablosu (Gün bazlı çalışma kayıtları)
CREATE TABLE IF NOT EXISTS `personnel_attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `personnel_id` INT NOT NULL,
  `work_date` DATE NOT NULL,
  `work_days` DECIMAL(5,2) DEFAULT 1,
  `overtime_hours` DECIMAL(5,2) DEFAULT 0,
  `notes` TEXT,
  `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`, `work_date`),
  INDEX `idx_work_date` (`work_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Maaş Hesaplama Tablosu (Ay bazlı)
CREATE TABLE IF NOT EXISTS `personnel_salary` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `personnel_id` INT NOT NULL,
  `year` INT NOT NULL,
  `month` INT NOT NULL,
  `total_days` DECIMAL(5,2) DEFAULT 0,
  `overtime_hours` DECIMAL(5,2) DEFAULT 0,
  `base_amount` DECIMAL(15,2) DEFAULT 0,
  `overtime_amount` DECIMAL(15,2) DEFAULT 0,
  `bonus` DECIMAL(15,2) DEFAULT 0,
  `deductions` DECIMAL(15,2) DEFAULT 0,
  `total_amount` DECIMAL(15,2) DEFAULT 0,
  `status` ENUM('pending','approved','paid') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_salary` (`personnel_id`, `year`, `month`),
  INDEX `idx_period` (`year`, `month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ödeme/Avans Tablosu
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','bonus','deduction') DEFAULT 'advance',
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE,
  INDEX `idx_payment_date` (`payment_date`),
  INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kullanıcı izinleri için users tablosuna role ekle
ALTER TABLE users ADD COLUMN IF NOT EXISTS `role` ENUM('admin','user') DEFAULT 'user';
