<?php
/**
 * Spacetraders API Client Library - Storage
 *
 * SQLite storage for configuration and API cache.
 *
 * @category Storage
 * @package  SpacetradersAPI
 * @author   Keith Solomon <keith@keithsolomon.net>
 * @license  MIT License
 * @version  GIT: <git_id>
 * @link     https://git.keithsolomon.net/keith/Spacetraders
 */

/**
 * Storage for configuration and API cache.
 */
class SpacetradersStorage {
    /**
     * Database handle.
     *
     * @var PDO
     */
    private PDO $db;

    /**
     * Create storage and initialize schema.
     *
     * @param string $dbPath Absolute or project-relative SQLite database path.
     */
    public function __construct( string $dbPath ) {
        $directory = dirname( $dbPath );
        if (! is_dir( $directory ) ) {
            mkdir( $directory, 0777, true );
        }

        $this->db = new PDO( 'sqlite:' . $dbPath );
        $this->db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $this->db->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );

        $this->_initializeSchema();
    }

    /**
     * Create required tables if they do not exist.
     *
     * @return void
     */
    private function _initializeSchema(): void { // phpcs:ignore
        $this->db->exec(
            'CREATE TABLE IF NOT EXISTS settings (
                setting_key TEXT PRIMARY KEY,
                setting_value TEXT NOT NULL,
                updated_at INTEGER NOT NULL
            )'
        );

        $this->db->exec(
            'CREATE TABLE IF NOT EXISTS api_cache (
                cache_key TEXT PRIMARY KEY,
                response_json TEXT NOT NULL,
                created_at INTEGER NOT NULL,
                expires_at INTEGER NOT NULL
            )'
        );

        $this->db->exec(
            'CREATE INDEX IF NOT EXISTS idx_api_cache_expires
            ON api_cache (expires_at)'
        );

        $this->db->exec(
            'CREATE TABLE IF NOT EXISTS market_scan_waypoints (
                system_symbol TEXT NOT NULL,
                waypoint_symbol TEXT NOT NULL,
                exports_json TEXT NOT NULL,
                probe_ship_symbol TEXT NOT NULL,
                error_message TEXT NOT NULL,
                updated_at INTEGER NOT NULL,
                PRIMARY KEY (system_symbol, waypoint_symbol)
            )'
        );

        $this->db->exec(
            'CREATE INDEX IF NOT EXISTS idx_market_scan_system_updated
            ON market_scan_waypoints (system_symbol, updated_at)'
        );
    }

    /**
     * Save a generic setting.
     *
     * @param string $key   Setting key.
     * @param string $value Setting value.
     *
     * @return void
     */
    public function setSetting( string $key, string $value ): void {
        $stmt = $this->db->prepare(
            'INSERT INTO settings (setting_key, setting_value, updated_at)
            VALUES (:key, :value, :updated_at)
            ON CONFLICT(setting_key) DO UPDATE SET
                setting_value = excluded.setting_value,
                updated_at = excluded.updated_at'
        );

        $stmt->execute(
            array(
                ':key'        => $key,
                ':value'      => $value,
                ':updated_at' => time(),
            )
        );
    }

    /**
     * Load a generic setting.
     *
     * @param string $key Setting key.
     *
     * @return string|null
     */
    public function getSetting( string $key ): ?string {
        $stmt = $this->db->prepare(
            'SELECT setting_value FROM settings WHERE setting_key = :key LIMIT 1'
        );
        $stmt->execute( array( ':key' => $key ) );
        $row = $stmt->fetch();

        if (! is_array( $row ) || ! isset( $row['setting_value'] ) ) {
            return null;
        }

        return (string) $row['setting_value'];
    }

    /**
     * Save the agent token in settings.
     *
     * @param string $token Agent token.
     *
     * @return void
     */
    public function setAgentToken( string $token ): void {
        $this->setSetting( 'agent_token', $token );
    }

    /**
     * Get the stored agent token, if available.
     *
     * @return string|null
     */
    public function getAgentToken(): ?string {
        return $this->getSetting( 'agent_token' );
    }

    /**
     * Fetch a valid cached response by cache key.
     *
     * @param string $cacheKey Cache key.
     *
     * @return array<string,mixed>|null
     */
    public function getCache( string $cacheKey ): ?array {
        $stmt = $this->db->prepare(
            'SELECT response_json, expires_at
            FROM api_cache
            WHERE cache_key = :cache_key
            LIMIT 1'
        );
        $stmt->execute( array( ':cache_key' => $cacheKey ) );
        $row = $stmt->fetch();

        if (! is_array( $row ) ) {
            return null;
        }

        if ((int) $row['expires_at'] <= time() ) {
            $this->deleteCache( $cacheKey );
            return null;
        }

        $decoded = json_decode( (string) $row['response_json'], true );
        if (! is_array( $decoded ) ) {
            $this->deleteCache( $cacheKey );
            return null;
        }

        return $decoded;
    }

    /**
     * Store API response in cache.
     *
     * @param string              $cacheKey Cache key.
     * @param array<string,mixed> $response API response payload.
     * @param int                 $ttl      Time to live in seconds.
     *
     * @return void
     */
    public function setCache( string $cacheKey, array $response, int $ttl = 600 ): void {
        $json = json_encode( $response );
        if ($json === false ) {
            return;
        }

        $createdAt = time();
        $expiresAt = $createdAt + max( 1, $ttl );

        $stmt = $this->db->prepare(
            'INSERT INTO api_cache (cache_key, response_json, created_at, expires_at)
            VALUES (:cache_key, :response_json, :created_at, :expires_at)
            ON CONFLICT(cache_key) DO UPDATE SET
                response_json = excluded.response_json,
                created_at = excluded.created_at,
                expires_at = excluded.expires_at'
        );

        $stmt->execute(
            array(
                ':cache_key'     => $cacheKey,
                ':response_json' => $json,
                ':created_at'    => $createdAt,
                ':expires_at'    => $expiresAt,
            )
        );
    }

    /**
     * Remove one cache row.
     *
     * @param string $cacheKey Cache key.
     *
     * @return void
     */
    public function deleteCache( string $cacheKey ): void {
        $stmt = $this->db->prepare(
            'DELETE FROM api_cache WHERE cache_key = :cache_key'
        );
        $stmt->execute( array( ':cache_key' => $cacheKey ) );
    }

    /**
     * Remove all expired cache rows.
     *
     * @return void
     */
    public function purgeExpiredCache(): void {
        $stmt = $this->db->prepare(
            'DELETE FROM api_cache WHERE expires_at <= :now'
        );
        $stmt->execute( array( ':now' => time() ) );
    }

    /**
     * Remove all cached API rows.
     *
     * @return void
     */
    public function clearAllCache(): void {
        $this->db->exec( 'DELETE FROM api_cache' );
    }

    /**
     * Store scanned export data for a marketplace waypoint.
     *
     * @param string                   $systemSymbol   System symbol.
     * @param string                   $waypointSymbol Waypoint symbol.
     * @param array<int,array<string,mixed>> $exports Scanned export records.
     * @param string                   $probeShipSymbol Probe ship symbol used for scan.
     * @param string                   $errorMessage   Optional scan error message.
     *
     * @return void
     */
    public function upsertMarketScanWaypoint(
        string $systemSymbol,
        string $waypointSymbol,
        array $exports,
        string $probeShipSymbol = '',
        string $errorMessage = ''
    ): void {
        $exportsJson = json_encode( array_values( $exports ) );
        if ($exportsJson === false ) {
            return;
        }

        $stmt = $this->db->prepare(
            'INSERT INTO market_scan_waypoints (
                system_symbol,
                waypoint_symbol,
                exports_json,
                probe_ship_symbol,
                error_message,
                updated_at
            )
            VALUES (
                :system_symbol,
                :waypoint_symbol,
                :exports_json,
                :probe_ship_symbol,
                :error_message,
                :updated_at
            )
            ON CONFLICT(system_symbol, waypoint_symbol) DO UPDATE SET
                exports_json = excluded.exports_json,
                probe_ship_symbol = excluded.probe_ship_symbol,
                error_message = excluded.error_message,
                updated_at = excluded.updated_at'
        );

        $stmt->execute(
            array(
                ':system_symbol'    => $systemSymbol,
                ':waypoint_symbol'  => $waypointSymbol,
                ':exports_json'     => $exportsJson,
                ':probe_ship_symbol' => $probeShipSymbol,
                ':error_message'    => $errorMessage,
                ':updated_at'       => time(),
            )
        );
    }

    /**
     * Load scanned market export data for a system.
     *
     * @param string $systemSymbol System symbol.
     *
     * @return array<int,array<string,mixed>>
     */
    public function getMarketScanWaypointsBySystem( string $systemSymbol ): array {
        $stmt = $this->db->prepare(
            'SELECT
                system_symbol,
                waypoint_symbol,
                exports_json,
                probe_ship_symbol,
                error_message,
                updated_at
            FROM market_scan_waypoints
            WHERE system_symbol = :system_symbol
            ORDER BY waypoint_symbol ASC'
        );
        $stmt->execute( array( ':system_symbol' => $systemSymbol ) );
        $rows = $stmt->fetchAll();

        if (! is_array( $rows ) ) {
            return array();
        }

        foreach ( $rows as &$row ) {
            $decodedExports = json_decode( (string) ( $row['exports_json'] ?? '[]' ), true );
            $row['exports'] = is_array( $decodedExports ) ? $decodedExports : array();
        }
        unset( $row );

        return $rows;
    }
}