<?php
declare(strict_types=1);

namespace App\Core;

use PDO;
use PDOException;

final class Database
{
    private static ?PDO $pdo = null;

    public static function connection(): PDO
    {
        if (self::$pdo instanceof PDO) {
            return self::$pdo;
        }

        $config = Config::get('database');
        $driver = $config['driver'] ?? 'sqlite';

        try {
            if ($driver === 'mysql') {
                $mysql = $config['mysql'] ?? [];
                $host = (string) ($mysql['host'] ?? '127.0.0.1');
                $port = (int) ($mysql['port'] ?? 3306);
                $database = (string) ($mysql['database'] ?? 'testing');
                $username = (string) ($mysql['username'] ?? 'root');
                $password = (string) ($mysql['password'] ?? '');
                $charset = (string) ($mysql['charset'] ?? 'utf8mb4');

                $serverPdo = new PDO(
                    sprintf('mysql:host=%s;port=%d;charset=%s', $host, $port, $charset),
                    $username,
                    $password,
                    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
                );
                $serverPdo->exec(sprintf(
                    'CREATE DATABASE IF NOT EXISTS `%s` CHARACTER SET %s COLLATE %s_unicode_ci',
                    str_replace('`', '``', $database),
                    $charset,
                    $charset
                ));

                self::$pdo = new PDO(
                    sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s', $host, $port, $database, $charset),
                    $username,
                    $password
                );
            } else {
                throw new PDOException('Unsupported database driver.');
            }

            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            self::$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            self::migrate();

            return self::$pdo;
        } catch (PDOException $exception) {
            $logDirectory = dirname(__DIR__) . '/storage/logs';
            if (! is_dir($logDirectory)) {
                mkdir($logDirectory, 0775, true);
            }
            error_log('[database] ' . $exception->getMessage() . PHP_EOL, 3, $logDirectory . '/database-error.log');
            abort(500, 'Koneksi database gagal.');
        }
    }

    public static function query(string $sql, array $params = []): \PDOStatement
    {
        $statement = self::connection()->prepare($sql);
        $statement->execute($params);

        return $statement;
    }

    private static function migrate(): void
    {
        self::$pdo->exec(
            'CREATE TABLE IF NOT EXISTS users (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                nama VARCHAR(100) NOT NULL,
                email VARCHAR(120) NOT NULL UNIQUE,
                whatsapp VARCHAR(20) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                role ENUM("admin","client") NOT NULL DEFAULT "client",
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );

        self::$pdo->exec(
            'CREATE TABLE IF NOT EXISTS products (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                nama_produk VARCHAR(120) NOT NULL,
                harga DECIMAL(12,2) NOT NULL DEFAULT 0,
                deskripsi TEXT NULL,
                status ENUM("aktif","nonaktif") NOT NULL DEFAULT "aktif",
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );

        self::$pdo->exec(
            'CREATE TABLE IF NOT EXISTS purchases (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT UNSIGNED NOT NULL,
                product_id BIGINT UNSIGNED NOT NULL,
                harga DECIMAL(12,2) NOT NULL DEFAULT 0,
                bukti_transfer VARCHAR(255) NULL,
                status ENUM("pending","verified","completed","rejected") NOT NULL DEFAULT "pending",
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                INDEX idx_purchases_user_id (user_id),
                INDEX idx_purchases_product_id (product_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );

        self::$pdo->exec(
            'CREATE TABLE IF NOT EXISTS settings (
                setting_key VARCHAR(100) NOT NULL PRIMARY KEY,
                setting_value TEXT NULL,
                updated_at DATETIME NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );

        self::seedAdminUser();
        self::seedDefaultProducts();
        self::seedDefaultSettings();
    }

    private static function seedAdminUser(): void
    {
        $exists = self::query('SELECT id FROM users WHERE role = :role LIMIT 1', [
            'role' => 'admin',
        ])->fetch();

        if (is_array($exists)) {
            return;
        }

        $algorithm = defined('PASSWORD_ARGON2ID') ? PASSWORD_ARGON2ID : PASSWORD_BCRYPT;
        $now = date('Y-m-d H:i:s');

        self::query(
            'INSERT INTO users (nama, email, whatsapp, password, role, created_at, updated_at)
             VALUES (:nama, :email, :whatsapp, :password, :role, :created_at, :updated_at)',
            [
                'nama' => getenv('ADMIN_NAME') ?: 'Administrator',
                'email' => getenv('ADMIN_EMAIL') ?: 'admin@local.test',
                'whatsapp' => getenv('ADMIN_WHATSAPP') ?: '6280000000000',
                'password' => password_hash(getenv('ADMIN_PASSWORD') ?: 'Admin12345!', $algorithm),
                'role' => 'admin',
                'created_at' => $now,
                'updated_at' => $now,
            ]
        );
    }

    private static function seedDefaultProducts(): void
    {
        $now = date('Y-m-d H:i:s');
        $products = [
            ['Jaringan Standar', 550000, 'Paket internet standar untuk kebutuhan harian.', 'aktif'],
            ['Jaringan Top', 750000, 'Paket internet dengan performa lebih stabil dan cepat.', 'aktif'],
            ['Jaringan Super Top', 999000, 'Paket rekomendasi dengan performa terbaik.', 'aktif'],
            ['Jaringan Premium', 1500000, 'Paket premium untuk kebutuhan paling tinggi.', 'aktif'],
        ];

        foreach ($products as [$name, $price, $description, $status]) {
            $exists = self::query('SELECT id FROM products WHERE nama_produk = :nama_produk LIMIT 1', [
                'nama_produk' => $name,
            ])->fetch();

            if (is_array($exists)) {
                continue;
            }

            self::query(
                'INSERT INTO products (nama_produk, harga, deskripsi, status, created_at, updated_at)
                 VALUES (:nama_produk, :harga, :deskripsi, :status, :created_at, :updated_at)',
                [
                    'nama_produk' => $name,
                    'harga' => $price,
                    'deskripsi' => $description,
                    'status' => $status,
                    'created_at' => $now,
                    'updated_at' => $now,
                ]
            );
        }
    }

    private static function seedDefaultSettings(): void
    {
        $now = date('Y-m-d H:i:s');
        $defaults = [
            'company_name' => getenv('APP_NAME') ?: 'WiFi Access',
            'company_logo' => null,
            'bank_account_number' => null,
            'payment_barcode' => null,
        ];

        foreach ($defaults as $key => $value) {
            $exists = self::query('SELECT setting_key FROM settings WHERE setting_key = :setting_key LIMIT 1', [
                'setting_key' => $key,
            ])->fetch();

            if (is_array($exists)) {
                continue;
            }

            self::query(
                'INSERT INTO settings (setting_key, setting_value, updated_at)
                 VALUES (:setting_key, :setting_value, :updated_at)',
                [
                    'setting_key' => $key,
                    'setting_value' => $value,
                    'updated_at' => $now,
                ]
            );
        }
    }
}
