<?php
/**
 * license-php/lib/LicenseRepository.php
 * Data Access Layer for License records in MySQL / SQLite.
 * Uses PDO Prepared Statements strictly.
 */

class LicenseRepository {
    private PDO $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    /**
     * Find license by raw key or key hash.
     */
    public function findByKeyOrHash(string $rawKey): ?array {
        $cleanKey = strtoupper(trim($rawKey));
        $hash = LicenseCrypto::hashKey($cleanKey);

        $stmt = $this->pdo->prepare("
            SELECT * FROM licenses 
            WHERE license_key = :key OR license_key_hash = :hash 
            LIMIT 1
        ");
        $stmt->execute([':key' => $cleanKey, ':hash' => $hash]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /**
     * Find license by ID (e.g. lic_...).
     */
    public function findById(string $id): ?array {
        $stmt = $this->pdo->prepare("SELECT * FROM licenses WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => trim($id)]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /**
     * Create a new license record.
     */
    public function createLicense(array $data): array {
        $id = $data['id'] ?? ('lic_' . bin2hex(random_bytes(8)));
        $key = strtoupper(trim($data['license_key']));
        $hash = $data['license_key_hash'] ?? LicenseCrypto::hashKey($key);
        $product = $data['product'] ?? 'presensi-smansa-pro';
        $plan = strtoupper(trim($data['plan'] ?? 'PRO'));
        $customerName = trim($data['customer_name'] ?? '');
        $customerEmail = trim($data['customer_email'] ?? '');
        $domain = !empty($data['domain']) ? LicenseCrypto::cleanDomain($data['domain']) : null;
        $status = $data['status'] ?? ($domain ? 'active' : 'unused');
        $licenseType = $data['license_type'] ?? 'Subscription';
        $maxActivation = (int)($data['max_activation'] ?? 1);
        $activationCount = (int)($data['activation_count'] ?? ($domain ? 1 : 0));
        $activatedAt = $data['activated_at'] ?? ($domain ? gmdate('Y-m-d\TH:i:s.v\Z') : null);
        $expiresAt = $data['expires_at'] ?? null;
        $now = gmdate('Y-m-d\TH:i:s.v\Z');
        $notes = $data['notes'] ?? '';

        $stmt = $this->pdo->prepare("
            INSERT INTO licenses (
                id, license_key, license_key_hash, product, plan, customer_name, customer_email,
                domain, status, license_type, max_activation, activation_count, activated_at,
                expires_at, last_verified_at, created_at, updated_at, notes
            ) VALUES (
                :id, :license_key, :license_key_hash, :product, :plan, :customer_name, :customer_email,
                :domain, :status, :license_type, :max_activation, :activation_count, :activated_at,
                :expires_at, NULL, :created_at, :updated_at, :notes
            )
        ");

        $stmt->execute([
            ':id'               => $id,
            ':license_key'      => $key,
            ':license_key_hash' => $hash,
            ':product'          => $product,
            ':plan'             => $plan,
            ':customer_name'    => $customerName,
            ':customer_email'   => $customerEmail,
            ':domain'           => $domain,
            ':status'           => $status,
            ':license_type'     => $licenseType,
            ':max_activation'   => $maxActivation,
            ':activation_count' => $activationCount,
            ':activated_at'     => $activatedAt,
            ':expires_at'       => $expiresAt,
            ':created_at'       => $now,
            ':updated_at'       => $now,
            ':notes'            => $notes,
        ]);

        return $this->findById($id);
    }

    /**
     * Activate / bind domain to license.
     */
    public function activateDomain(string $id, string $domain, int $activationCount): bool {
        $now = gmdate('Y-m-d\TH:i:s.v\Z');
        $stmt = $this->pdo->prepare("
            UPDATE licenses 
            SET domain = :domain, status = 'active', activation_count = :count,
                activated_at = COALESCE(activated_at, :now1), last_verified_at = :now2, updated_at = :now3
            WHERE id = :id
        ");
        return $stmt->execute([
            ':domain' => $domain,
            ':count'  => $activationCount,
            ':now1'   => $now,
            ':now2'   => $now,
            ':now3'   => $now,
            ':id'     => $id
        ]);
    }

    /**
     * Update license verification timestamp.
     */
    public function updateLastVerified(string $id): bool {
        $now = gmdate('Y-m-d\TH:i:s.v\Z');
        $stmt = $this->pdo->prepare("UPDATE licenses SET last_verified_at = :now1, updated_at = :now2 WHERE id = :id");
        return $stmt->execute([':now1' => $now, ':now2' => $now, ':id' => $id]);
    }

    /**
     * Update status (e.g. active, suspended, revoked, expired).
     */
    public function updateStatus(string $id, string $status): bool {
        $now = gmdate('Y-m-d\TH:i:s.v\Z');
        $stmt = $this->pdo->prepare("UPDATE licenses SET status = :status, updated_at = :now WHERE id = :id");
        return $stmt->execute([':status' => $status, ':now' => $now, ':id' => $id]);
    }

    /**
     * Reset domain binding.
     */
    public function resetDomain(string $id): bool {
        $now = gmdate('Y-m-d\TH:i:s.v\Z');
        $stmt = $this->pdo->prepare("
            UPDATE licenses 
            SET domain = NULL, status = 'unused', activation_count = 0, activated_at = NULL, updated_at = :now 
            WHERE id = :id
        ");
        return $stmt->execute([':now1' => $now, ':now2' => $now, ':id' => $id]);
    }

    /**
     * Extend expiration date.
     */
    public function extendExpiration(string $id, string $newExpiresAt): bool {
        $now = gmdate('Y-m-d\TH:i:s.v\Z');
        $stmt = $this->pdo->prepare("
            UPDATE licenses 
            SET expires_at = :expires_at, status = CASE WHEN status = 'expired' THEN 'active' ELSE status END, updated_at = :now 
            WHERE id = :id
        ");
        return $stmt->execute([':expires_at' => $newExpiresAt, ':now' => $now, ':id' => $id]);
    }

    /**
     * Delete license.
     */
    public function deleteLicense(string $id): bool {
        $stmt = $this->pdo->prepare("DELETE FROM licenses WHERE id = :id");
        return $stmt->execute([':id' => $id]);
    }

    /**
     * List licenses with query filters.
     */
    public function listLicenses(array $filters = []): array {
        $sql = "SELECT * FROM licenses WHERE 1=1";
        $params = [];

        if (!empty($filters['search'])) {
            $s = '%' . trim($filters['search']) . '%';
            $sql .= " AND (license_key LIKE :s1 OR customer_name LIKE :s2 OR customer_email LIKE :s3 OR domain LIKE :s4)";
            $params[':s1'] = $s;
            $params[':s2'] = $s;
            $params[':s3'] = $s;
            $params[':s4'] = $s;
        }

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $sql .= " AND status = :status";
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['plan']) && $filters['plan'] !== 'all') {
            $sql .= " AND UPPER(plan) = :plan";
            $params[':plan'] = strtoupper($filters['plan']);
        }

        if (!empty($filters['product']) && $filters['product'] !== 'all') {
            $sql .= " AND product = :product";
            $params[':product'] = $filters['product'];
        }

        $sql .= " ORDER BY created_at DESC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Get statistics breakdown.
     */
    public function getStats(): array {
        $all = $this->listLicenses();
        $now = time();
        $in7d = $now + 7 * 86400;
        $in30d = $now + 30 * 86400;

        $stats = [
            'total' => count($all),
            'active' => 0,
            'unused' => 0,
            'suspended' => 0,
            'expired' => 0,
            'revoked' => 0,
            'expiring_7d' => 0,
            'expiring_30d' => 0,
        ];

        foreach ($all as $lic) {
            $st = $lic['status'];
            if ($st === 'active') $stats['active']++;
            elseif ($st === 'unused' || ($st === 'inactive' && empty($lic['domain']))) $stats['unused']++;
            elseif ($st === 'suspended') $stats['suspended']++;
            elseif ($st === 'revoked') $stats['revoked']++;

            if (!empty($lic['expires_at'])) {
                $exp = strtotime($lic['expires_at']);
                if ($exp !== false) {
                    if ($exp < $now) {
                        $stats['expired']++;
                    } else {
                        if ($exp <= $in7d) $stats['expiring_7d']++;
                        if ($exp <= $in30d) $stats['expiring_30d']++;
                    }
                }
            }
        }

        return $stats;
    }
}
