<?php

declare(strict_types=1);

namespace CloudXM\NFe\Persistence;

use CloudXM\SDK\Support\HasLogger;

/**
 * NFe Emissions Repository
 *
 * Manages NFe emission records in database
 */
class NfeEmissionsRepository
{
    use HasLogger;

    private string $table_prefix;

    /**
     * Constructor
     */
    public function __construct(string $table_prefix = 'nfe_')
    {
        $this->table_prefix = $table_prefix;
    }

    /**
     * Get table name
     */
    private function getTableName(): string
    {
        global $wpdb;
        return $wpdb->prefix . $this->table_prefix . 'emissions';
    }

    /**
     * Create new emission record
     *
     * @param array $data Emission data
     * @return int|false Emission ID or false on failure
     */
    public function create(array $data)
    {
        global $wpdb;

        $result = $wpdb->insert(
            $this->getTableName(),
            $data,
            $this->getFormats($data)
        );

        if ($result === false) {
            // Unique key conflict on (order_id, status) — delete stale row and retry
            $orderId = $data['order_id'] ?? null;
            $status = $data['status'] ?? null;

            if ($orderId !== null && $status !== null && (str_contains($wpdb->last_error, 'Duplicate') || str_contains($wpdb->last_error, '1062') || str_contains($wpdb->last_error, 'UNIQUE'))) {
                $this->log()->warning('nfe.emission.create_duplicate_key', [
                    'order_id' => $orderId,
                    'status' => $status,
                    'mysql_error' => $wpdb->last_error,
                ]);

                $wpdb->delete(
                    $this->getTableName(),
                    ['order_id' => $orderId, 'status' => $status],
                    ['%d', '%s']
                );

                $result = $wpdb->insert(
                    $this->getTableName(),
                    $data,
                    $this->getFormats($data)
                );
            }

            if ($result === false) {
                $this->log()->error('nfe.emission.create_failed', [
                    'data' => $data,
                    'error' => $wpdb->last_error,
                ]);
                return false;
            }
        }

        $emission_id = $wpdb->insert_id;

        $this->log()->info('nfe.emission.created', [
            'emission_id' => $emission_id,
            'order_id' => $data['order_id'] ?? null,
        ]);

        return $emission_id;
    }

    /**
     * Update emission record
     *
     * @param int $id Emission ID
     * @param array $data Data to update
     * @return bool True on success, false on failure
     */
    public function update(int $id, array $data): bool
    {
        global $wpdb;

        $result = $wpdb->update(
            $this->getTableName(),
            $data,
            ['id' => $id],
            $this->getFormats($data),
            ['%d']
        );

        if ($result === false) {
            // Unique key conflict — delete the conflicting row and retry
            $status = $data['status'] ?? null;
            $orderId = $data['order_id'] ?? null;

            if ($status !== null && (str_contains($wpdb->last_error, 'Duplicate') || str_contains($wpdb->last_error, '1062') || str_contains($wpdb->last_error, 'UNIQUE'))) {
                $this->log()->warning('nfe.emission.update_duplicate_key', [
                    'emission_id' => $id,
                    'status' => $status,
                    'mysql_error' => $wpdb->last_error,
                ]);

                // Delete the conflicting row (same order_id + status, different id)
                $wpdb->query($wpdb->prepare(
                    "DELETE FROM {$this->getTableName()} WHERE order_id = %d AND status = %s AND id != %d",
                    $orderId ?? 0,
                    $status,
                    $id
                ));

                $result = $wpdb->update(
                    $this->getTableName(),
                    $data,
                    ['id' => $id],
                    $this->getFormats($data),
                    ['%d']
                );
            }

            if ($result === false) {
                $this->log()->error('nfe.emission.update_failed', [
                    'emission_id' => $id,
                    'data' => $data,
                    'error' => $wpdb->last_error,
                ]);
                return false;
            }
        }

        $this->log()->debug('nfe.emission.updated', [
            'emission_id' => $id,
            'data' => $data,
        ]);

        return true;
    }

