<?php

namespace App\Models;

use App\Core\Database;
use PDO;

class LeaveBalance
{
    private Database $db;
    private string $table_name = "leave_balances";

    // Properties
    public ?int $id = null;
    public int $employee_id;
    public int $leave_type_id;
    public float $balance_days; // Using float for days like 8.5
    public int $year; // The year this balance applies to
    public ?string $last_updated_at = null;

    // Optional: For holding related objects if populated
    public ?Employee $employee = null;
    public ?LeaveType $leaveType = null;

    /**
     * @param Database $db
     */
    public function __construct(?Database $db = null)
    {
        $this->db = $db ?: Database::getInstance();
    }

    // Setter methods
    /**
     * @param int $id
     * @return mixed
     */
    public function setId(int $id): self
    {
        $this->id = $id;
        return $this;
    }
    /**
     * @param int $employee_id
     * @return mixed
     */
    public function setEmployeeId(int $employee_id): self
    {
        $this->employee_id = $employee_id;
        return $this;
    }
    /**
     * @param int $leave_type_id
     * @return mixed
     */
    public function setLeaveTypeId(int $leave_type_id): self
    {
        $this->leave_type_id = $leave_type_id;
        return $this;
    }
    /**
     * @param float $balance_days
     * @return mixed
     */
    public function setBalanceDays(float $balance_days): self
    {
        $this->balance_days = $balance_days;
        return $this;
    }
    /**
     * @param int $year
     * @return mixed
     */
    public function setYear(int $year): self
    {
        $this->year = $year;
        return $this;
    }

    /**
     * Calculate the duration in days between two dates, inclusive.
     * Basic calculation, does not account for weekends or holidays yet.
     * @param string $startDate YYYY-MM-DD
     * @param string $endDate YYYY-MM-DD
     * @return int Number of days. Returns 0 if dates are invalid or end_date is before start_date.
     */
    public static function calculateLeaveDurationInDays(string $startDate, string $endDate): int
    {
        try {
            // Ensure DateTime class is available or use \DateTime if not in global namespace
            $start = new \DateTime($startDate);
            $end = new \DateTime($endDate);

            if ($start > $end) {
                return 0;
            }
            $diff = $start->diff($end);
            return $diff->days + 1;

        } catch (\Exception $e) {
            // It's better to use a proper logging mechanism if available
            // For now, using error_log if log_message function is not defined in this scope
            if (function_exists('log_message')) {
                log_message("Error calculating date duration: {$startDate} to {$endDate}. Error: ".$e->getMessage());
            } else {
                error_log("LeaveBalance Model: Error calculating date duration: {$startDate} to {$endDate}. Error: ".$e->getMessage());
            }
            return 0;
        }
    }

