<?php 

namespace App\Repositories;

use App\Models\Stage;
use PDO;
use App\Config\ContextDB;
use App\Models\Project;
use DateTime;

class StageRepository {
    
    private PDO $context;

    public function __construct() {
        $this->context = (new ContextDB())->conn;
    }

    public function getAll()
    {
        $consult = $this->context->prepare("
            SELECT * FROM stages;
        ");
    }

    public function findBy(string $columnName, int $stageId)
    {
        $consult = $this->context->prepare("
            SELECT 
                s.id AS stage_id,
                s.name AS stage_name,
                s.description,
                s.start_date,
                s.end_date,
                s.deletable,
                s.status_traffic_light,
                p.id AS project_id,
                p.name AS project_name,
                parent.id AS parent_stage_id,
                parent.name AS parent_stage_name
            FROM stages s
            INNER JOIN projects p ON p.id = s.project_id
            LEFT JOIN stages parent ON parent.id = s.parent_id
            WHERE s.$columnName = :stageId;
        ");

        $consult->execute([
            "stageId" => $stageId
        ]);

        $response = $consult->fetch(PDO::FETCH_ASSOC);

        return new Stage([
            'id' => $response['stage_id'] ?? 0,
            "name" => $response['stage_name'] ?? '',
            "description" => $response['description'] ?? '',
            "start_date" => isset($response['start_date']) ? $response['start_date'] : null,
            "end_date" => isset($response['end_date']) ? $response['end_date'] : null,
            "project_id" => $response['project_id'] ?? null,
            "parent_id" => $response['parent_stage_id'] ?? null,
            "deletable" => $response['deletable'] ?? false,
            "status_traffic_light" => $response['status_traffic_light'] ?? null
        ]);
    }

    public function create(Stage $stage): int
    {
        $consult = $this->context->prepare("
            INSERT INTO stages (name, description, start_date, end_date, project_id, parent_id, deletable)
            VALUES (:name, :description, :start_date, :end_date, :project_id, :parent_id, :deletable)
        ");

        $consult->execute([
            "name"       => $stage->name,
            "description"=> $stage->description,
            "start_date" => $stage->start_date?->format('Y-m-d'),
            "end_date"   => $stage->end_date?->format('Y-m-d'),
            "project_id" => $stage->project_id,
            "parent_id"  => $stage->parent_id ?? null,
            "deletable" => $stage->deletable
        ]);

        return (int) $this->context->lastInsertId(); // opcional: devolver el id insertado
    }

    public function update(Stage $stage): bool
    {
        $consult = $this->context->prepare("
            UPDATE stages
            SET 
                name = :name,
                description = :description,
                start_date = :start_date,
                end_date = :end_date,
                updated_at = NOW()
            WHERE id = :stageId
        ");

        $consult->execute([
            "stageId"     => $stage->id,
            "name"        => $stage->name,
            "description" => $stage->description,
            "start_date"  => $stage->start_date?->format('Y-m-d'),
            "end_date"    => $stage->end_date?->format('Y-m-d')
        ]);

        return $consult->rowCount() > 0; // true si actualizó algo
    }

    public function updateTrafficLight(int $stageId, string $status_traffic_light): bool
    {
        $consult = $this->context->prepare("
            UPDATE stages
            SET 
                status_traffic_light = :status_traffic_light,
                updated_at = NOW()
            WHERE id = :stageId
        ");

        $consult->execute([
            "stageId"              => $stageId,
            "status_traffic_light" => $status_traffic_light
        ]);

        return $consult->rowCount() > 0; // true si actualizó algo
    }

    public function getStageTreeByProjectId(int $projectId) : array
    {
        $consult = $this->context->prepare(" 
            SELECT 
                s.id,
                s.name,
                s.description,
                s.start_date,
                s.end_date,
                s.status,
                s.parent_id,
                s.project_id,
                s.status_traffic_light
            FROM stages s
            WHERE s.project_id = :projectId
            ORDER BY s.id ASC;
        ");
        $consult->execute(['projectId' => $projectId]);
        $response = $consult->fetchAll(PDO::FETCH_ASSOC);

        $stages = array_map(fn($row) => new Stage($row), $response);

        return Stage::buildTree($stages);
    }

    public function delete(int $stageId): bool
    {
        $consult = $this->context->prepare("
            DELETE FROM stages
            WHERE id = :stageId
            AND deletable = 1
        ");

        $consult->execute(["stageId" => $stageId]);

        return $consult->rowCount() > 0;
    }

    public function getProjectBy($stageId)  : ?Project
    {
        $consult = $this->context->prepare("
            SELECT p.* 
            FROM stages s
            INNER JOIN projects p ON p.id = s.project_id
            WHERE s.id = :stageId
        ");

        $consult->execute(["stageId" => $stageId]);
        $response = $consult->fetch(PDO::FETCH_ASSOC);

        if (!$response) {
            return null; // si no existe
        }

        return new Project([
            "id" => $response["id"],
            "name" => $response["name"],
            "start_date" => $response["start_date"],
            "end_date" => $response["end_date"],
            "priority" => $response["priority"],
            "client_id" => $response["client_id"],
            "user_created" => $response["user_created"]
        ]);
    }
}