<?php
namespace App\Services;

use App\Database\Connection;
use PDO;
use RuntimeException;

class DatabaseMaintenanceService
{
    private Connection $db;
    private PDO $pdo;
    private string $backupDir;

    public function __construct(Connection $db, ?string $backupDir = null)
    {
        $this->db = $db;
        $this->pdo = $db->getPdo();
        $this->backupDir = $backupDir ?: dirname(__DIR__, 2) . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR . 'backups' . DIRECTORY_SEPARATOR . 'db';
    }

    public function dashboard(): array
    {
        return [
            'health' => $this->healthCheck(),
            'backups' => $this->listBackups(),
            'backup_dir' => $this->backupDir,
        ];
    }

    public function healthCheck(): array
    {
        $tables = $this->loadTables();
        $expectedTables = [
            'payment_records',
            'payment_record_links',
            'payment_events',
            'payment_refunds',
            'commission_lines',
            'commission_payout_cycles',
            'commission_payout_cycle_lines',
            'user_hierarchy_assignments',
        ];
        $expectedColumns = [
            'payment_records' => ['id', 'payment_uuid', 'deal_id', 'source_type', 'source_hash', 'amount', 'effective_date', 'payment_owner_user_id', 'payment_owner_didar_id'],
            'payment_record_links' => ['id', 'payment_record_id', 'linked_table', 'linked_id', 'link_type'],
            'transactions' => [
                'payment_record_id',
                'payment_uuid',
                'payment_owner_user_id',
                'commission_status',
                'destination_tafsili_code',
                'payment_screen_at',
                'payment_screen_date',
                'is_cheque_payment',
            ],
            'commission_lines' => ['id', 'source_hash', 'payment_record_id', 'beneficiary_user_id', 'calculated_commission', 'status', 'beneficiary_type'],
            'commission_payout_cycles' => ['id', 'user_id', 'billing_month', 'status', 'final_payable_amount'],
            'commission_payout_cycle_lines' => ['id', 'payout_cycle_id', 'commission_line_id'],
            'user_hierarchy_assignments' => ['id', 'user_id', 'supervisor_user_id', 'manager_user_id', 'effective_from'],
        ];

        $tableStatus = [];
        foreach ($expectedTables as $table) {
            $exists = isset($tables[$table]);
            $tableStatus[$table] = [
                'exists' => $exists,
                'rows' => $exists ? $this->countRows($table) : null,
            ];
        }

        $columnStatus = [];
        foreach ($expectedColumns as $table => $columns) {
            $present = isset($tables[$table]) ? $this->db->getTableColumns($table) : [];
            $missing = [];
            foreach ($columns as $column) {
                if (!isset($present[$column])) {
                    $missing[] = $column;
                }
            }
            $columnStatus[$table] = [
                'exists' => isset($tables[$table]),
                'missing' => $missing,
                'ok' => empty($missing),
            ];
        }

        $missingTables = array_values(array_filter($expectedTables, static fn($table) => !isset($tables[$table])));
        $missingColumns = [];
        foreach ($columnStatus as $table => $status) {
            foreach ($status['missing'] as $column) {
                $missingColumns[] = $table . '.' . $column;
            }
        }

        return [
            'ok' => empty($missingTables) && empty($missingColumns),
            'checked_at' => date('c'),
            'database' => (string)$this->pdo->query('SELECT DATABASE()')->fetchColumn(),
            'tables' => $tableStatus,
            'columns' => $columnStatus,
            'summary' => [
                'missing_tables' => count($missingTables),
                'missing_columns' => count($missingColumns),
                'missing_table_names' => $missingTables,
                'missing_column_names' => $missingColumns,
            ],
        ];
    }

