<?php

namespace App\Models;

use App\Core\Database;
use PDO;

class LeaveRequest
{
    private Database $db;
    private string $table_name = "leave_requests";

    // Properties
    public ?int $id = null;
    public int $employee_id;
    public int $leave_type_id;
    public string $start_date; // YYYY-MM-DD
    public string $end_date; // YYYY-MM-DD
    public ?string $reason = null;
    public string $status = 'PENDING'; // PENDING, APPROVED, REJECTED, CANCELLED
    public ?int $approved_by_id = null;
    public ?string $approved_at = null;
    public ?string $requested_at = null;
    public ?string $updated_at = null;

    // For joining with other tables (optional, can be populated by specific methods)
    public ?Employee $employee = null;
    public ?LeaveType $leaveType = null;
    public ?Employee $approver = 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 string $start_date
     * @return mixed
     */
    public function setStartDate(string $start_date): self
    {
        $this->start_date = $start_date;
        return $this;
    }
    /**
     * @param string $end_date
     * @return mixed
     */
    public function setEndDate(string $end_date): self
    {
        $this->end_date = $end_date;
        return $this;
    }
    /**
     * @param string $reason
     * @return mixed
     */
    public function setReason(?string $reason): self
    {
        $this->reason = $reason;
        return $this;
    }
    /**
     * @param string $status
     * @return mixed
     */
    public function setStatus(string $status): self
    {
        // Basic validation for status
        $allowed_statuses = ['PENDING', 'APPROVED', 'REJECTED', 'CANCELLED'];
        if (in_array(strtoupper($status), $allowed_statuses)) {
            $this->status = strtoupper($status);
        } else {
            // Handle invalid status, perhaps throw an exception or default
            $this->status = 'PENDING';
        }
        return $this;
    }
    /**
     * @param int $approved_by_id
     * @return mixed
     */
    public function setApprovedById(?int $approved_by_id): self
    {
        $this->approved_by_id = $approved_by_id;
        return $this;
    }
    /**
     * @param string $approved_at
     * @return mixed
     */
    public function setApprovedAt(?string $approved_at): self
    {
        $this->approved_at = $approved_at;
        return $this;
    }

