<?php

declare(strict_types=1);

namespace ABS\Modules\QuickAccess\Repositories;

use ABS\Core\Database;
use RuntimeException;

final class QuickAccessRepository
{
    private bool $schemaReady = false;

    public function ensureSchema(): void
    {
        if ($this->schemaReady) return;

        $statements = [
            "CREATE TABLE IF NOT EXISTS abs_quick_link_categories (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                slug VARCHAR(120) NOT NULL UNIQUE,
                icon VARCHAR(80) NOT NULL DEFAULT 'bi-folder',
                color VARCHAR(20) NOT NULL DEFAULT '#0d6efd',
                sort_order INT NOT NULL DEFAULT 0,
                active TINYINT(1) NOT NULL DEFAULT 1,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS abs_quick_links (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                category_id INT UNSIGNED NULL,
                supplier_id BIGINT UNSIGNED NULL,
                name VARCHAR(190) NOT NULL,
                url VARCHAR(1000) NOT NULL,
                description VARCHAR(500) NULL,
                logo_path VARCHAR(500) NULL,
                open_new_tab TINYINT(1) NOT NULL DEFAULT 1,
                is_favorite TINYINT(1) NOT NULL DEFAULT 0,
                active TINYINT(1) NOT NULL DEFAULT 1,
                sort_order INT NOT NULL DEFAULT 0,
                created_by INT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_quick_links_category(category_id),
                INDEX idx_quick_links_active(active),
                INDEX idx_quick_links_order(sort_order),
                UNIQUE KEY uq_quick_link_supplier(supplier_id),
                CONSTRAINT fk_quick_link_category FOREIGN KEY(category_id) REFERENCES abs_quick_link_categories(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
            "CREATE TABLE IF NOT EXISTS abs_quick_link_usage (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                link_id BIGINT UNSIGNED NOT NULL,
                user_id INT NULL,
                accessed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_quick_usage_link(link_id),
                INDEX idx_quick_usage_user(user_id),
                INDEX idx_quick_usage_date(accessed_at),
                CONSTRAINT fk_quick_usage_link FOREIGN KEY(link_id) REFERENCES abs_quick_links(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        ];

        foreach ($statements as $sql) Database::prepare($sql)->execute();

        $defaults = [
            ['Cataloage piese','cataloage-piese','bi-car-front','#0d6efd',10],
            ['Furnizori','furnizori','bi-building','#fd7e14',20],
            ['Asigurări','asigurari','bi-shield-check','#198754',30],
            ['Vopsea','vopsea','bi-palette','#6f42c1',40],
            ['Service','service','bi-tools','#dc3545',50],
            ['Documentație tehnică','documentatie-tehnica','bi-journal-text','#0dcaf0',60],
            ['Platforme interne','platforme-interne','bi-grid','#212529',70],
            ['Altele','altele','bi-three-dots','#6c757d',999],
        ];
        $q = Database::prepare('INSERT IGNORE INTO abs_quick_link_categories(name,slug,icon,color,sort_order) VALUES(?,?,?,?,?)');
        foreach ($defaults as $row) $q->execute($row);
        $this->schemaReady = true;
    }

    public function categories(bool $activeOnly = true): array
    {
        $this->ensureSchema();
        $q = Database::prepare('SELECT * FROM abs_quick_link_categories'.($activeOnly?' WHERE active=1':'').' ORDER BY sort_order,name');
        $q->execute();
        return $q->fetchAll();
    }

    public function links(array $filters = []): array
    {
        $this->ensureSchema();
        $where = []; $params = [];
        if (($filters['active'] ?? '1') !== 'all') { $where[]='l.active=:active'; $params['active']=(int)($filters['active'] ?? 1); }
        if (!empty($filters['category_id'])) { $where[]='l.category_id=:category_id'; $params['category_id']=(int)$filters['category_id']; }
        if (!empty($filters['favorite'])) $where[]='l.is_favorite=1';
        if (!empty($filters['q'])) {
            $where[]='(l.name LIKE :q OR l.description LIKE :q OR l.url LIKE :q OR c.name LIKE :q)';
            $params['q']='%'.trim((string)$filters['q']).'%';
        }
        $sql = "SELECT l.*,c.name category_name,c.icon category_icon,c.color category_color,
                COUNT(u.id) usage_count,MAX(u.accessed_at) last_accessed_at
                FROM abs_quick_links l
                LEFT JOIN abs_quick_link_categories c ON c.id=l.category_id
                LEFT JOIN abs_quick_link_usage u ON u.link_id=l.id"
                .($where?' WHERE '.implode(' AND ',$where):'').
                " GROUP BY l.id ORDER BY l.is_favorite DESC,l.sort_order ASC,l.name ASC";
        $q=Database::prepare($sql); $q->execute($params); return $q->fetchAll();
    }

    public function find(int $id): ?array
    {
        $this->ensureSchema();
        $q=Database::prepare('SELECT * FROM abs_quick_links WHERE id=?');$q->execute([$id]);$r=$q->fetch();return $r?:null;
    }

    public function save(array $d, int $userId, ?int $id = null): int
    {
        $this->ensureSchema();
        $url=trim((string)($d['url']??''));
        if ($url==='' || !filter_var($url,FILTER_VALIDATE_URL)) throw new RuntimeException('URL-ul introdus nu este valid. Include https://');
        $name=trim((string)($d['name']??''));
        if ($name==='') throw new RuntimeException('Numele linkului este obligatoriu.');
        $values=[
            'category_id'=>(int)($d['category_id']??0)?:null,
            'supplier_id'=>(int)($d['supplier_id']??0)?:null,
            'name'=>$name,'url'=>$url,
            'description'=>trim((string)($d['description']??''))?:null,
            'logo_path'=>trim((string)($d['logo_path']??''))?:null,
            'open_new_tab'=>!empty($d['open_new_tab'])?1:0,
            'is_favorite'=>!empty($d['is_favorite'])?1:0,
            'active'=>!empty($d['active'])?1:0,
            'sort_order'=>(int)($d['sort_order']??0),
        ];
        if ($id) {
            $values['id']=$id;
            $sql='UPDATE abs_quick_links SET category_id=:category_id,supplier_id=:supplier_id,name=:name,url=:url,description=:description,logo_path=:logo_path,open_new_tab=:open_new_tab,is_favorite=:is_favorite,active=:active,sort_order=:sort_order WHERE id=:id';
            Database::prepare($sql)->execute($values); return $id;
        }
        $values['created_by']=$userId;
        $sql='INSERT INTO abs_quick_links(category_id,supplier_id,name,url,description,logo_path,open_new_tab,is_favorite,active,sort_order,created_by) VALUES(:category_id,:supplier_id,:name,:url,:description,:logo_path,:open_new_tab,:is_favorite,:active,:sort_order,:created_by)';
        Database::prepare($sql)->execute($values); return (int)Database::lastInsertId();
    }

    public function delete(int $id): void { $this->ensureSchema(); Database::prepare('DELETE FROM abs_quick_links WHERE id=?')->execute([$id]); }
    public function favorite(int $id): bool
    {
        $this->ensureSchema();
        Database::prepare('UPDATE abs_quick_links SET is_favorite=IF(is_favorite=1,0,1) WHERE id=?')->execute([$id]);
        $r=$this->find($id); return (bool)($r['is_favorite']??false);
    }
    public function reorder(array $ids): void
    {
        $this->ensureSchema(); $q=Database::prepare('UPDATE abs_quick_links SET sort_order=? WHERE id=?');
        foreach(array_values($ids) as $i=>$id) $q->execute([($i+1)*10,(int)$id]);
    }
    public function recordUsage(int $id, int $userId): void
    {
        $this->ensureSchema(); Database::prepare('INSERT INTO abs_quick_link_usage(link_id,user_id) VALUES(?,?)')->execute([$id,$userId?:null]);
    }
    public function summary(): array
    {
        $this->ensureSchema();
        $q=Database::prepare("SELECT COUNT(*) active,COALESCE(SUM(is_favorite=1),0) favorites FROM abs_quick_links WHERE active=1");$q->execute();$r=$q->fetch();
        return ['active'=>(int)($r['active']??0),'favorites'=>(int)($r['favorites']??0)];
    }

    public function importSuppliers(int $userId): int
    {
        $this->ensureSchema();
        if (!$this->tableExists('md_suppliers')) return 0;
        $cat=$this->categoryIdBySlug('cataloage-piese');
        $q=Database::prepare("SELECT id,name,website,logo_path FROM md_suppliers WHERE status='active' AND is_blocked=0 AND website IS NOT NULL AND TRIM(website)<>'' ORDER BY name");$q->execute();
        $insert=Database::prepare("INSERT IGNORE INTO abs_quick_links(category_id,supplier_id,name,url,logo_path,open_new_tab,active,sort_order,created_by) VALUES(?,?,?,?,?,1,1,?,?)");
        $count=0;$order=100;
        foreach($q->fetchAll() as $s){
            $url=trim((string)$s['website']); if(!preg_match('~^https?://~i',$url))$url='https://'.$url;
            $insert->execute([$cat,(int)$s['id'],$s['name'],$url,$s['logo_path']?:null,$order,$userId]);
            if($insert->rowCount()>0)$count++;$order+=10;
        }
        return $count;
    }

    private function categoryIdBySlug(string $slug): ?int { $q=Database::prepare('SELECT id FROM abs_quick_link_categories WHERE slug=?');$q->execute([$slug]);$r=$q->fetch();return $r?(int)$r['id']:null; }
    private function tableExists(string $table): bool { $q=Database::prepare('SELECT COUNT(*) c FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=?');$q->execute([$table]);$r=$q->fetch();return (int)($r['c']??0)>0; }
}