<?php

declare(strict_types=1);

namespace SeoScope\Repositories;

use PDO;

final class GooglePropertyRepository
{
    public function __construct(
        private PDO $pdo,
        private string $tablePrefix,
        private int $userId = 1
    ) {}

    private function accountsTable(): string
    {
        return '`' . str_replace('`', '``', $this->tablePrefix . 'google_accounts') . '`';
    }

    private function table(): string
    {
        return '`' . str_replace('`', '``', $this->tablePrefix . 'gsc_properties') . '`';
    }

    private function accountId(): int
    {
        $accounts = $this->accountsTable();
        $stmt = $this->pdo->prepare("SELECT id FROM {$accounts} WHERE user_id = ? AND provider = 'google' ORDER BY id ASC LIMIT 1");
        $stmt->execute([$this->userId]);
        $id = (int) ($stmt->fetchColumn() ?: 0);
        if ($id > 0) {
            return $id;
        }
        $now = date('Y-m-d H:i:s');
        $ins = $this->pdo->prepare("INSERT INTO {$accounts} (user_id, provider, created_at, updated_at) VALUES (?, 'google', ?, ?)");
        $ins->execute([$this->userId, $now, $now]);
        return (int) $this->pdo->lastInsertId();
    }

    /**
     * @return list<array{id:int,google_account_id:int,site_url:string,site_type:string,permission_level:string,fetched_at:string,is_enabled:int,enabled_at:?string,last_sync_date:?string,last_sync_at:?string,last_sync_error:?string}>
     */
    public function all(): array
    {
        $accId = $this->accountId();
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT id, google_account_id, site_url, site_type, permission_level, fetched_at, is_enabled, enabled_at, last_sync_date, last_sync_at, last_sync_error
             FROM {$t}
             WHERE google_account_id = ?
             ORDER BY site_url ASC"
        );
        $stmt->execute([$accId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $out[] = [
                'id' => (int) ($r['id'] ?? 0),
                'google_account_id' => (int) ($r['google_account_id'] ?? 0),
                'site_url' => (string) ($r['site_url'] ?? ''),
                'site_type' => (string) ($r['site_type'] ?? ''),
                'permission_level' => (string) ($r['permission_level'] ?? ''),
                'fetched_at' => (string) ($r['fetched_at'] ?? ''),
                'is_enabled' => (int) ($r['is_enabled'] ?? 0),
                'enabled_at' => $r['enabled_at'] !== null ? (string) $r['enabled_at'] : null,
                'last_sync_date' => $r['last_sync_date'] !== null ? (string) $r['last_sync_date'] : null,
                'last_sync_at' => $r['last_sync_at'] !== null ? (string) $r['last_sync_at'] : null,
                'last_sync_error' => $r['last_sync_error'] !== null ? (string) $r['last_sync_error'] : null,
            ];
        }
        return $out;
    }

