<?php

namespace App\Extensions\Installed\Importer\Services\DatabaseAdapters;

class SupportPalAdapter extends AbstractDatabaseImportAdapter
{
    protected const ANNOUNCEMENT_TYPE = 1;
    protected function softDeleteCondition(string $table, ?string $alias = null): ?string
    {
        return $this->columnExists($table, 'deleted_at')
            ? (($alias ? "{$alias}." : '') . 'deleted_at IS NULL')
            : null;
    }

    protected function whereClause(array $conditions): string
    {
        $conditions = array_filter($conditions);

        return $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
    }

    public static function id(): string
    {
        return 'supportpal';
    }

    public static function label(): string
    {
        return 'SupportPal';
    }

    public static function defaults(): array
    {
        return [
            'chunk_size' => 100,
            'options' => [
                'import_departments' => true,
                'import_categories' => true,
                'import_users' => true,
                'import_operators' => true,
                'import_tickets' => true,
                'import_responses' => true,
                'import_articles' => true,
            ],
        ];
    }

    public static function isPreview(): bool
    {
        return false;
    }

    public function stages(): array
    {
        return [
            'departments',
            'categories',
            'users',
            'operators',
            'tickets',
            'responses',
            'articles',
        ];
    }

    public function count(string $stage): int
    {
        return match ($stage) {
            'departments' => $this->countTable('department'),
            'categories' => $this->countAnnouncementCategories(),
            'users' => $this->countUsers(),
            'operators' => $this->countOperators(),
            'tickets' => $this->countTable('ticket'),
            'responses' => $this->countTable('ticket_message'),
            'articles' => $this->countAnnouncementArticles(),
            default => 0,
        };
    }

    public function fetch(string $stage, int $offset, int $limit): array
    {
        return match ($stage) {
            'departments' => $this->fetchDepartments($offset, $limit),
            'categories' => $this->fetchCategories($offset, $limit),
            'users' => $this->fetchUsers($offset, $limit),
            'operators' => $this->fetchOperators($offset, $limit),
            'tickets' => $this->fetchTickets($offset, $limit),
            'responses' => $this->fetchResponses($offset, $limit),
            'articles' => $this->fetchArticles($offset, $limit),
            default => [],
        };
    }

    public function mapStatus(?int $statusId): string
    {
        return match ($statusId) {
            2 => 'closed',
            3 => 'awaiting reply',
            4 => 'in progress',
            default => 'open',
        };
    }

    public function mapPriority(?int $priorityId): string
    {
        return match ($priorityId) {
            1 => 'low',
            2 => 'medium',
            3 => 'high',
            4 => 'emergency',
            default => 'none',
        };
    }

    public function isOperatorUser(int $userId): bool
    {
        static $cache = null;

        if ($cache === null) {
            $cache = $this->loadOperatorUserIds();
        }

        return in_array($userId, $cache, true);
    }