    /**
     * Deducts a specific number of days from an employee's leave balance for a given leave type and year.
     * If no balance record exists, it attempts to create one with a negative balance.
     *
     * @param int $employeeId
     * @param int $leaveTypeId
     * @param int $year
     * @param float $daysToDeduct
     * @return bool True on successful deduction, false otherwise.
     */
    public function deductFromBalance(int $employeeId, int $leaveTypeId, int $year, float $daysToDeduct): bool
    {
        if ($daysToDeduct <= 0) {
            if (function_exists('log_message')) {
                log_message("Days to deduct must be positive. Value: {$daysToDeduct}");
            } else {
                error_log("LeaveBalance Model: Days to deduct must be positive. Value: {$daysToDeduct}");
            }
            return false;
        }

        $this->db->beginTransaction();

        try {
            $query = "SELECT * FROM {$this->table_name}
                      WHERE employee_id = :employee_id
                        AND leave_type_id = :leave_type_id
                        AND year = :year
                      LIMIT 1 FOR UPDATE";

            $this->db->query($query);
            $this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
            $this->db->bind(':leave_type_id', $leaveTypeId, PDO::PARAM_INT);
            $this->db->bind(':year', $year, PDO::PARAM_INT);
            $currentBalanceRow = $this->db->single();

            $newBalance = 0; // Initialize to ensure it's defined for logging

            if ($currentBalanceRow) {
                $newBalance = (float) $currentBalanceRow['balance_days'] - $daysToDeduct;
                $updateQuery = "UPDATE {$this->table_name} SET balance_days = :new_balance
                                WHERE id = :id";
                $this->db->query($updateQuery);
                $this->db->bind(':new_balance', $newBalance);
                $this->db->bind(':id', (int) $currentBalanceRow['id'], PDO::PARAM_INT);
                $this->db->execute();
                if (function_exists('log_message')) {
                    log_message("Updated balance for employee {$employeeId}, type {$leaveTypeId}, year {$year}. Old: {$currentBalanceRow['balance_days']}, Deducted: {$daysToDeduct}, New: {$newBalance}");
                }

            } else {
                $newBalance = 0 - $daysToDeduct;
                $insertQuery = "INSERT INTO {$this->table_name} (employee_id, leave_type_id, year, balance_days)
                                VALUES (:employee_id, :leave_type_id, :year, :balance_days)";
                $this->db->query($insertQuery);
                $this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
                $this->db->bind(':leave_type_id', $leaveTypeId, PDO::PARAM_INT);
                $this->db->bind(':year', $year, PDO::PARAM_INT);
                $this->db->bind(':balance_days', $newBalance);
                $this->db->execute();
                if (function_exists('log_message')) {
                    log_message("Created new balance entry for employee {$employeeId}, type {$leaveTypeId}, year {$year} with balance {$newBalance}");
                }
            }

            $this->db->commit();
            return true;

        } catch (\Exception $e) {
            $this->db->rollBack();
            if (function_exists('log_message')) {
                log_message("Error deducting leave balance for employee {$employeeId}, type {$leaveTypeId}: ".$e->getMessage());
            } else {
                error_log("LeaveBalance Model: Error deducting leave balance for employee {$employeeId}, type {$leaveTypeId}: ".$e->getMessage());
            }
            return false;
        }
    }

    /**
     * Create or update a leave balance record.
     * This method uses an "upsert" like logic: if a balance for the employee, leave type, and year exists, it updates it.
     * Otherwise, it creates a new one.
     *
     * @return bool True on success, false on failure.
     */
    public function save(): bool
    {
        // Check if a balance record already exists for this employee, leave type, and year
        $existing = $this->findByEmployeeLeaveTypeYear($this->employee_id, $this->leave_type_id, $this->year);

        if ($existing && $existing->id) {
            // Update existing record
            $this->id = $existing->id; // Ensure ID is set for update
            $query = "UPDATE {$this->table_name} SET
                        balance_days = :balance_days
                        /* last_updated_at is usually handled by DB trigger or DEFAULT CURRENT_TIMESTAMP ON UPDATE */
                      WHERE id = :id";
            $this->db->query($query);
            $this->db->bind(':balance_days', $this->balance_days);
            $this->db->bind(':id', $this->id, PDO::PARAM_INT);
        } else {
            // Create new record
            $query = "INSERT INTO {$this->table_name} (employee_id, leave_type_id, balance_days, year)
                      VALUES (:employee_id, :leave_type_id, :balance_days, :year)";
            $this->db->query($query);
            $this->db->bind(':employee_id', $this->employee_id, PDO::PARAM_INT);
            $this->db->bind(':leave_type_id', $this->leave_type_id, PDO::PARAM_INT);
            $this->db->bind(':balance_days', $this->balance_days); // PDO should handle float correctly
            $this->db->bind(':year', $this->year, PDO::PARAM_INT);
        }

        if ($this->db->execute()) {
            if (!$existing || !$existing->id) {
                // If it was an insert
                $this->id = (int) $this->db->lastInsertId();
            }
            return true;
        }
        return false;
    }

    /**
     * Read leave balances.
     * @param ?int $id If null, fetches all. Otherwise, fetches by ID.
     * @return mixed Array of balances, a single balance object, or false.
     */
    public function read(?int $id = null)
    {
        if ($id !== null) {
            $query = "SELECT * FROM {$this->table_name} WHERE id = :id LIMIT 1";
            $this->db->query($query);
            $this->db->bind(':id', $id, PDO::PARAM_INT);
            $row = $this->db->single();
            if ($row) {
                return $this->populateFromRow($row);
            }
            return false;
        } else {
            // Read all balances
            $query = "SELECT * FROM {$this->table_name} ORDER BY year DESC, employee_id ASC";
            $this->db->query($query);
            $results = $this->db->resultSet();
            $balances = [];
            foreach ($results as $row) {
                $balance = new self($this->db);
                $balances[] = $balance->populateFromRow($row);
            }
            return $balances;
        }
    }

