<?php

namespace App\Repositories;

use PDO;
use App\Config\DatasourceConfig;
use App\Helpers\TicketMapper;

final class TicketRepo
{
  private PDO $connection;
  private TicketMapper $mapper;

  public function __construct()
  {
    $this->connection = (new DatasourceConfig())->connect();
    $this->mapper = new TicketMapper();
  }

  private string $querySelect = "
        SELECT
            t.id_ticket,

            u.id_user,
            u.nama,

            c.id_kategori,
            c.nama_kategori,

            p.id_prioritas,
            p.nama_prioritas,

            s.id_sla,
            s.nama_sla,

            st.id_status,
            st.nama_status,

            t.judul,
            t.deskripsi,
            t.tanggal_dibuat,
            t.tanggal_update,
            t.deadline,
            t.is_closed,
            t.nomor_tiket

        FROM tickets t

        INNER JOIN users u
            ON u.id_user = t.id_user

        INNER JOIN categories c
            ON c.id_kategori = t.id_kategori

        LEFT JOIN priorities p
            ON p.id_prioritas = t.id_prioritas

        LEFT JOIN sla s
            ON s.id_sla = t.id_sla

        INNER JOIN status_ticket st
            ON st.id_status = t.id_status
    ";

  public function findAll()
  {
    $statement = $this->connection->prepare(
      $this->querySelect . " ORDER BY t.tanggal_dibuat DESC"
    );

    $statement->execute();

    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

    return array_map([$this->mapper, 'toTicket'], $rows);
  }