    public function findUser(int $userId): ?array
    {
        $stmt = $this->connection()->prepare("
            SELECT *
              FROM {$this->table('user')}
             WHERE id = :id
             LIMIT 1
        ");
        $stmt->bindValue(':id', $userId, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetch() ?: null;
    }

    protected function countTable(string $table): int
    {
        if (!$this->tableExists($table)) {
            return 0;
        }

        $conditions = [];
        if ($condition = $this->softDeleteCondition($table)) {
            $conditions[] = $condition;
        }
        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->query("
            SELECT COUNT(*)
              FROM {$this->table($table)}
             {$where}
        ");

        return (int) $stmt->fetchColumn();
    }

    protected function countUsers(): int
    {
        if (!$this->tableExists('user')) {
            return 0;
        }

        $operatorExists = $this->tableExists('operator');
        if ($operatorExists) {
            $userCondition = $this->softDeleteCondition('user', 'u');
            $operatorCondition = $this->softDeleteCondition('operator', 'o');

            $conditions = [];
            if ($userCondition) {
                $conditions[] = $userCondition;
            }
            $conditions[] = 'o.user_id IS NULL';
            $whereClause = $this->whereClause($conditions);

            $stmt = $this->connection()->query("
                SELECT COUNT(*)
                  FROM (
                    SELECT u.id
                      FROM {$this->table('user')} u
                      LEFT JOIN {$this->table('operator')} o
                        ON o.user_id = u.id
                       AND " . ($operatorCondition ?: '1=1') . "
                     {$whereClause}
                  ) AS customers
            ");
            return (int) $stmt->fetchColumn();
        }

        $conditions = [];
        if ($condition = $this->softDeleteCondition('user')) {
            $conditions[] = $condition;
        }
        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->query("
            SELECT COUNT(*)
              FROM {$this->table('user')}
             {$where}
        ");

        return (int) $stmt->fetchColumn();
    }

    protected function countAnnouncementCategories(): int
    {
        $table = $this->categoryTable();
        if (!$table) {
            return 0;
        }

        $alias = 'c';
        $conditions = [];
        if ($condition = $this->softDeleteCondition($table, $alias)) {
            $conditions[] = $condition;
        }
        if ($typeCondition = $this->announcementCategoryCondition($alias, $table)) {
            $conditions[] = $typeCondition;
        }

        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->query("
            SELECT COUNT(*)
              FROM {$this->table($table)} {$alias}
             {$where}
        ");

        return (int) $stmt->fetchColumn();
    }

    protected function countAnnouncementArticles(): int
    {
        $articleTable = $this->articleTable();
        if (!$articleTable) {
            return 0;
        }

        $alias = 'a';
        $conditions = [];
        if ($condition = $this->softDeleteCondition($articleTable, $alias)) {
            $conditions[] = $condition;
        }

        $joins = '';
        $categoryTable = $this->categoryTable();
        $membershipTable = $this->articleCategoryMembershipTable();
        $categoryColumn = $this->articleCategoryColumn($articleTable);

        if ($categoryTable && $membershipTable) {
            $joins = "
              INNER JOIN {$this->table($membershipTable)} m
                ON m.article_id = {$alias}.id
              INNER JOIN {$this->table($categoryTable)} c
                ON c.id = m.category_id
            ";
            if ($categorySoftDelete = $this->softDeleteCondition($categoryTable, 'c')) {
                $conditions[] = $categorySoftDelete;
            }
            if ($categoryCondition = $this->announcementCategoryCondition('c', $categoryTable)) {
                $conditions[] = $categoryCondition;
            }
        } elseif ($categoryTable && $categoryColumn) {
            $joins = "
              INNER JOIN {$this->table($categoryTable)} c
                ON c.id = {$alias}.{$categoryColumn}
            ";
            if ($categorySoftDelete = $this->softDeleteCondition($categoryTable, 'c')) {
                $conditions[] = $categorySoftDelete;
            }
            if ($categoryCondition = $this->announcementCategoryCondition('c', $categoryTable)) {
                $conditions[] = $categoryCondition;
            }
        } elseif ($categoryTable) {
            $joins = "
              INNER JOIN {$this->table($categoryTable)} c
                ON c.article_id = {$alias}.id
            ";
            if ($categorySoftDelete = $this->softDeleteCondition($categoryTable, 'c')) {
                $conditions[] = $categorySoftDelete;
            }
            if ($categoryCondition = $this->announcementCategoryCondition('c', $categoryTable)) {
                $conditions[] = $categoryCondition;
            }
        } elseif ($this->columnExists($articleTable, 'type')) {
            $conditions[] = "{$alias}.type = " . self::ANNOUNCEMENT_TYPE;
        }

        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->query("
            SELECT COUNT(DISTINCT {$alias}.id)
              FROM {$this->table($articleTable)} {$alias}
              {$joins}
             {$where}
        ");

        return (int) $stmt->fetchColumn();
    }
 
    protected function countOperators(): int
    {
        if ($this->tableExists('operator')) {
            return $this->countTable('operator');
        }

        if (!$this->tableExists('user_membership') || !$this->tableExists('user')) {
            return 0;
        }

        $conditions = [];
        if ($condition = $this->softDeleteCondition('user', 'u')) {
            $conditions[] = $condition;
        }
        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->query("
            SELECT COUNT(DISTINCT m.user_id)
              FROM {$this->table('user_membership')} m
              INNER JOIN {$this->table('user')} u
                ON u.id = m.user_id
             {$where}
        ");

        return (int) $stmt->fetchColumn();
    }

    protected function fetchDepartments(int $offset, int $limit): array
    {
        if (!$this->tableExists('department')) {
            return [];
        }

        $conditions = [];
        if ($condition = $this->softDeleteCondition('department')) {
            $conditions[] = $condition;
        }
        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->prepare("
            SELECT *
              FROM {$this->table('department')}
             {$where}
             ORDER BY id ASC
             LIMIT :limit OFFSET :offset
        ");
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    protected function fetchCategories(int $offset, int $limit): array
    {
        $table = $this->categoryTable();
        if (!$table) {
            return [];
        }

        $alias = 'c';
        $conditions = [];
        if ($condition = $this->softDeleteCondition($table, $alias)) {
            $conditions[] = $condition;
        }
        if ($typeCondition = $this->announcementCategoryCondition($alias, $table)) {
            $conditions[] = $typeCondition;
        }
        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->prepare("
            SELECT {$alias}.*
              FROM {$this->table($table)} {$alias}
             {$where}
             ORDER BY {$alias}.id ASC
             LIMIT :limit OFFSET :offset
        ");
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        $rows = $stmt->fetchAll();

        return array_values(array_map(function (array $row) use ($table): array {
            if (!isset($row['name'])) {
                $row['name'] = $row['title'] ?? ($row['label'] ?? "Category {$row['id']}");
            }

            if (!isset($row['status'])) {
                $row['status'] = isset($row['enabled'])
                    ? (int) $row['enabled']
                    : 1;
            }

            return $row;
        }, $rows));
    }

    protected function fetchUsers(int $offset, int $limit): array
    {
        if (!$this->tableExists('user')) {
            return [];
        }

        $operatorFilter = $this->tableExists('operator') ? "
            LEFT JOIN {$this->table('operator')} op
                   ON op.user_id = u.id
                  AND " . ($this->softDeleteCondition('operator', 'op') ?: '1=1') . "
        " : "";

        $selectParts = ['u.*'];
        $organisationJoin = '';

        if ($this->tableExists('user_organisation')) {
            $joinColumn = null;
            foreach (['user_id', 'userID', 'member_id'] as $candidate) {
                if ($this->columnExists('user_organisation', $candidate)) {
                    $joinColumn = $candidate;
                    break;
                }
            }

            if ($joinColumn) {
                $orgSelectParts = [];
                $orgColumns = [
                    'name' => 'organisation_name',
                    'country' => 'organisation_country',
                    'firstname' => 'organisation_firstname',
                    'lastname' => 'organisation_lastname',
                    'first_name' => 'organisation_first_name',
                    'last_name' => 'organisation_last_name',
                ];

                foreach ($orgColumns as $column => $alias) {
                    if ($this->columnExists('user_organisation', $column)) {
                        $orgSelectParts[] = "org.{$column} AS {$alias}";
                    }
                }

                if (!empty($orgSelectParts)) {
                    $selectParts = array_merge($selectParts, $orgSelectParts);
                }

                $organisationJoin = "
              LEFT JOIN {$this->table('user_organisation')} org
                ON org.{$joinColumn} = u.id
            ";
            }
        }

        $selectClause = implode(",\n                   ", $selectParts);

        $conditions = [];
        if ($condition = $this->softDeleteCondition('user', 'u')) {
            $conditions[] = $condition;
        }
        if ($this->tableExists('operator')) {
            $conditions[] = 'op.user_id IS NULL';
        }
        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->prepare("
            SELECT {$selectClause}
              FROM {$this->table('user')} u
              {$operatorFilter}
              {$organisationJoin}
             {$where}
             ORDER BY u.id ASC
             LIMIT :limit OFFSET :offset
        ");
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    protected function fetchOperators(int $offset, int $limit): array
    {
        if ($this->tableExists('operator')) {
            $conditions = [];
            if ($condition = $this->softDeleteCondition('operator', 'op')) {
                $conditions[] = $condition;
            }
            if ($condition = $this->softDeleteCondition('user', 'u')) {
                $conditions[] = $condition;
            }
            $where = $this->whereClause($conditions);

            $stmt = $this->connection()->prepare("
                SELECT op.*,
                       u.*
                  FROM {$this->table('operator')} op
                  INNER JOIN {$this->table('user')} u
                    ON u.id = op.user_id
                 {$where}
                 ORDER BY op.id ASC
                 LIMIT :limit OFFSET :offset
            ");
            $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll();
        }

        if (!$this->tableExists('user_membership') || !$this->tableExists('user')) {
            return [];
        }

        $conditions = [];
        if ($condition = $this->softDeleteCondition('user', 'u')) {
            $conditions[] = $condition;
        }
        $where = $this->whereClause($conditions);

        $departmentSelect = '';
        if ($this->columnExists('user_membership', 'department_id')) {
            $departmentSelect = ', GROUP_CONCAT(DISTINCT m.department_id) AS membership_department_ids';
        }

        $stmt = $this->connection()->prepare("
            SELECT m.user_id AS operator_id,
                   u.*
                   {$departmentSelect}
              FROM {$this->table('user_membership')} m
              INNER JOIN {$this->table('user')} u
                ON u.id = m.user_id
             {$where}
             GROUP BY m.user_id
             ORDER BY m.user_id ASC
             LIMIT :limit OFFSET :offset
        ");
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        $rows = $stmt->fetchAll();

        return array_map(function (array $row): array {
            $row['id'] = (int) ($row['operator_id'] ?? $row['id']);
            $row['user_id'] = (int) ($row['user_id'] ?? $row['id']);
            if (isset($row['membership_department_ids'])) {
                $row['department_ids'] = array_filter(array_map('intval', explode(',', (string) $row['membership_department_ids'])));
                unset($row['membership_department_ids']);
            }
            unset($row['operator_id']);

            return $row;
        }, $rows);
    }

    protected function fetchTickets(int $offset, int $limit): array
    {
        if (!$this->tableExists('ticket')) {
            return [];
        }

        $conditions = [];
        if ($condition = $this->softDeleteCondition('ticket')) {
            $conditions[] = $condition;
        }
        $where = $this->whereClause($conditions);

        $stmt = $this->connection()->prepare("
            SELECT *
              FROM {$this->table('ticket')}
             {$where}
             ORDER BY id ASC
             LIMIT :limit OFFSET :offset
        ");
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    protected function fetchResponses(int $offset, int $limit): array
    {
        if (!$this->tableExists('ticket_message')) {
            return [];
        }

        $messageCondition = $this->softDeleteCondition('ticket_message', 'm');
        $ticketCondition = $this->softDeleteCondition('ticket', 't');
        $where = $this->whereClause(array_filter([$messageCondition, $ticketCondition]));

        $stmt = $this->connection()->prepare("
            SELECT m.*, t.id as ticket_id
              FROM {$this->table('ticket_message')} m
              INNER JOIN {$this->table('ticket')} t
                ON m.ticket_id = t.id
             {$where}
             ORDER BY m.created_at ASC
             LIMIT :limit OFFSET :offset
        ");
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    protected function fetchArticles(int $offset, int $limit): array
    {
        $articleTable = $this->articleTable();
        if (!$articleTable) {
            return [];
        }

        $alias = 'a';
        $conditions = [];
        if ($condition = $this->softDeleteCondition($articleTable, $alias)) {
            $conditions[] = $condition;
        }

        $categoryTable = $this->categoryTable();
        $membershipTable = $this->articleCategoryMembershipTable();
        $categoryColumn = $this->articleCategoryColumn($articleTable);

        $joins = '';
        $selectParts = ["{$alias}.*"];

        if ($categoryTable && $membershipTable) {
            $joins = "
              INNER JOIN {$this->table($membershipTable)} m
                ON m.article_id = {$alias}.id
              INNER JOIN {$this->table($categoryTable)} c
                ON c.id = m.category_id
            ";
            $selectParts[] = 'MIN(m.category_id) AS category_id';
            if ($categorySoftDelete = $this->softDeleteCondition($categoryTable, 'c')) {
                $conditions[] = $categorySoftDelete;
            }
            if ($categoryCondition = $this->announcementCategoryCondition('c', $categoryTable)) {
                $conditions[] = $categoryCondition;
            }
        } elseif ($categoryTable && $categoryColumn) {
            $joins = "
              INNER JOIN {$this->table($categoryTable)} c
                ON c.id = {$alias}.{$categoryColumn}
            ";
            $selectParts[] = "{$alias}.{$categoryColumn} AS category_id";
            if ($categorySoftDelete = $this->softDeleteCondition($categoryTable, 'c')) {
                $conditions[] = $categorySoftDelete;
            }
            if ($categoryCondition = $this->announcementCategoryCondition('c', $categoryTable)) {
                $conditions[] = $categoryCondition;
            }
        } elseif ($categoryTable) {
            $joins = "
              INNER JOIN {$this->table($categoryTable)} c
                ON c.article_id = {$alias}.id
            ";
            $column = $this->columnExists($categoryTable, 'category_id')
                ? 'MIN(c.category_id)'
                : 'MIN(c.id)';
            $selectParts[] = "{$column} AS category_id";
            if ($categorySoftDelete = $this->softDeleteCondition($categoryTable, 'c')) {
                $conditions[] = $categorySoftDelete;
            }
            if ($categoryCondition = $this->announcementCategoryCondition('c', $categoryTable)) {
                $conditions[] = $categoryCondition;
            }
        } elseif ($this->columnExists($articleTable, 'type')) {
            $conditions[] = "{$alias}.type = " . self::ANNOUNCEMENT_TYPE;
        }

        $selectClause = implode(', ', $selectParts);
        $where = $this->whereClause($conditions);
        $groupBy = ($membershipTable || ($categoryTable && !$categoryColumn)) ? "GROUP BY {$alias}.id" : '';

        $stmt = $this->connection()->prepare("
            SELECT {$selectClause}
              FROM {$this->table($articleTable)} {$alias}
              {$joins}
             {$where}
             {$groupBy}
             ORDER BY {$alias}.id ASC
             LIMIT :limit OFFSET :offset
        ");
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        $rows = $stmt->fetchAll();

        if ($rows) {
            foreach ($rows as &$row) {
                if (isset($row['category_id'])) {
                    $row['category_id'] = (int) $row['category_id'];
                }
            }
            unset($row);
        }

        return $rows;
    }

    protected function articleTable(): ?string
    {
        foreach (['selfservice_article', 'article', 'sp_article'] as $table) {
            if ($this->tableExists($table)) {
                return $table;
            }
        }

        return null;
    }

    protected function categoryTable(): ?string
    {
        foreach (['article_category', 'selfservice_category', 'sp_category'] as $table) {
            if ($this->tableExists($table)) {
                return $table;
            }
        }

        return null;
    }

    protected function articleCategoryColumn(string $articleTable): ?string
    {
        foreach (['category_id', 'category'] as $column) {
            if ($this->columnExists($articleTable, $column)) {
                return $column;
            }
        }

        return null;
    }

    protected function articleCategoryMembershipTable(): ?string
    {
        foreach (['article_cat_membership', 'article_category_membership'] as $table) {
            if ($this->tableExists($table)) {
                return $table;
            }
        }

        return null;
    }

    protected function announcementCategoryCondition(?string $alias = null, ?string $table = null): ?string
    {
        $table ??= $this->categoryTable();

        if (!$table || !$this->columnExists($table, 'type')) {
            return null;
        }

        $prefix = $alias ? "{$alias}." : '';

        return "{$prefix}type = " . self::ANNOUNCEMENT_TYPE;
    }

    protected function loadOperatorUserIds(): array
    {
        if ($this->tableExists('operator')) {
            $conditions = [];
            if ($condition = $this->softDeleteCondition('operator')) {
                $conditions[] = $condition;
            }
            $where = $this->whereClause($conditions);

            $stmt = $this->connection()->query("\n                SELECT user_id\n                  FROM {$this->table('operator')}\n                 {$where}\n            ");

            return $stmt ? array_map('intval', array_column($stmt->fetchAll(), 'user_id')) : [];
        }

        if (!$this->tableExists('user_membership')) {
            return [];
        }

        $stmt = $this->connection()->query("\n            SELECT DISTINCT user_id\n              FROM {$this->table('user_membership')}\n        ");

        return $stmt ? array_map('intval', array_column($stmt->fetchAll(), 'user_id')) : [];
    }
}