    /**
     * Create a new leave request.
     * @return bool True on success, false on failure.
     */
    public function create(): bool
    {
        $query = "INSERT INTO {$this->table_name}
                    (employee_id, leave_type_id, start_date, end_date, reason, status, approved_by_id, approved_at)
                  VALUES
                    (:employee_id, :leave_type_id, :start_date, :end_date, :reason, :status, :approved_by_id, :approved_at)";

        $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(':start_date', $this->start_date);
        $this->db->bind(':end_date', $this->end_date);
        $this->db->bind(':reason', $this->reason, $this->reason === null ? PDO::PARAM_NULL : PDO::PARAM_STR);
        $this->db->bind(':status', $this->status);
        $this->db->bind(':approved_by_id', $this->approved_by_id, $this->approved_by_id === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
        $this->db->bind(':approved_at', $this->approved_at, $this->approved_at === null ? PDO::PARAM_NULL : PDO::PARAM_STR);

        if ($this->db->execute()) {
            $this->id = (int) $this->db->lastInsertId();
            return true;
        }
        return false;
    }

    /**
     * Read leave requests.
     * @param ?int $id If null, fetches all. Otherwise, fetches by ID.
     * @return mixed Array of requests, a single request object, or false.
     */
    public function read(?int $id = null)
    {
        $baseQuery = "SELECT lr.*,
                             e.first_name as employee_first_name, e.last_name as employee_last_name, e.email as employee_email,
                             lt.name as leave_type_name,
                             approver.first_name as approver_first_name, approver.last_name as approver_last_name
                      FROM {$this->table_name} lr
                      JOIN employees e ON lr.employee_id = e.id
                      JOIN leave_types lt ON lr.leave_type_id = lt.id
                      LEFT JOIN employees approver ON lr.approved_by_id = approver.id";

        if ($id !== null) {
            $query = $baseQuery." WHERE lr.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 requests, potentially with filters or pagination in a real app
            $query = $baseQuery." ORDER BY lr.requested_at DESC";
            $this->db->query($query);
            $results = $this->db->resultSet();
            $requests = [];
            foreach ($results as $row) {
                $request = new self($this->db); // Create new instance for each row
                $requests[] = $request->populateFromRow($row);
            }
            return $requests;
        }
    }

    /**
     * Helper method to populate object properties from a database row.
     * @param array $row Associative array from database fetch.
     * @return self
     */
    private function populateFromRow(array $row): self
    {
        $this->id = (int) $row['id'];
        $this->employee_id = (int) $row['employee_id'];
        $this->leave_type_id = (int) $row['leave_type_id'];
        $this->start_date = $row['start_date'];
        $this->end_date = $row['end_date'];
        $this->reason = $row['reason'];
        $this->status = $row['status'];
        $this->approved_by_id = $row['approved_by_id'] ? (int) $row['approved_by_id'] : null;
        $this->approved_at = $row['approved_at'];
        $this->requested_at = $row['requested_at'];
        $this->updated_at = $row['updated_at'];

        // Populate related objects if data is available from JOIN
        // This is a simple way; more complex hydration might be needed for full ORM-like behavior
        $this->employee = new Employee($this->db);
        $this->employee->id = (int) $row['employee_id'];
        $this->employee->first_name = $row['employee_first_name'] ?? 'N/A';
        $this->employee->last_name = $row['employee_last_name'] ?? 'N/A';
        $this->employee->email = $row['employee_email'] ?? 'N/A';

        $this->leaveType = new LeaveType($this->db);
        $this->leaveType->id = (int) $row['leave_type_id'];
        $this->leaveType->name = $row['leave_type_name'] ?? 'N/A';

        if ($this->approved_by_id) {
            $this->approver = new Employee($this->db);
            $this->approver->id = $this->approved_by_id;
            $this->approver->first_name = $row['approver_first_name'] ?? 'N/A';
            $this->approver->last_name = $row['approver_last_name'] ?? 'N/A';
        }
        return $this;
    }

    /**
     * Get all pending leave requests for a specific employee.
     * @param int $employeeId The ID of the employee.
     * @return array Array of leave request objects (or arrays if populateFromRow creates arrays).
     */
    public function getPendingRequestsByEmployee(int $employeeId): array
    {
        $query = "SELECT lr.*,
                         e.first_name as employee_first_name, e.last_name as employee_last_name, e.email as employee_email,
                         lt.name as leave_type_name,
                         approver.first_name as approver_first_name, approver.last_name as approver_last_name
                  FROM {$this->table_name} lr
                  JOIN employees e ON lr.employee_id = e.id
                  JOIN leave_types lt ON lr.leave_type_id = lt.id
                  LEFT JOIN employees approver ON lr.approved_by_id = approver.id
                  WHERE lr.employee_id = :employee_id AND lr.status = 'PENDING'
                  ORDER BY lr.start_date ASC";

        $this->db->query($query);
        $this->db->bind(':employee_id', $employeeId, PDO::PARAM_INT);

        $results = $this->db->resultSet();
        $requests = [];
        if ($results) {
            foreach ($results as $row) {
                $request = new self($this->db);
                $requests[] = $request->populateFromRow($row);
            }
        }
        return $requests;
    }

    /**
     * Update an existing leave request.
     * Typically used to update status, approver_id, approved_at.
     * @return bool True on success, false on failure.
     */
    public function update(): bool
    {
        if ($this->id === null) {
            return false;
        }

        $query = "UPDATE {$this->table_name} SET
                    employee_id = :employee_id,
                    leave_type_id = :leave_type_id,
                    start_date = :start_date,
                    end_date = :end_date,
                    reason = :reason,
                    status = :status,
                    approved_by_id = :approved_by_id,
                    approved_at = :approved_at
                    /* updated_at is usually handled by DB trigger or DEFAULT CURRENT_TIMESTAMP ON UPDATE */
                  WHERE id = :id";

        $this->db->query($query);
        $this->db->bind(':id', $this->id, PDO::PARAM_INT);
        $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(':start_date', $this->start_date);
        $this->db->bind(':end_date', $this->end_date);
        $this->db->bind(':reason', $this->reason, $this->reason === null ? PDO::PARAM_NULL : PDO::PARAM_STR);
        $this->db->bind(':status', $this->status);
        $this->db->bind(':approved_by_id', $this->approved_by_id, $this->approved_by_id === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
        $this->db->bind(':approved_at', $this->approved_at, $this->approved_at === null ? PDO::PARAM_NULL : PDO::PARAM_STR);

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

    /**
     * Update the status of a leave request.
     * @param string $status The new status.
     * @param ?int $approvedById The ID of the employee who approved/rejected (if applicable).
     * @return bool True on success, false on failure.
     */
    public function updateStatus(string $status, ?int $approvedById = null): bool
    {
        if ($this->id === null) {
            return false;
        }
        $this->setStatus($status); // Use setter for validation
        $this->approved_by_id = $approvedById;
        $this->approved_at = ($status === 'APPROVED' || $status === 'REJECTED') ? date('Y-m-d H:i:s') : null;

        $query = "UPDATE {$this->table_name} SET
                    status = :status,
                    approved_by_id = :approved_by_id,
                    approved_at = :approved_at
                  WHERE id = :id";

        $this->db->query($query);
        $this->db->bind(':id', $this->id, PDO::PARAM_INT);
        $this->db->bind(':status', $this->status);
        $this->db->bind(':approved_by_id', $this->approved_by_id, $this->approved_by_id === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
        $this->db->bind(':approved_at', $this->approved_at, $this->approved_at === null ? PDO::PARAM_NULL : PDO::PARAM_STR);

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

    /**
     * Delete a leave request.
     * @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();
    }

    // Potential methods to add later:
    // - findByEmployeeId(int $employeeId, ?string $status = null, ?string $dateRangeStart = null, ?string $dateRangeEnd = null)
    // - findByManagerId(int $managerId, ?string $status = 'PENDING')
    // - findPendingRequests()
    // - getRequestsForHRSummary(string $date)

    /**
     * Get all approved leave/wfh requests that are active on a specific date.
     * @param string $dateString The date to check in 'YYYY-MM-DD' format.
     * @return array Array of leave request objects (or arrays, depending on populateFromRow).
     */
    public function getAbsencesForDate(string $dateString): array
    {
        // Validate dateString format
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $dateString) || !\DateTime::createFromFormat('Y-m-d', $dateString)) {
            // Use a globally available log_message if defined, otherwise error_log
            if (function_exists('log_message')) {
                log_message("Invalid date format for getAbsencesForDate: {$dateString}");
            } else {
                error_log("LeaveRequest Model: Invalid date format for getAbsencesForDate: {$dateString}");
            }
            return [];
        }

        $query = "SELECT lr.*,
                         e.first_name as employee_first_name, e.last_name as employee_last_name, e.email as employee_email,
                         lt.name as leave_type_name, lt.alias as leave_type_alias
                         -- Add e.department if you have it and want to display it
                  FROM {$this->table_name} lr
                  JOIN employees e ON lr.employee_id = e.id
                  JOIN leave_types lt ON lr.leave_type_id = lt.id
                  WHERE lr.status = 'APPROVED'
                    AND :check_date BETWEEN lr.start_date AND lr.end_date
                  ORDER BY lt.name ASC, e.first_name ASC, e.last_name ASC";

        $this->db->query($query);
        $this->db->bind(':check_date', $dateString);

        $results = $this->db->resultSet();
        $absences = [];
        if ($results) {
            foreach ($results as $row) {
                $request = new self($this->db);
                $absences[] = $request->populateFromRow($row);
            }
        }
        return $absences;
    }
}