    public function createBackup(string $label = 'manual'): array
    {
        $this->ensureLongRunningRequest();
        $this->ensureBackupDir();

        $label = preg_replace('/[^a-zA-Z0-9_-]+/', '-', trim($label));
        $label = trim((string)$label, '-_');
        if ($label === '') {
            $label = 'manual';
        }

        $database = (string)$this->pdo->query('SELECT DATABASE()')->fetchColumn();
        $compressed = function_exists('gzopen') && function_exists('gzwrite') && function_exists('gzclose');
        $filename = 'saleos-db-' . $label . '-' . date('Ymd-His') . ($compressed ? '.sql.gz' : '.sql');
        $path = $this->backupDir . DIRECTORY_SEPARATOR . $filename;
        $handle = $compressed ? gzopen($path, 'wb6') : fopen($path, 'wb');
        if (!$handle) {
            throw new RuntimeException('امکان ساخت فایل بکاپ وجود ندارد');
        }

        $report = [
            'filename' => $filename,
            'path' => $path,
            'database' => $database,
            'created_at' => date('c'),
            'tables' => [],
            'total_rows' => 0,
            'size_bytes' => 0,
        ];

        try {
            $this->writeDump($handle, "-- SaleOS database backup\n", $compressed);
            $this->writeDump($handle, "-- Created at: " . date('c') . "\n", $compressed);
            $this->writeDump($handle, "-- Database: " . $database . "\n\n", $compressed);
            $this->writeDump($handle, "SET FOREIGN_KEY_CHECKS=0;\n", $compressed);
            $this->writeDump($handle, "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n\n", $compressed);

            foreach (array_keys($this->loadTables()) as $table) {
                $tableRows = $this->dumpTable($handle, $table, $compressed);
                $report['tables'][] = ['name' => $table, 'rows' => $tableRows];
                $report['total_rows'] += $tableRows;
            }

            $this->writeDump($handle, "SET FOREIGN_KEY_CHECKS=1;\n", $compressed);
            $compressed ? gzclose($handle) : fclose($handle);
        } catch (\Throwable $e) {
            $compressed ? gzclose($handle) : fclose($handle);
            if (is_file($path)) {
                @unlink($path);
            }
            throw $e;
        }

        $report['size_bytes'] = (int)filesize($path);
        $report['size_human'] = $this->formatBytes($report['size_bytes']);
        $report['compressed'] = $compressed;
        return $report;
    }

    public function listBackups(): array
    {
        if (!is_dir($this->backupDir)) {
            return [];
        }

        $backups = [];
        foreach (glob($this->backupDir . DIRECTORY_SEPARATOR . '*.sql*') ?: [] as $path) {
            if (!is_file($path)) {
                continue;
            }
            $size = (int)filesize($path);
            $mtime = (int)filemtime($path);
            $backups[] = [
                'filename' => basename($path),
                'size_bytes' => $size,
                'size_human' => $this->formatBytes($size),
                'created_at' => date('c', $mtime),
                'restore_phrase' => 'RESTORE ' . basename($path),
            ];
        }

        usort($backups, static fn($a, $b) => strcmp((string)$b['created_at'], (string)$a['created_at']));
        return $backups;
    }

    public function restoreBackup(string $filename, string $confirmation): array
    {
        $this->ensureLongRunningRequest();
        $path = $this->resolveBackupPath($filename);
        $basename = basename($path);
        $expected = 'RESTORE ' . $basename;
        if (trim($confirmation) !== $expected) {
            throw new RuntimeException('عبارت تایید restore باید دقیقاً "' . $expected . '" باشد');
        }

        $preRestoreBackup = $this->createBackup('pre-restore');
        $executed = 0;
        $buffer = '';
        $isCompressed = substr($path, -3) === '.gz';
        $handle = $isCompressed ? gzopen($path, 'rb') : fopen($path, 'rb');
        if (!$handle) {
            throw new RuntimeException('فایل بکاپ قابل خواندن نیست');
        }

        try {
            $this->pdo->exec('SET FOREIGN_KEY_CHECKS=0');
            while (($line = ($isCompressed ? gzgets($handle) : fgets($handle))) !== false) {
                $trimmed = trim($line);
                if ($trimmed === '' || strpos($trimmed, '--') === 0 || strpos($trimmed, '/*') === 0) {
                    continue;
                }
                $buffer .= $line;
                if (substr(rtrim($line), -1) !== ';') {
                    continue;
                }
                $sql = trim($buffer);
                $buffer = '';
                if ($sql === '') {
                    continue;
                }
                $this->pdo->exec($sql);
                $executed++;
            }
            if (trim($buffer) !== '') {
                $this->pdo->exec($buffer);
                $executed++;
            }
            Connection::flushColumnCache();
        } finally {
            try {
                $this->pdo->exec('SET FOREIGN_KEY_CHECKS=1');
            } catch (\Throwable $ignored) {
            }
            $isCompressed ? gzclose($handle) : fclose($handle);
        }

        return [
            'restored_from' => $basename,
            'executed_statements' => $executed,
            'pre_restore_backup' => $preRestoreBackup,
            'restored_at' => date('c'),
        ];
    }

