<?php

namespace App\Models;

use App\Enums\ProcessGeneralState;
use PDO;
use Config\Database;

class Process extends BaseModel
{
    protected static $table = 'processes';

    public $id;
    public $num_exped;
    public $entry_date;
    public $period;
    public $folios;
    public $user_id;
    public $key;
    public $subject;
    public $rationale;
    public $reset;
    public $active;
    public $deleted_at;
    public $last_location;
    public $procedure_id;
    public $custom_procedure;
    public $resolved;
    public $responsible_id;
    public $group_derived;
    public $subgroup_derived;
    public $general_state;
    public $re_entered;
    public $correction_deadline;
    public $derivation_mode;
    public ?State $state = null;
    
    /** @var Procedure|null */
    public $procedure = null;

    public function __construct($data = [])
    {
        parent::__construct($data);

        $this->folios = $data['folios'] ?? 0;
        $this->reset = $data['reset'] ?? 1;
        $this->active = $data['active'] ?? 1;
        $this->resolved = $data['resolved'] ?? 0;
        $this->general_state = $data['general_state'] ?? ProcessGeneralState::EN_PROCESO;
        $this->re_entered = $data['re_entered'] ?? 0;
        $this->derivation_mode = $data['derivation_mode'] ?? "AUTOMATICO";
    }

    public function save()
    {
        $db = Database::getConnection();

        if ($this->id) {
            $query = "UPDATE processes 
                      SET num_exped = :num_exped, entry_date = :entry_date, period = :period, 
                          folios = :folios, user_id = :user_id, 
                          `key` = :key, subject = :subject, rationale = :rationale, reset = :reset, active = :active, 
                          deleted_at = :deleted_at, last_location = :last_location, 
                          procedure_id = :procedure_id, custom_procedure = :custom_procedure, 
                          resolved = :resolved, responsible_id = :responsible_id, group_derived = :group_derived, subgroup_derived = :subgroup_derived, general_state = :general_state, re_entered = :re_entered, correction_deadline = :correction_deadline, derivation_mode = :derivation_mode
                      WHERE id = :id";
            $stmt = $db->prepare($query);
            $stmt->execute([
                ':id' => $this->id,
                ':num_exped' => $this->num_exped,
                ':entry_date' => $this->entry_date,
                ':period' => $this->period,
                ':folios' => $this->folios,
                ':user_id' => $this->user_id,
                ':key' => $this->key,
                ':subject' => $this->subject,
                ':rationale' => $this->rationale,
                ':reset' => $this->reset,
                ':active' => $this->active,
                ':deleted_at' => $this->deleted_at,
                ':last_location' => $this->last_location,
                ':procedure_id' => $this->procedure_id,
                ':custom_procedure' => $this->custom_procedure,
                ':resolved' => $this->resolved,
                ':responsible_id' => $this->responsible_id,
                ':group_derived' => $this->group_derived,
                ':subgroup_derived' => $this->subgroup_derived,
                ':general_state' => $this->general_state,
                ':re_entered' => $this->re_entered,
                ':correction_deadline' => $this->correction_deadline,
                ':derivation_mode'=> $this->derivation_mode,
            ]);
        } else {
            $query = "INSERT INTO processes (num_exped, entry_date, period, folios, user_id, 
                                             `key`, subject, rationale, reset, active, deleted_at, last_location, 
                                             procedure_id, custom_procedure, resolved, responsible_id, group_derived, subgroup_derived, general_state, re_entered, correction_deadline, derivation_mode) 
                      VALUES (:num_exped, :entry_date, :period, :folios, :user_id, 
                              :key, :subject, :rationale, :reset, :active, :deleted_at, :last_location, 
                              :procedure_id, :custom_procedure, :resolved, :responsible_id, :group_derived, :subgroup_derived, :general_state, :re_entered, :correction_deadline, :derivation_mode)";
            $stmt = $db->prepare($query);
            $stmt->execute([
                ':num_exped' => $this->num_exped,
                ':entry_date' => $this->entry_date,
                ':period' => $this->period,
                ':folios' => $this->folios,
                ':user_id' => $this->user_id,
                ':key' => $this->key,
                ':subject' => $this->subject,
                ':rationale' => $this->rationale,
                ':reset' => $this->reset,
                ':active' => $this->active,
                ':deleted_at' => $this->deleted_at,
                ':last_location' => $this->last_location,
                ':procedure_id' => $this->procedure_id,
                ':custom_procedure' => $this->custom_procedure,
                ':resolved' => $this->resolved,
                ':responsible_id' => $this->responsible_id,
                ':group_derived' => $this->group_derived,
                ':subgroup_derived' => $this->subgroup_derived,
                ':general_state' => $this->general_state,
                ':re_entered' => $this->re_entered,
                ':correction_deadline' => $this->correction_deadline,
                ':derivation_mode'=> $this->derivation_mode,
            ]);

            $this->id = $db->lastInsertId();
        }
    }

    public function group()
    {
        return Group::find($this->group_derived);
    }

    public function subgroup()
    {
        return Subgroup::find($this->subgroup_derived);
    }

    public function areaGroupType()
    {
        $group = $this->group();
        return $group ? AreaGroupType::find($group->area_group_type_id) : null;
    }

    public function area()
    {
        $areaGroupType = $this->areaGroupType();
        return $areaGroupType ? Area::find($areaGroupType->area_id) : null;
    }

    public function groupType()
    {
        $areaGroupType = $this->areaGroupType();
        return $areaGroupType ? GroupType::find($areaGroupType->group_type_id) : null;
    }

    public function procedure()
    {
        return Procedure::find($this->procedure_id);
    }

    public function user()
    {
        return UserComunity::find($this->user_id);
    }

    public function lastLocation()
    {
        return Location::find($this->last_location);
    }
    
    public function locations()
    {
        return Location::where('process_id', $this->id);
    }

    public function getResolutionFile()
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("
            SELECT * FROM attachments 
            WHERE location_id = (
                SELECT id FROM locations 
                WHERE process_id = :process_id 
                AND state = 'RESUELTO'
                ORDER BY created_at DESC 
                LIMIT 1
            )
            LIMIT 1
        ");
        $stmt->bindParam(":process_id", $this->id, PDO::PARAM_INT);
        $stmt->execute();
        
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $row ? new Attachment($row) : null;
    }

    public static function getUnresolvedProcesses()
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT * FROM processes WHERE resolved = 0 AND deleted_at IS NULL");
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $processes = [];
        foreach ($results as $row) {
            $process = new Process();
            foreach ($row as $key => $value) {
                $process->$key = $value;
            }
            $processes[] = $process;
        }

        return $processes;
    }

    public function isResolved()
    {
        return (int)$this->resolved === 1;
    }

}