<?php

namespace Models;

class TableModel
{
    private \PDO $pdo;

    public function __construct()
    {
        $dbFile = $_ENV['DB_FILE'];
        $dbPath = __DIR__ . '/../' . $dbFile;
        $this->pdo = new \PDO("sqlite:" . $dbPath);
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        // Prevenção de erros de concorrência
        $this->pdo->exec('PRAGMA journal_mode = WAL;');
        $this->pdo->exec('PRAGMA busy_timeout = 5000;');
    }

    // Retorna todas as mesas ativas de uma empresa
    public function getTables(int $companyId): array
    {
        $stmt = $this->pdo->prepare("
            SELECT table_id, table_number, status_id
            FROM `table`
            WHERE company_id = :company_id AND table_flag = 'a'
        ");
        $stmt->execute(['company_id' => $companyId]);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    // Cria uma nova mesa
    public function createTable(int $companyId, string $tableNumber, int $statusId): bool
    {
        $stmt = $this->pdo->prepare("
            INSERT INTO `table` (company_id, table_number, status_id, table_flag)
            VALUES (:company_id, :table_number, :status_id, 'a')
        ");
        return $stmt->execute([
            'company_id' => $companyId,
            'table_number' => $tableNumber,
            'status_id' => $statusId
        ]);
    }

    // Atualiza o status de uma mesa
    public function updateTable(int $tableId, int $companyId, int $statusId): bool
    {
        $stmt = $this->pdo->prepare("
            UPDATE `table`
            SET status_id = :status_id
            WHERE table_id = :table_id AND company_id = :company_id AND table_flag = 'a'
        ");
        return $stmt->execute([
            'status_id' => $statusId,
            'table_id' => $tableId,
            'company_id' => $companyId
        ]);
    }

    // Soft delete de uma mesa POR table_id (mantido para compatibilidade, opcional)
    public function deleteTable(int $tableId, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("
            UPDATE `table`
            SET table_flag = 'd'
            WHERE table_id = :table_id AND company_id = :company_id AND table_flag = 'a'
        ");
        return $stmt->execute([
            'table_id' => $tableId,
            'company_id' => $companyId
        ]);
    }

    // Soft delete de uma mesa POR table_number (novo método)
    public function deleteTableByNumber(string $tableNumber, int $companyId): bool
    {
        $stmt = $this->pdo->prepare("
            UPDATE `table`
            SET table_flag = 'd'
            WHERE table_number = :table_number AND company_id = :company_id AND table_flag = 'a'
        ");
        return $stmt->execute([
            'table_number' => $tableNumber,
            'company_id' => $companyId
        ]);
    }

    // Verifica se uma empresa existe e está ativa
    public function companyExists(int $companyId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM company WHERE company_id = :id AND company_flag = 'a'");
        $stmt->execute(['id' => $companyId]);
        return (bool) $stmt->fetch();
    }

    // Verifica se um status existe
    public function statusExists(int $statusId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM status WHERE status_id = :id");
        $stmt->execute(['id' => $statusId]);
        return (bool) $stmt->fetch();
    }

    public function getStatusIdByName(string $statusStatus): ?int
    {
        $stmt = $this->pdo->prepare("SELECT status_id FROM status WHERE status_status = :name");
        $stmt->execute(['name' => $statusStatus]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $result ? (int)$result['status_id'] : null;
    }

    public function updateTableByNumber(string $tableNumber, int $companyId, int $statusId): bool
{
    $stmt = $this->pdo->prepare("
        UPDATE `table`
        SET status_id = :status_id
        WHERE table_number = :table_number AND company_id = :company_id AND table_flag = 'a'
    ");
    return $stmt->execute([
        'status_id' => $statusId,
        'table_number' => $tableNumber,
        'company_id' => $companyId
    ]);
}

}