    private function dumpTable($handle, string $table, bool $compressed): int
    {
        $quotedTable = $this->quoteIdentifier($table);
        $createStmt = $this->pdo->query('SHOW CREATE TABLE ' . $quotedTable);
        $createRow = $createStmt ? $createStmt->fetch(PDO::FETCH_ASSOC) : null;
        $createSql = $createRow['Create Table'] ?? $createRow['Create View'] ?? null;
        if (!$createSql) {
            return 0;
        }

        $this->writeDump($handle, "\n-- Table: {$table}\n", $compressed);
        $this->writeDump($handle, "DROP TABLE IF EXISTS {$quotedTable};\n", $compressed);
        $this->writeDump($handle, $createSql . ";\n", $compressed);

        $rowCount = 0;
        $bufferedAttr = $this->getBufferedQueryAttribute();
        $previousBuffered = null;
        if ($bufferedAttr !== null) {
            try {
                $previousBuffered = $this->pdo->getAttribute($bufferedAttr);
                $this->pdo->setAttribute($bufferedAttr, false);
            } catch (\Throwable $e) {
                $previousBuffered = null;
            }
        }

        try {
            $stmt = $this->pdo->query('SELECT * FROM ' . $quotedTable);
            while ($stmt && ($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
                $columns = array_map([$this, 'quoteIdentifier'], array_keys($row));
                $values = array_map([$this, 'quoteValue'], array_values($row));
                $this->writeDump($handle, 'INSERT INTO ' . $quotedTable . ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $values) . ");\n", $compressed);
                $rowCount++;
            }
            if ($stmt) {
                $stmt->closeCursor();
            }
        } finally {
            if ($bufferedAttr !== null && $previousBuffered !== null) {
                try {
                    $this->pdo->setAttribute($bufferedAttr, $previousBuffered);
                } catch (\Throwable $e) {
                    try {
                        $this->pdo->setAttribute($bufferedAttr, true);
                    } catch (\Throwable $ignored) {
                    }
                }
            }
        }

        return $rowCount;
    }

    private function loadTables(): array
    {
        $tables = [];
        $stmt = $this->pdo->query("
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_type = 'BASE TABLE'
            ORDER BY table_name
        ");
        foreach (($stmt ? $stmt->fetchAll(PDO::FETCH_COLUMN) : []) as $table) {
            $tables[(string)$table] = true;
        }
        return $tables;
    }

    private function countRows(string $table): int
    {
        try {
            return (int)$this->pdo->query('SELECT COUNT(*) FROM ' . $this->quoteIdentifier($table))->fetchColumn();
        } catch (\Throwable $e) {
            return 0;
        }
    }

    private function resolveBackupPath(string $filename): string
    {
        $filename = basename($filename);
        if ($filename === '' || (!preg_match('/\.sql(\.gz)?$/', $filename))) {
            throw new RuntimeException('نام فایل بکاپ نامعتبر است');
        }
        $path = $this->backupDir . DIRECTORY_SEPARATOR . $filename;
        $realDir = realpath($this->backupDir);
        $realPath = realpath($path);
        if (!$realDir || !$realPath || strpos($realPath, $realDir . DIRECTORY_SEPARATOR) !== 0 || !is_file($realPath)) {
            throw new RuntimeException('فایل بکاپ پیدا نشد');
        }
        return $realPath;
    }

    private function ensureBackupDir(): void
    {
        if (is_dir($this->backupDir)) {
            return;
        }
        if (!mkdir($this->backupDir, 0775, true) && !is_dir($this->backupDir)) {
            throw new RuntimeException('امکان ساخت فولدر بکاپ وجود ندارد');
        }
    }

    private function quoteIdentifier(string $identifier): string
    {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }

    private function quoteValue($value): string
    {
        if ($value === null) {
            return 'NULL';
        }
        $value = (string)$value;
        $value = str_replace(
            ["\\", "\0", "\n", "\r", "\x1a", "'"],
            ["\\\\", "\\0", "\\n", "\\r", "\\Z", "\\'"],
            $value
        );
        return "'" . $value . "'";
    }

    private function formatBytes(int $bytes): string
    {
        $units = ['B', 'KB', 'MB', 'GB'];
        $size = (float)$bytes;
        $idx = 0;
        while ($size >= 1024 && $idx < count($units) - 1) {
            $size /= 1024;
            $idx++;
        }
        return number_format($size, $idx === 0 ? 0 : 1) . ' ' . $units[$idx];
    }

    private function ensureLongRunningRequest(): void
    {
        if (function_exists('set_time_limit')) {
            @set_time_limit(0);
        }
    }

    private function writeDump($handle, string $data, bool $compressed): void
    {
        $written = $compressed ? gzwrite($handle, $data) : fwrite($handle, $data);
        if ($written === false || $written < strlen($data)) {
            throw new RuntimeException('نوشتن فایل بکاپ ناموفق بود؛ فضای دیسک یا دسترسی نوشتن را بررسی کنید');
        }
    }

    private function getBufferedQueryAttribute(): ?int
    {
        if (class_exists('\Pdo\Mysql') && defined('\Pdo\Mysql::ATTR_USE_BUFFERED_QUERY')) {
            return (int)constant('\Pdo\Mysql::ATTR_USE_BUFFERED_QUERY');
        }
        if (defined('PDO::MYSQL_ATTR_USE_BUFFERED_QUERY')) {
            return (int)@constant('PDO::MYSQL_ATTR_USE_BUFFERED_QUERY');
        }
        return null;
    }
}
