<?php

declare(strict_types=1);

namespace PhpWA\Storage;

use InvalidArgumentException;
use PDO;
use PhpWA\Exception\StorageException;

final class PdoSchema
{
    public static function install(PDO $pdo, string $prefix = 'phpwa_'): void
    {
        $driver = (string) $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        foreach (self::statements($driver, $prefix) as $sql) {
            try {
                $pdo->exec($sql);
            } catch (\Throwable $error) {
                throw new StorageException('Unable to install the PhpWA storage schema.', 0, $error);
            }
        }
    }

     
    public static function statements(string $driver, string $prefix = 'phpwa_'): array
    {
        self::assertPrefix($prefix);
        if ($driver === 'mysql') {
            return self::mysql($prefix);
        }
        if ($driver === 'sqlite') {
            return self::sqlite($prefix);
        }
        throw new InvalidArgumentException('Only MySQL and SQLite PDO drivers are supported.');
    }

    public static function assertPrefix(string $prefix): void
    {
        if (preg_match('/\A[A-Za-z][A-Za-z0-9_]{0,31}\z/D', $prefix) !== 1) {
            throw new InvalidArgumentException('PDO table prefix is invalid.');
        }
    }

     
    private static function mysql(string $p): array
    {
        $engine = ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC';
        return [
            "CREATE TABLE IF NOT EXISTS {$p}accounts (account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, revision BIGINT UNSIGNED NOT NULL DEFAULT 0, updated_at BIGINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (account_hash)){$engine}",
            "CREATE TABLE IF NOT EXISTS {$p}state (account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, collection VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, state_key_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, state_key VARBINARY(1280) NOT NULL, encrypted_value MEDIUMBLOB NOT NULL, revision BIGINT UNSIGNED NOT NULL, updated_at BIGINT UNSIGNED NOT NULL, PRIMARY KEY (account_hash, collection, state_key_hash), KEY {$p}state_collection (account_hash, collection), CONSTRAINT {$p}state_account_fk FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE){$engine}",
            "CREATE TABLE IF NOT EXISTS {$p}dedupe (account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, message_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, received_at BIGINT UNSIGNED NOT NULL, PRIMARY KEY (account_hash, message_hash), KEY {$p}dedupe_age (account_hash, received_at), CONSTRAINT {$p}dedupe_account_fk FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE){$engine}",
            "CREATE TABLE IF NOT EXISTS {$p}outbox (event_id CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, event_type VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, encrypted_payload MEDIUMBLOB NOT NULL, occurred_at BIGINT UNSIGNED NOT NULL, attempts INT UNSIGNED NOT NULL DEFAULT 0, available_at BIGINT UNSIGNED NOT NULL, lease_owner VARCHAR(128) CHARACTER SET ascii COLLATE ascii_bin NULL, lease_until BIGINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (event_id), KEY {$p}outbox_claim (account_hash, available_at, lease_until), CONSTRAINT {$p}outbox_account_fk FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE){$engine}",
            "CREATE TABLE IF NOT EXISTS {$p}retries (account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, message_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, attempts INT UNSIGNED NOT NULL, next_allowed_at BIGINT UNSIGNED NOT NULL, updated_at BIGINT UNSIGNED NOT NULL, PRIMARY KEY (account_hash, message_hash), KEY {$p}retries_age (account_hash, updated_at), CONSTRAINT {$p}retries_account_fk FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE){$engine}",
        ];
    }

     
    private static function sqlite(string $p): array
    {
        return [
            'PRAGMA foreign_keys = ON',
            "CREATE TABLE IF NOT EXISTS {$p}accounts (account_hash TEXT NOT NULL PRIMARY KEY, revision INTEGER NOT NULL DEFAULT 0, updated_at INTEGER NOT NULL DEFAULT 0)",
            "CREATE TABLE IF NOT EXISTS {$p}state (account_hash TEXT NOT NULL, collection TEXT NOT NULL, state_key_hash TEXT NOT NULL, state_key BLOB NOT NULL, encrypted_value BLOB NOT NULL, revision INTEGER NOT NULL, updated_at INTEGER NOT NULL, PRIMARY KEY (account_hash, collection, state_key_hash), FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE)",
            "CREATE INDEX IF NOT EXISTS {$p}state_collection ON {$p}state (account_hash, collection)",
            "CREATE TABLE IF NOT EXISTS {$p}dedupe (account_hash TEXT NOT NULL, message_hash TEXT NOT NULL, received_at INTEGER NOT NULL, PRIMARY KEY (account_hash, message_hash), FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE)",
            "CREATE INDEX IF NOT EXISTS {$p}dedupe_age ON {$p}dedupe (account_hash, received_at)",
            "CREATE TABLE IF NOT EXISTS {$p}outbox (event_id TEXT NOT NULL PRIMARY KEY, account_hash TEXT NOT NULL, event_type TEXT NOT NULL, encrypted_payload BLOB NOT NULL, occurred_at INTEGER NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, available_at INTEGER NOT NULL, lease_owner TEXT NULL, lease_until INTEGER NOT NULL DEFAULT 0, FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE)",
            "CREATE INDEX IF NOT EXISTS {$p}outbox_claim ON {$p}outbox (account_hash, available_at, lease_until)",
            "CREATE TABLE IF NOT EXISTS {$p}retries (account_hash TEXT NOT NULL, message_hash TEXT NOT NULL, attempts INTEGER NOT NULL, next_allowed_at INTEGER NOT NULL, updated_at INTEGER NOT NULL, PRIMARY KEY (account_hash, message_hash), FOREIGN KEY (account_hash) REFERENCES {$p}accounts(account_hash) ON DELETE CASCADE)",
            "CREATE INDEX IF NOT EXISTS {$p}retries_age ON {$p}retries (account_hash, updated_at)",
        ];
    }

    private function __construct()
    {
    }
}