    /**
     * @return array{id:int,site_url:string,permission_level:string,is_enabled:int,last_sync_date:?string,last_sync_at:?string,last_sync_error:?string}|null
     */
    public function findById(int $id): ?array
    {
        if ($id < 1) {
            return null;
        }
        $accId = $this->accountId();
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "SELECT id, site_url, permission_level, is_enabled, last_sync_date, last_sync_at, last_sync_error
             FROM {$t}
             WHERE google_account_id = ? AND id = ?
             LIMIT 1"
        );
        $stmt->execute([$accId, $id]);
        $r = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($r)) {
            return null;
        }
        return [
            'id' => (int) ($r['id'] ?? 0),
            'site_url' => (string) ($r['site_url'] ?? ''),
            'permission_level' => (string) ($r['permission_level'] ?? ''),
            'is_enabled' => (int) ($r['is_enabled'] ?? 0),
            'last_sync_date' => $r['last_sync_date'] !== null ? (string) $r['last_sync_date'] : null,
            'last_sync_at' => $r['last_sync_at'] !== null ? (string) $r['last_sync_at'] : null,
            'last_sync_error' => $r['last_sync_error'] !== null ? (string) $r['last_sync_error'] : null,
        ];
    }

    /**
     * @param list<array{siteUrl:string, permissionLevel:string, siteType:string}> $sites
     */
    public function upsertMany(array $sites): int
    {
        if ($sites === []) {
            return 0;
        }
        $accId = $this->accountId();
        $t = $this->table();
        $now = date('Y-m-d H:i:s');
        $stmt = $this->pdo->prepare(
            "INSERT INTO {$t} (user_id, google_account_id, site_url, site_type, permission_level, fetched_at)
             VALUES (?, ?, ?, ?, ?, ?)
             ON DUPLICATE KEY UPDATE site_type = VALUES(site_type),
                                     permission_level = VALUES(permission_level),
                                     user_id = VALUES(user_id),
                                     fetched_at = VALUES(fetched_at)"
        );
        $count = 0;
        foreach ($sites as $s) {
            $siteUrl = trim((string) ($s['siteUrl'] ?? ''));
            $perm = trim((string) ($s['permissionLevel'] ?? ''));
            $type = trim((string) ($s['siteType'] ?? ''));
            if ($siteUrl === '' || $perm === '' || $type === '') {
                continue;
            }
            $stmt->execute([$this->userId, $accId, $siteUrl, $type, $perm, $now]);
            $count++;
        }
        return $count;
    }

    /**
     * @return list<string>
     */
    public function siteUrls(): array
    {
        $accId = $this->accountId();
        $t = $this->table();
        $stmt = $this->pdo->prepare("SELECT site_url FROM {$t} WHERE google_account_id = ? ORDER BY site_url ASC");
        $stmt->execute([$accId]);
        $rows = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $out = [];
        foreach ($rows as $r) {
            if (is_string($r) && trim($r) !== '') {
                $out[] = trim($r);
            }
        }
        return $out;
    }

    public function setEnabled(string $siteUrl, bool $enabled): void
    {
        $siteUrl = trim($siteUrl);
        if ($siteUrl === '') {
            return;
        }
        $accId = $this->accountId();
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "UPDATE {$t} SET is_enabled = ?, enabled_at = ? WHERE google_account_id = ? AND site_url = ? LIMIT 1"
        );
        $stmt->execute([$enabled ? 1 : 0, $enabled ? date('Y-m-d H:i:s') : null, $accId, $siteUrl]);
    }

    /**
     * @return array{site_url:string,permission_level:string}|null
     */
    public function findPermission(string $siteUrl): ?array
    {
        $siteUrl = trim($siteUrl);
        if ($siteUrl === '') {
            return null;
        }
        $accId = $this->accountId();
        $t = $this->table();
        $stmt = $this->pdo->prepare("SELECT site_url, permission_level FROM {$t} WHERE google_account_id = ? AND site_url = ? LIMIT 1");
        $stmt->execute([$accId, $siteUrl]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!is_array($row)) {
            return null;
        }
        return [
            'site_url' => (string) ($row['site_url'] ?? ''),
            'permission_level' => (string) ($row['permission_level'] ?? ''),
        ];
    }

    public function updateSyncMeta(string $siteUrl, ?string $lastSyncDate, ?string $error): void
    {
        $siteUrl = trim($siteUrl);
        if ($siteUrl === '') {
            return;
        }
        $accId = $this->accountId();
        $t = $this->table();
        $stmt = $this->pdo->prepare(
            "UPDATE {$t} SET last_sync_date = ?, last_sync_at = ?, last_sync_error = ? WHERE google_account_id = ? AND site_url = ? LIMIT 1"
        );
        $stmt->execute([
            $lastSyncDate,
            $lastSyncDate !== null ? date('Y-m-d H:i:s') : null,
            $error,
            $accId,
            $siteUrl,
        ]);
    }
}