    /**
     * Get emission by ID
     *
     * @param int $id Emission ID
     * @return array|null Emission data or null if not found
     */
    public function findById(int $id): ?array
    {
        global $wpdb;

        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()} WHERE id = %d",
                $id
            ),
            ARRAY_A
        );

        return $row ?: null;
    }

    /**
     * Get emission by order ID
     *
     * @param int $orderId Order ID
     * @param bool $activeOnly Get only active emissions
     * @return array|null Emission data or null if not found
     */
    public function findByOrderId(int $orderId, bool $activeOnly = true): ?array
    {
        global $wpdb;

        $sql = "SELECT * FROM {$this->getTableName()} WHERE order_id = %d";
        $params = [$orderId];

        if ($activeOnly) {
            $sql .= " AND status IN ('pending', 'processing', 'authorized', 'cancelled', 'issued', 'scheduled', 'async_pending')";
        }

        $sql .= " ORDER BY id DESC LIMIT 1";

        $row = $wpdb->get_row(
            $wpdb->prepare($sql, ...$params),
            ARRAY_A
        );

        return $row ?: null;
    }

    /**
     * Get emission by invoice ID
     *
     * @param string $invoiceId Invoice ID from NFE server
     * @return array|null Emission data or null if not found
     */
    public function findByInvoiceId(string $invoiceId): ?array
    {
        global $wpdb;

        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()} WHERE invoice_id = %s",
                $invoiceId
            ),
            ARRAY_A
        );

        return $row ?: null;
    }

    /**
     * Get emission by access key
     *
     * @param string $accessKey NFe access key (chave de acesso)
     * @return array|null Emission data or null if not found
     */
    public function findByAccessKey(string $accessKey): ?array
    {
        global $wpdb;

        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()} WHERE access_key = %s",
                $accessKey
            ),
            ARRAY_A
        );

        return $row ?: null;
    }

    /**
     * Get emissions by status
     *
     * @param string $status Emission status
     * @param int $limit Maximum number of records
     * @param int $offset Offset for pagination
     * @return array List of emissions
     */
    public function findByStatus(string $status, int $limit = 50, int $offset = 0): array
    {
        global $wpdb;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()} 
                 WHERE status = %s 
                 ORDER BY created_at DESC 
                 LIMIT %d OFFSET %d",
                $status,
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return $results ?: [];
    }

    /**
     * Get emissions by environment
     *
     * @param string $environment Environment (production|homologation)
     * @param int $limit Maximum number of records
     * @param int $offset Offset for pagination
     * @return array List of emissions
     */
    public function findByEnvironment(string $environment, int $limit = 50, int $offset = 0): array
    {
        global $wpdb;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()} 
                 WHERE environment = %s 
                 ORDER BY created_at DESC 
                 LIMIT %d OFFSET %d",
                $environment,
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return $results ?: [];
    }

    /**
     * Get emissions by date range
     *
     * @param \DateTime $startDate Start date
     * @param \DateTime $endDate End date
     * @param int $limit Maximum number of records
     * @param int $offset Offset for pagination
     * @return array List of emissions
     */
    public function findByDateRange(\DateTime $startDate, \DateTime $endDate, int $limit = 50, int $offset = 0): array
    {
        global $wpdb;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()} 
                 WHERE emission_date BETWEEN %s AND %s 
                 ORDER BY emission_date DESC 
                 LIMIT %d OFFSET %d",
                $startDate->format('Y-m-d H:i:s'),
                $endDate->format('Y-m-d H:i:s'),
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return $results ?: [];
    }

    /**
     * Get emissions by consumer document
     *
     * @param string $document Consumer document (CPF/CNPJ)
     * @param int $limit Maximum number of records
     * @param int $offset Offset for pagination
     * @return array List of emissions
     */
    public function findByConsumerDocument(string $document, int $limit = 50, int $offset = 0): array
    {
        global $wpdb;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()} 
                 WHERE consumer_id = %s 
                 ORDER BY created_at DESC 
                 LIMIT %d OFFSET %d",
                $document,
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return $results ?: [];
    }

    /**
     * Get all emissions with pagination
     *
     * @param int $limit Maximum number of records
     * @param int $offset Offset for pagination
     * @return array List of emissions
     */
    public function findAll(int $limit = 50, int $offset = 0): array
    {
        global $wpdb;

        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$this->getTableName()}
                 ORDER BY created_at DESC
                 LIMIT %d OFFSET %d",
                $limit,
                $offset
            ),
            ARRAY_A
        );

        return $results ?: [];
    }

    /**
     * Get emissions with conditions and pagination
     *
     * @param array $conditions WHERE conditions (key => value)
     * @param int $limit Maximum number of records (0 for no limit)
     * @param int $offset Offset for pagination
     * @return array List of emissions
     */
    public function findByConditions(array $conditions = [], int $limit = 50, int $offset = 0): array
    {
        global $wpdb;

        $where = ['1=1'];
        $params = [];

        foreach ($conditions as $field => $value) {
            [$column, $operator] = $this->parseConditionKey((string) $field);
            if ($column === '') {
                continue;
            }
            $where[] = "{$column} {$operator} %s";
            $params[] = (string) $value;
        }

        $whereClause = implode(' AND ', $where);

        $limitClause = $limit > 0 ? "LIMIT %d OFFSET %d" : "";
        if ($limit > 0) {
            $params[] = $limit;
            $params[] = $offset;
        }

        $sql = "SELECT * FROM {$this->getTableName()}
                 WHERE {$whereClause}
                 ORDER BY created_at DESC
                 {$limitClause}";

        if (!empty($params)) {
            $sql = $wpdb->prepare($sql, ...$params);
        }

        $results = $wpdb->get_results($sql, ARRAY_A);

        return $results ?: [];
    }

    /**
     * Count emissions with conditions
     *
     * @param array $conditions WHERE conditions (key => value)
     * @return int Number of emissions
     */
    public function countByConditions(array $conditions = []): int
    {
        global $wpdb;

        $where = ['1=1'];
        $params = [];

        foreach ($conditions as $field => $value) {
            [$column, $operator] = $this->parseConditionKey((string) $field);
            if ($column === '') {
                continue;
            }
            $where[] = "{$column} {$operator} %s";
            $params[] = (string) $value;
        }

        $whereClause = implode(' AND ', $where);

        $sql = "SELECT COUNT(*) FROM {$this->getTableName()} WHERE {$whereClause}";

        if (!empty($params)) {
            $sql = $wpdb->prepare($sql, ...$params);
        }

        return (int) $wpdb->get_var($sql);
    }

    /**
     * Parse condition key supporting operators in keys like "emission_date >=".
     *
     * @return array{0:string,1:string}
     */
    private function parseConditionKey(string $field): array
    {
        $field = trim($field);
        if ($field === '') {
            return ['', '='];
        }

        $operator = '=';
        $column = $field;

        if (preg_match('/^([a-zA-Z0-9_]+)\s*(>=|<=|!=|>|<|LIKE)$/i', $field, $matches)) {
            $column = $matches[1];
            $operator = strtoupper($matches[2]);
        }

        if (!preg_match('/^[a-zA-Z0-9_]+$/', $column)) {
            return ['', '='];
        }

        return [$column, $operator];
    }

    /**
     * Count emissions by status
     *
     * @param string $status Emission status
     * @return int Number of emissions
     */
    public function countByStatus(string $status): int
    {
        global $wpdb;

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->getTableName()} WHERE status = %s",
                $status
            )
        );
    }

    /**
     * Count all emissions
     *
     * @return int Total number of emissions
     */
    public function countAll(): int
    {
        global $wpdb;

        return (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$this->getTableName()}"
        );
    }

    /**
     * Get emissions statistics
     *
     * @param string $period Time period (today, week, month, year, all)
     * @return array Statistics data
     */
    public function getStatistics(string $period = 'month'): array
    {
        global $wpdb;

        $dateCondition = $this->getDateCondition($period);

        $stats = $wpdb->get_row(
            "SELECT 
                COUNT(*) as total,
                COUNT(CASE WHEN status = 'authorized' THEN 1 END) as authorized,
                COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled,
                COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending,
                COUNT(CASE WHEN status = 'processing' THEN 1 END) as processing,
                COUNT(CASE WHEN status = 'error' THEN 1 END) as error,
                SUM(total_value) as total_value
            FROM {$this->getTableName()}
            {$dateCondition}",
            ARRAY_A
        );

        return $stats ?: [];
    }

    /**
     * Get date condition for statistics
     *
     * @param string $period Time period
     * @return string SQL WHERE condition
     */
    private function getDateCondition(string $period): string
    {
        switch ($period) {
            case 'today':
                return "WHERE DATE(created_at) = CURDATE()";
            case 'week':
                return "WHERE YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1)";
            case 'month':
                return "WHERE MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE())";
            case 'year':
                return "WHERE YEAR(created_at) = YEAR(CURDATE())";
            case 'all':
            default:
                return "";
        }
    }

    /**
     * Delete emission by ID
     *
     * @param int $id Emission ID
     * @return bool True on success, false on failure
     */
    public function delete(int $id): bool
    {
        global $wpdb;

        $result = $wpdb->delete(
            $this->getTableName(),
            ['id' => $id],
            ['%d']
        );

        if ($result === false) {
            $this->log()->error('nfe.emission.delete_failed', [
                'emission_id' => $id,
                'error' => $wpdb->last_error,
            ]);
            return false;
        }

        $this->log()->info('nfe.emission.deleted', [
            'emission_id' => $id,
        ]);

        return true;
    }

    /**
     * Delete all emission records for a given order
     */
    public function deleteAllByOrderId(int $orderId): int
    {
        global $wpdb;

        $deleted = $wpdb->delete(
            $this->getTableName(),
            ['order_id' => $orderId],
            ['%d']
        );

        if ($deleted === false) {
            $this->log()->error('nfe.emission.delete_all_failed', [
                'order_id' => $orderId,
                'error' => $wpdb->last_error,
            ]);
            return 0;
        }

        $this->log()->info('nfe.emission.deleted_all_for_order', [
            'order_id' => $orderId,
            'count' => $deleted,
        ]);

        return (int) $deleted;
    }

    /**
     * Get format placeholders for data
     *
     * @param array $data Data array
     * @return array Format placeholders
     */
    private function getFormats(array $data): array
    {
        $formats = [];
        
        foreach ($data as $key => $value) {
            if (is_int($value)) {
                $formats[] = '%d';
            } elseif (is_float($value)) {
                $formats[] = '%f';
            } else {
                $formats[] = '%s';
            }
        }
        
        return $formats;
    }
}
