<?php
declare(strict_types=1);

namespace Infolab\Controllers;

use Infolab\ActivityLogger;
use Infolab\Database;
use Infolab\Request;
use Infolab\Response;
use Infolab\Slugger;
use Infolab\Validator;

/**
 * CRUD projets — entité centrale. Soft delete via `deleted_at`.
 * Les sous-ressources (roadmap, members, discussions, resources) ont leurs
 * propres contrôleurs, mais s'appuient sur ProjectsController::findOr404().
 */
final class ProjectsController
{
    public function index(Request $req, array $params): void
    {
        $page    = max(1, (int) $req->query('page', 1));
        $perPage = max(1, min(100, (int) $req->query('per_page', 20)));
        $offset  = ($page - 1) * $perPage;

        // Construction dynamique du WHERE — chaque valeur est bindée.
        $where = ['p.deleted_at IS NULL'];
        $args  = [];

        if (null !== ($cat = $req->query('category')) && $cat !== '') {
            $where[]      = 'c.slug = :cat';
            $args[':cat'] = (string) $cat;
        }
        if (null !== ($status = $req->query('status')) && $status !== '') {
            if (!in_array($status, ['active', 'completed', 'archived'], true)) {
                throw new \ApiException(400, ['error' => 'bad_request', 'message' => 'status invalide']);
            }
            $where[]         = 'p.status = :status';
            $args[':status'] = (string) $status;
        }
        if (null !== ($q = $req->query('q')) && $q !== '') {
            // Échappe les wildcards SQL avant le LIKE.
            $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], (string) $q) . '%';
            $where[]    = '(p.title LIKE :q OR p.short_description LIKE :q)';
            $args[':q'] = $like;
        }
        $whereSql = implode(' AND ', $where);

        $total = (int) Database::fetchValue(
            "SELECT COUNT(*) FROM projects p INNER JOIN categories c ON c.id = p.category_id WHERE $whereSql",
            $args,
        );
        $rows = Database::fetchAll(
            "SELECT p.id, p.slug, p.title, p.status, p.territory, p.short_description,
                    p.cover_image_url, p.external_url, p.progress_percent,
                    p.started_at, p.completed_at, p.created_at, p.updated_at,
                    c.id  AS category_id,   c.slug AS category_slug, c.name AS category_name,
                    c.color_hex AS category_color_hex, c.dimension AS category_dimension
             FROM projects p
             INNER JOIN categories c ON c.id = p.category_id
             WHERE $whereSql
             ORDER BY p.created_at DESC, p.id DESC
             LIMIT $perPage OFFSET $offset",
            $args,
        );

        Response::json([
            'data' => array_map([self::class, 'presentListRow'], $rows),
            'meta' => [
                'page'        => $page,
                'per_page'    => $perPage,
                'total'       => $total,
                'total_pages' => $total === 0 ? 0 : (int) ceil($total / $perPage),
            ],
        ]);
    }

    public function show(Request $req, array $params): void
    {
        Response::json(self::presentDetail(self::findOr404($params['slug'])));
    }

    public function store(Request $req, array $params): void
    {
        $body = $req->json();
        $v = new Validator($body);
        $v->required('title')->string('title', 200);
        $v->required('category_id')->integer('category_id', 1);
        $v->string('slug', 160);
        $v->in('status', ['active', 'completed', 'archived']);
        $v->string('territory', 160);
        $v->string('short_description', 5000);
        $v->string('context_description');
        $v->string('objectives');
        $v->url('cover_image_url', 512);
        $v->url('external_url', 512);
        $v->integer('progress_percent', 0, 100);
        $v->date('started_at');
        $v->date('completed_at');

        // Lookup category_id seulement si le format est OK pour éviter le bruit.
        if ($v->has('category_id') && !$v->fails()) {
            $exists = Database::fetchValue(
                'SELECT 1 FROM categories WHERE id = :id',
                [':id' => (int) $v->value('category_id')],
            );
            if ($exists === null) {
                $v->setError('category_id', 'not_found');
            }
        }
        $v->check();

        $title = (string) $body['title'];
        $base  = (isset($body['slug']) && $body['slug'] !== '')
            ? Slugger::slugify((string) $body['slug'])
            : Slugger::slugify($title);
        $slug = Slugger::unique($base, 'projects');

        $id = Database::insert('projects', [
            'slug'                => $slug,
            'title'               => $title,
            'category_id'         => (int) $body['category_id'],
            'status'              => $body['status'] ?? 'active',
            'territory'           => $body['territory'] ?? 'Marseille',
            'short_description'   => $body['short_description'] ?? null,
            'context_description' => $body['context_description'] ?? null,
            'objectives'          => $body['objectives'] ?? null,
            'cover_image_url'     => $body['cover_image_url'] ?? null,
            'external_url'        => $body['external_url'] ?? null,
            'progress_percent'    => isset($body['progress_percent']) ? (int) $body['progress_percent'] : 0,
            'started_at'          => $body['started_at'] ?? null,
            'completed_at'        => $body['completed_at'] ?? null,
        ]);

        ActivityLogger::log(
            'created_project',
            sprintf('a créé le projet « %s »', $title),
            $id,
        );

        Response::json(self::presentDetail(self::findOr404($slug)), 201);
    }

    public function update(Request $req, array $params): void
    {
        $p = self::findOr404($params['slug']);
        $body = $req->json();
        $v = new Validator($body);
        $v->string('title', 200);
        $v->string('slug', 160);
        $v->integer('category_id', 1);
        $v->in('status', ['active', 'completed', 'archived']);
        $v->string('territory', 160);
        $v->string('short_description', 5000);
        $v->string('context_description');
        $v->string('objectives');
        $v->url('cover_image_url', 512);
        $v->url('external_url', 512);
        $v->integer('progress_percent', 0, 100);
        $v->date('started_at');
        $v->date('completed_at');

        if (array_key_exists('category_id', $body) && !$v->fails()) {
            $exists = Database::fetchValue(
                'SELECT 1 FROM categories WHERE id = :id',
                [':id' => (int) $body['category_id']],
            );
            if ($exists === null) {
                $v->setError('category_id', 'not_found');
            }
        }
        $v->check();

        $update = [];
        $fields = ['title','category_id','status','territory','short_description','context_description',
                   'objectives','cover_image_url','external_url','progress_percent','started_at','completed_at'];
        foreach ($fields as $f) {
            if (array_key_exists($f, $body)) {
                $update[$f] = $body[$f];
            }
        }
        if (isset($update['progress_percent'])) $update['progress_percent'] = (int) $update['progress_percent'];
        if (isset($update['category_id']))      $update['category_id']      = (int) $update['category_id'];

        if (isset($body['slug']) && $body['slug'] !== '') {
            $update['slug'] = Slugger::unique(
                Slugger::slugify((string) $body['slug']),
                'projects',
                'slug',
                (int) $p['id'],
            );
        }

        // Transition status -> completed : remplit completed_at si vide.
        $becameCompleted = false;
        if (isset($update['status']) && $update['status'] === 'completed' && $p['status'] !== 'completed') {
            $becameCompleted = true;
            if (!array_key_exists('completed_at', $update) || $update['completed_at'] === null || $update['completed_at'] === '') {
                $update['completed_at'] = gmdate('Y-m-d');
            }
        }

        if ($update !== []) {
            Database::update('projects', $update, 'id = :id', [':id' => (int) $p['id']]);
        }

        $title = $update['title'] ?? $p['title'];
        ActivityLogger::log(
            'updated_project',
            sprintf('a mis à jour le projet « %s »', $title),
            (int) $p['id'],
        );
        if ($becameCompleted) {
            ActivityLogger::log(
                'completed_project',
                sprintf('a terminé le projet « %s »', $title),
                (int) $p['id'],
            );
        }

        Response::json(self::presentDetail(self::findOr404($update['slug'] ?? $p['slug'])));
    }

    public function destroy(Request $req, array $params): void
    {
        $p = self::findOr404($params['slug']);
        // Soft delete : on garde l'historique, on cache de la liste publique.
        Database::update(
            'projects',
            ['deleted_at' => gmdate('Y-m-d H:i:s')],
            'id = :id',
            [':id' => (int) $p['id']],
        );
        Response::noContent();
    }

    /**
     * Helper public : récupère un projet non soft-deleted par son slug, ou 404.
     * Utilisé par tous les sous-contrôleurs (Roadmap, ProjectMembers, etc.).
     *
     * @return array<string,mixed>
     */
    public static function findOr404(string $slug): array
    {
        $row = Database::fetchOne(
            'SELECT * FROM projects WHERE slug = :slug AND deleted_at IS NULL',
            [':slug' => $slug],
        );
        if ($row === null) {
            throw new \ApiException(404, ['error' => 'not_found']);
        }
        return $row;
    }

    /** Ligne de liste (projet + catégorie jointe). */
    private static function presentListRow(array $row): array
    {
        return [
            'id'                => (int) $row['id'],
            'slug'              => $row['slug'],
            'title'             => $row['title'],
            'status'            => $row['status'],
            'territory'         => $row['territory'],
            'short_description' => $row['short_description'],
            'cover_image_url'   => $row['cover_image_url'],
            'external_url'      => $row['external_url'],
            'progress_percent'  => (int) $row['progress_percent'],
            'started_at'        => $row['started_at'],
            'completed_at'      => $row['completed_at'],
            'created_at'        => $row['created_at'],
            'updated_at'        => $row['updated_at'],
            'category'          => [
                'id'        => (int) $row['category_id'],
                'slug'      => $row['category_slug'],
                'name'      => $row['category_name'],
                'color_hex' => $row['category_color_hex'],
                'dimension' => $row['category_dimension'],
            ],
        ];
    }

    /** Détail complet d'un projet : catégorie, membres, roadmap, compteurs. */
    private static function presentDetail(array $p): array
    {
        $pid = (int) $p['id'];

        $cat = Database::fetchOne(
            'SELECT id, slug, name, color_hex, icon, dimension FROM categories WHERE id = :id',
            [':id' => (int) $p['category_id']],
        );

        $members = Database::fetchAll(
            'SELECT m.id, m.slug, m.display_name, m.initials, m.kind, m.avatar_url,
                    pm.role, pm.joined_at
             FROM project_members pm
             INNER JOIN members m ON m.id = pm.member_id
             WHERE pm.project_id = :pid
             ORDER BY pm.joined_at ASC',
            [':pid' => $pid],
        );

        $roadmap = Database::fetchAll(
            'SELECT id, title, description, step_date, is_done, sort_order, created_at, updated_at
             FROM roadmap_steps WHERE project_id = :pid ORDER BY sort_order ASC, id ASC',
            [':pid' => $pid],
        );
        foreach ($roadmap as &$s) {
            $s['id']         = (int) $s['id'];
            $s['is_done']    = (bool) $s['is_done'];
            $s['sort_order'] = (int) $s['sort_order'];
        }
        unset($s);

        $discCount = (int) Database::fetchValue('SELECT COUNT(*) FROM discussions WHERE project_id = :pid', [':pid' => $pid]);
        $resCount  = (int) Database::fetchValue('SELECT COUNT(*) FROM resources   WHERE project_id = :pid', [':pid' => $pid]);

        return [
            'id'                  => $pid,
            'slug'                => $p['slug'],
            'title'               => $p['title'],
            'status'              => $p['status'],
            'territory'           => $p['territory'],
            'short_description'   => $p['short_description'],
            'context_description' => $p['context_description'],
            'objectives'          => $p['objectives'],
            'cover_image_url'     => $p['cover_image_url'],
            'external_url'        => $p['external_url'],
            'progress_percent'    => (int) $p['progress_percent'],
            'started_at'          => $p['started_at'],
            'completed_at'        => $p['completed_at'],
            'created_at'          => $p['created_at'],
            'updated_at'          => $p['updated_at'],
            'category'            => $cat === null ? null : [
                'id'        => (int) $cat['id'],
                'slug'      => $cat['slug'],
                'name'      => $cat['name'],
                'color_hex' => $cat['color_hex'],
                'icon'      => $cat['icon'],
                'dimension' => $cat['dimension'],
            ],
            'members' => array_map(static fn(array $m): array => [
                'id'           => (int) $m['id'],
                'slug'         => $m['slug'],
                'display_name' => $m['display_name'],
                'initials'     => $m['initials'],
                'kind'         => $m['kind'],
                'avatar_url'   => $m['avatar_url'],
                'role'         => $m['role'],
                'joined_at'    => $m['joined_at'],
            ], $members),
            'roadmap' => $roadmap,
            'counts'  => [
                'discussions' => $discCount,
                'resources'   => $resCount,
                'members'     => count($members),
            ],
        ];
    }
}