  public function findBy(
    ?string $keyword,
    ?string $status,
    ?string $prioritas,
    ?string $kategori,
    ?string $sla
  ) {
    $querySearch = $this->querySelect . "
      WHERE
      (
        (:keywordNull IS NULL OR :keywordNull = '')
        OR t.nomor_tiket LIKE CONCAT('%', :keyword, '%')
        OR t.judul LIKE CONCAT('%', :keyword, '%')
        OR t.deskripsi LIKE CONCAT('%', :keyword, '%')
        OR u.nama LIKE CONCAT('%', :keyword, '%')
      )
      AND (
        (:statusNull IS NULL OR :statusNull = '')
        OR st.id_status = :status
      )
      AND (
        (:prioritasNull IS NULL OR :prioritasNull = '')
        OR p.id_prioritas = :prioritas
      )
      AND (
        (:kategoriNull IS NULL OR :kategoriNull = '')
        OR c.id_kategori = :kategori
      )
      AND (
        (:slaNull IS NULL OR :slaNull = '')
        OR s.id_sla = :sla
      )
        ORDER BY t.tanggal_dibuat DESC
      ";
    $statement = $this->connection->prepare($querySearch);
    $statement->execute([
      // keyword
      'keywordNull' => $keyword ?? null,
      'keyword' => $keyword,
      // status
      'statusNull' => $status ?? null,
      'status' => $status,
      // prioritas
      'prioritasNull' => $prioritas ?? null,
      'prioritas' => $prioritas,
      // kategori
      'kategoriNull' => $kategori ?? null,
      'kategori' => $kategori,
      // sla
      'slaNull' => $sla ?? null,
      'sla' => $sla,
    ]);
    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);
    return array_map(
      [$this->mapper, 'toTicket'],
      $rows
    );
  }

  public function findById(int $id)
  {
    $statement = $this->connection->prepare(
      $this->querySelect . " WHERE t.id_ticket = :id"
    );

    $statement->execute([
      'id' => $id
    ]);

    $row = $statement->fetch(PDO::FETCH_ASSOC);

    if (empty($row)) {
      return null;
    }

    return $this->mapper->toTicket($row);
  }

  public function save(
    PDO $connection,
    int $userId,
    int $kategoriId,
    int $prioritasId,
    int $slaId,
    int $statusId,
    string $judul,
    ?string $deskripsi,
    ?string $deadline,
    string $nomorTiket
  ): int {
    $query = "
            INSERT INTO tickets (
                id_user,
                id_kategori,
                id_prioritas,
                id_sla,
                id_status,
                judul,
                deskripsi,
                deadline,
                nomor_tiket
            )
            VALUES (
                :userId,
                :kategoriId,
                :prioritasId,
                :slaId,
                :statusId,
                :judul,
                :deskripsi,
                :deadline,
                :nomorTiket
            );
        ";

    $statement = $connection->prepare($query);

    $statement->execute([
      'userId' => $userId,
      'kategoriId' => $kategoriId,
      'prioritasId' => $prioritasId,
      'slaId' => $slaId,
      'statusId' => $statusId,
      'judul' => $judul,
      'deskripsi' => $deskripsi,
      'deadline' => $deadline,
      'nomorTiket' => $nomorTiket,
    ]);

    return (int) $connection->lastInsertId();
  }

  public function update(
    int $id,
    int $kategoriId,
    int $prioritasId,
    int $slaId,
    int $statusId,
    string $judul,
    ?string $deskripsi,
    ?string $deadline,
    bool $isClosed
  ) {
    $query = "
            UPDATE tickets
            SET
                id_kategori = :kategoriId,
                id_prioritas = :prioritasId,
                id_sla = :slaId,
                id_status = :statusId,
                judul = :judul,
                deskripsi = :deskripsi,
                deadline = :deadline,
                is_closed = :isClosed
            WHERE id_ticket = :id;
        ";

    $statement = $this->connection->prepare($query);

    $statement->execute([
      'id' => $id,
      'kategoriId' => $kategoriId,
      'prioritasId' => $prioritasId,
      'slaId' => $slaId,
      'statusId' => $statusId,
      'judul' => $judul,
      'deskripsi' => $deskripsi,
      'deadline' => $deadline,
      'isClosed' => $isClosed
    ]);
  }

  public function delete(int $id)
  {
    $statement = $this->connection->prepare(
      "DELETE FROM tickets WHERE id_ticket = :id"
    );

    $statement->execute([
      'id' => $id
    ]);
  }

  public function findByStatus(int $statusId)
  {
    $statement = $this->connection->prepare(
      $this->querySelect .
        " WHERE t.id_status = :statusId
              ORDER BY t.tanggal_dibuat DESC"
    );

    $statement->execute([
      'statusId' => $statusId
    ]);

    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

    return array_map([$this->mapper, 'toTicket'], $rows);
  }

  public function findByUser(int $userId)
  {
    $statement = $this->connection->prepare(
      $this->querySelect .
        " WHERE t.id_user = :userId
              ORDER BY t.tanggal_dibuat DESC"
    );

    $statement->execute([
      'userId' => $userId
    ]);

    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

    return array_map([$this->mapper, 'toTicket'], $rows);
  }

  public function getLastTicketNumber(PDO $connection): int
  {
    $query = "
        SELECT nomor_tiket
        FROM tickets
        WHERE nomor_tiket LIKE :prefix
        ORDER BY id_ticket DESC
        LIMIT 1
    ";

    $statement = $connection->prepare($query);

    $prefix = 'TKT-' . date('Ymd') . '-%';

    $statement->execute([
      'prefix' => $prefix
    ]);

    $nomor = $statement->fetchColumn();

    if (!$nomor) {
      return 1;
    }

    return (int) substr($nomor, -5) + 1;
  }

  // update status
  public function updateStatus(int $id, int $statusId, ?PDO $connection = null)
  {
    $connect = $connection ?? $this->connection;
    $statement = $connect->prepare(
      "UPDATE tickets SET id_status = :statusId WHERE id_ticket = :id"
    );

    $statement->execute([
      'id' => $id,
      'statusId' => $statusId
    ]);
  }

  public function close(int $id, int $statusId, ?PDO $connection = null)
  {
    $statement = $connection->prepare(
      "UPDATE tickets 
      SET id_status = :statusId,
      is_closed = 1
      WHERE id_ticket = :id"
    );

    $statement->execute([
      'id' => $id,
      'statusId' => $statusId
    ]);
  }

  // find by date range
  public function findByDateRange(string $startDate, string $endDate, ?bool $active = false)
  {
    $statement = $this->connection->prepare(
      $this->querySelect .
        " WHERE t.tanggal_dibuat BETWEEN :startDate AND :endDate
              AND t.is_closed = :closed
              ORDER BY t.tanggal_dibuat DESC"
    );

    $statement->execute([
      'startDate' => $startDate,
      'endDate' => $endDate,
      'closed' => !$active
    ]);

    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

    return array_map([$this->mapper, 'toTicket'], $rows);
  }

  public function findOpenTicket(string $startDate, string $endDate)
  {
    $statement = $this->connection->prepare(
      $this->querySelect . " WHERE t.tanggal_dibuat BETWEEN :startDate AND :endDate 
          AND t.is_closed = 0 ORDER BY t.tanggal_dibuat DESC"
    );

    $statement->execute([
      'startDate' => $startDate,
      'endDate' => $endDate
    ]);

    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

    return array_map([$this->mapper, 'toTicket'], $rows);
  }

  public function findCompletedSla(string $startDate, string $endDate, int $statusId): array
  {

    $query = "SELECT
      t.id_ticket AS id,
      t.tanggal_dibuat AS tanggal_dibuat,
      ta.tanggal_selesai AS tanggal_selesai,
      s.waktu_penyelesaian_jam AS waktu_penyelesaian_jam
    FROM tickets t
    INNER JOIN sla s
        ON s.id_sla = t.id_sla
    LEFT JOIN ticket_assignments ta
        ON ta.id_ticket = t.id_ticket
    WHERE t.tanggal_dibuat BETWEEN :startDate AND :endDate 
    AND t.id_status = :statusId";

    $statement = $this->connection->prepare($query);

    $statement->execute([
      'startDate' => $startDate,
      'endDate' => $endDate,
      'statusId' => $statusId
    ]);

    $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

    return $rows;
  }

  public function updateSlaTicket(
    int $idTicket,
    int $idSla,
    int $idPrioritas,
    string $deadline
  ): bool {

    $sql = "
        UPDATE tickets
        SET 
            id_sla = :id_sla,
            id_prioritas = :id_prioritas,
            deadline = :deadline
        WHERE id_ticket = :id_ticket
    ";

    $stmt = $this->connection->prepare($sql);

    return $stmt->execute([
      'id_sla' => $idSla,
      'id_prioritas' => $idPrioritas,
      'id_ticket' => $idTicket,
      'deadline' => $deadline
    ]);
  }
}