    /**
     * Find a specific leave balance for an employee, leave type, and year.
     * @param int $employeeId
     * @param int $leaveTypeId
     * @param int $year
     * @return self|false The LeaveBalance object if found, otherwise false.
     */
    public function findByEmployeeLeaveTypeYear(int $employeeId, int $leaveTypeId, int $year)
    {
        $query = "SELECT * FROM {$this->table_name}
                  WHERE employee_id = :employee_id
                    AND leave_type_id = :leave_type_id
                    AND year = :year
                  LIMIT 1";
        $this->db->query($query);
        $this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
        $this->db->bind(':leave_type_id', $leaveTypeId, PDO::PARAM_INT);
        $this->db->bind(':year', $year, PDO::PARAM_INT);
        $row = $this->db->single();
        if ($row) {
            return $this->populateFromRow($row);
        }
        return false;
    }

    /**
     * Get all leave balances for a specific employee for a given year.
     * @param int $employeeId
     * @param int $year
     * @return array Array of LeaveBalance objects.
     */
    public function getBalancesForEmployeeByYear(int $employeeId, int $year): array
    {
        $query = "SELECT lb.*, lt.name as leave_type_name, lt.alias as leave_type_alias
                  FROM {$this->table_name} lb
                  JOIN leave_types lt ON lb.leave_type_id = lt.id
                  WHERE lb.employee_id = :employee_id AND lb.year = :year
                  ORDER BY lt.name ASC";
        $this->db->query($query);
        $this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);
        $this->db->bind(':year', $year, PDO::PARAM_INT);

        $results = $this->db->resultSet();
        $balances = [];
        foreach ($results as $row) {
            $balance = $this->populateFromRow($row, true); // Pass true to populate related LeaveType
            $balances[] = $balance;
        }
        return $balances;
    }

    /**
     * Helper method to populate object properties from a database row.
     * @param array $row Associative array from database fetch.
     * @param bool $populateRelations Whether to try to populate related objects (LeaveType in this case)
     * @return self
     */
    private function populateFromRow(array $row, bool $populateRelations = false): self
    {
        $this->id = (int) $row['id'];
        $this->employee_id = (int) $row['employee_id'];
        $this->leave_type_id = (int) $row['leave_type_id'];
        $this->balance_days = (float) $row['balance_days'];
        $this->year = (int) $row['year'];
        $this->last_updated_at = $row['last_updated_at'];

        if ($populateRelations) {
            if (isset($row['leave_type_name'])) {
                $this->leaveType = new LeaveType($this->db);
                $this->leaveType->id = $this->leave_type_id;
                $this->leaveType->name = $row['leave_type_name'];
                if (isset($row['leave_type_alias'])) {
                    $this->leaveType->alias = $row['leave_type_alias'];
                }

            }
            // Could also populate Employee if needed and joined
        }
        return $this;
    }

    /**
     * Deduct days from a specific leave balance.
     * @param float $daysToDeduct Number of days to deduct.
     * @return bool True on success, false on failure (e.g., insufficient balance).
     */
    public function deductDays(float $daysToDeduct): bool
    {
        if ($this->id === null) {
            return false;
        }
        // Must have a balance record loaded
        if ($this->balance_days < $daysToDeduct) {
            return false;
        }
        // Insufficient balance

        $this->balance_days -= $daysToDeduct;
        return $this->save(); // Use save to update the balance
    }

    /**
     * Add days to a specific leave balance (e.g., correcting a deduction or cancelling a leave).
     * @param float $daysToAdd Number of days to add.
     * @return bool True on success, false on failure.
     */
    public function addDays(float $daysToAdd): bool
    {
        if ($this->id === null) {
            return false;
        }
        // Must have a balance record loaded

        $this->balance_days += $daysToAdd;
        return $this->save(); // Use save to update the balance
    }

    /**
     * Delete a leave balance record.
     * @param ?int $id If null, uses $this->id.
     * @return bool True on success, false on failure.
     */
    public function delete(?int $id = null): bool
    {
        $id_to_delete = $id ?? $this->id;
        if ($id_to_delete === null) {
            return false;
        }

        $query = "DELETE FROM {$this->table_name} WHERE id = :id";
        $this->db->query($query);
        $this->db->bind(':id', $id_to_delete, PDO::PARAM_INT);

        return $this->db->execute();
    }
}