<?php

namespace App\Repositories;

use PDO;
use App\Config\ContextDB;
use App\Models\Project;
use App\Models\User;

class ProjectRepository {
    private PDO $context;

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

    public function getAll(): array 
    {
        $consult = $this->context->prepare("
            SELECT 
                p.id AS project_id,
                p.name AS project_name,
                p.start_date,
                p.end_date,
                p.status,
                p.priority,
                p.budget,
                p.current_cost,
                
                c.id AS client_id,
                c.name_company,
                c.name_contact,
                c.phone_contact,
                c.email_contact,
                
                u.id AS user_id,
                u.name AS user_name,
                u.username,
                u.email AS user_email,
                u.role_id AS user_role_id,
                r.name AS role_name

            FROM projects p
            INNER JOIN clients c ON c.id = p.client_id
            INNER JOIN users u ON u.id = p.user_created
            INNER JOIN roles r ON r.id = u.role_id;
        ");
        $consult->execute();
        $response = $consult->fetchAll(PDO::FETCH_ASSOC);
        
        return $response = array_map(function($item) {
            return new Project([
                'id' => $item['project_id'],
                'name' => $item['project_name'],
                'start_date' => $item['start_date'],
                'end_date' => $item['end_date'],
                'status' => $item['status'],
                'priority' => $item['priority'],
                'budget' => $item['budget'],
                'actual_cost' => $item['current_cost'],
                'client' => [
                    'id' => $item['client_id'],
                    'name_company' => $item['name_company'],
                    'name_contact' => $item['name_contact'],
                    'phone_contact' => $item['phone_contact'],
                    'email_contact' => $item['email_contact'],
                ],
                'user' => [
                    'id' => $item['user_id'],
                    'name' => $item['user_name'],
                    'username' => $item['username'],
                    'email' => $item['user_email'],
                    'role_id' => $item['user_role_id'],
                    'role' => [
                        'id' => $item['user_role_id'],
                        'name' => $item['role_name']
                    ]
                ]
            ]);
        }, $response);
    }

   public function getMyProjects(int $userId): array
    {
        $consult = $this->context->prepare("
            SELECT 
                p.id AS project_id,
                p.name AS project_name,
                p.start_date,
                p.end_date,
                p.status,
                p.priority,
                p.budget,
                p.current_cost,
                c.id AS client_id,
                c.name_company,
                c.name_contact,
                c.phone_contact,
                c.email_contact,
                u.id AS user_id,
                u.name AS user_name,
                u.username,
                u.email AS user_email,
                u.role_id AS user_role_id,
                r.name AS role_name
            FROM projects p
            INNER JOIN clients c ON p.client_id = c.id
            INNER JOIN user_project up ON p.id = up.project_id
            INNER JOIN users u ON up.user_id = u.id
            INNER JOIN roles r ON u.role_id = r.id
            WHERE up.user_id = :user_id
        ");

        $consult->execute(['user_id' => $userId]);
        $response = $consult->fetchAll(PDO::FETCH_ASSOC);

        return array_map(function($item) {
            return new Project([
                'id' => $item['project_id'],
                'name' => $item['project_name'],
                'start_date' => $item['start_date'],
                'end_date' => $item['end_date'],
                'status' => $item['status'],
                'priority' => $item['priority'],
                'budget' => $item['budget'],
                'actual_cost' => $item['current_cost'],
                'client' => [
                    'id' => $item['client_id'],
                    'name_company' => $item['name_company'],
                    'name_contact' => $item['name_contact'],
                    'phone_contact' => $item['phone_contact'],
                    'email_contact' => $item['email_contact'],
                ],
                'user' => [
                    'id' => $item['user_id'],
                    'name' => $item['user_name'],
                    'username' => $item['username'],
                    'email' => $item['user_email'],
                    'role_id' => $item['user_role_id'],
                    'role' => [
                        'id' => $item['user_role_id'],
                        'name' => $item['role_name']
                    ]
                ]
            ]);
        }, $response);
    }

    public function findBy(string $columnName, $value): ?Project
    {
        $consult = $this->context->prepare("
            SELECT 
                p.id AS project_id,
                p.name AS project_name,
                p.start_date,
                p.end_date,
                p.status,
                p.priority,
                p.budget,
                p.current_cost,
                
                c.id AS client_id,
                c.name_company,
                c.name_contact,
                c.phone_contact,
                c.email_contact,
                
                u.id AS user_id,
                u.name AS user_name,
                u.username,
                u.email AS user_email,
                u.role_id AS user_role_id,
                r.name AS role_name,

                pu.token AS shared_token,
                pu.created_at AS token_created  

            FROM projects p
            INNER JOIN clients c ON c.id = p.client_id
            INNER JOIN users u ON u.id = p.user_created
            INNER JOIN roles r ON r.id = u.role_id
            LEFT JOIN protected_urls pu ON pu.resource_id = p.id
            WHERE p.$columnName = :value;
        ");
        
        $consult->execute(['value' => $value]);
        $response = $consult->fetch(PDO::FETCH_ASSOC);

        if(!$response){
            return null;
        }

        return new Project([
            'id' => $response['project_id'],
            'name' => $response['project_name'],
            'start_date' => $response['start_date'],
            'end_date' => $response['end_date'],
            'status' => $response['status'],
            'priority' => $response['priority'],
            'budget' => $response['budget'],
            'current_cost' => $response['current_cost'],
            'client' => [
                'id' => $response['client_id'],
                'name_company' => $response['name_company'],
                'name_contact' => $response['name_contact'],
                'phone_contact' => $response['phone_contact'],
                'email_contact' => $response['email_contact'],
            ],
            'user' => [
                'id' => $response['user_id'],
                'name' => $response['user_name'],
                'username' => $response['username'],
                'email' => $response['user_email'],
                'role_id' => $response['user_role_id'],
                'role' => [
                    'id' => $response['user_role_id'],
                    'name' => $response['role_name']
                ]
            ],
            'shared_url' => [
                'token' => $response['shared_token'] ?? null,
                'created_at' => $response['token_created'] ?? null
            ]
        ]);
    }

    public function create(Project $project): int {
        $consult = $this->context->prepare("
            INSERT INTO projects(name, start_date, end_date, priority, user_created, client_id) 
            VALUES(:name, :start_date, :end_date, :priority, :user_created, :client_id)
        ");
        
        $consult->execute([
            "name" => $project->name,
            "start_date" => $project->start_date->format('Y-m-d'),
            "end_date" => $project->end_date->format('Y-m-d'),
            "priority" => $project->priority,
            "user_created" => $project->user_created,
            "client_id" => $project->client_id
        ]);

        return (int) $this->context->lastInsertId();
    }

    public function configured(int $projectId, string $jsonStructure): bool
    {
        $consult = $this->context->prepare("CALL CreateDefaultStageStructure(:projectId, :structure)");
        
        return $consult->execute([
            "projectId" => $projectId,
            "structure" => $jsonStructure
        ]);
    }

    public function getUsersIn(int $projectId): array
    {
        $consult = $this->context->prepare("
            SELECT u.id, u.name, r.name as 'role', u.isActive
            FROM user_project up 
            INNER JOIN users u ON u.id = up.user_id
            INNER JOIN roles r ON r.id = u.role_id
            WHERE up.project_id = :projectId;
        ");

        $consult->execute(['projectId' => $projectId]);
        $response = $consult->fetchAll(PDO::FETCH_ASSOC);

        return array_map(fn($item) => 
            new User([
                "id" => $item['id'],
                "name" => $item['name'],
                "isActive" => $item['isActive'] ?? true,
                "role" => [
                    "name" => $item['role']
                ]
            ]), $response);
    }

    public function getUsersNotIn(int $projectId): array
    {
        $consult = $this->context->prepare("
            SELECT u.id, u.name, r.name AS role, u.isActive
            FROM users u
            INNER JOIN roles r ON r.id = u.role_id
            LEFT JOIN user_project up 
                ON u.id = up.user_id AND up.project_id = :projectId
            WHERE up.project_id IS NULL;
        ");

        $consult->execute(['projectId' => $projectId]);
        $response = $consult->fetchAll(PDO::FETCH_ASSOC);

        return array_map(fn($item) => 
            new User([
                "id" => $item['id'],
                "name" => $item['name'],
                "isActive" => $item['isActive'] ?? true,
                "role" => [
                    "name" => $item['role']
                ]
            ]), $response);
    }

    public function addUserIn(int $userId, int $projectId)
    {
        $consult = $this->context->prepare("
            INSERT INTO user_project(user_id, project_id) VALUES(:user_id, :project_id);
        ");

        $consult->execute([
            'user_id' => $userId,
            "project_id" => $projectId
        ]);

        return $consult->rowCount() === 1;
    }

    public function removeUser(int $projectId, int $userId): bool
    {
        $consult = $this->context->prepare("
            DELETE FROM user_project WHERE project_id = :projectId AND user_id = :userId
        ");

        $consult->execute([
            'projectId' => $projectId,
            'userId' => $userId
        ]);

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

    public function userHas(int $projectId, int $userId): bool
    {
        $consult = $this->context->prepare("
            SELECT COUNT(*) AS total
            FROM user_project
            WHERE user_id = :userId AND project_id = :projectId;
        ");

        $consult->execute([
            'projectId' => $projectId,
            'userId' => $userId
        ]);

        $response = $consult->fetch(PDO::FETCH_ASSOC);
        return $response['total'] > 0;
    }
}