<?php

declare(strict_types=1);

namespace App\Services\Google;

use App\Core\DB;
use DateTime;
use PDO;

class GoogleImportService
{
    public function import(): array
    {
        $pdo = DB::connection();

        $stmt = $pdo->query("
            SELECT *
            FROM board_sources
            WHERE source_type = 'google_sheet'
              AND is_active = 1
              AND sync_mode IN ('import', 'import_export')
        ");
        $sources = $stmt->fetchAll();

        $totalCreated = 0;
        $totalUpdated = 0;
        $totalSkipped = 0;
        $totalRows = 0;

        foreach ($sources as $source) {
            $result = $this->importBoard($pdo, $source);

            $totalCreated += $result['created'];
            $totalUpdated += $result['updated'];
            $totalSkipped += $result['skipped'];
            $totalRows += $result['total'];
        }

        return [
            'created' => $totalCreated,
            'updated' => $totalUpdated,
            'skipped' => $totalSkipped,
            'total' => $totalRows,
        ];
    }

    private function importBoard(PDO $pdo, array $source): array
    {
        $boardId = (int)$source['board_id'];
        $spreadsheetId = (string)$source['source_key'];
        $sheetName = (string)$source['sheet_name'];
        $notificationService = new \App\Services\NotificationService();
        $userIds = $this->getAllUserIds($pdo);

        $stmt = $pdo->prepare("
            SELECT target_type, target_key, source_column_name
            FROM board_source_mappings
            WHERE board_source_id = ?
              AND is_active = 1
        ");
        $stmt->execute([$source['id']]);
        $mappings = $stmt->fetchAll();

        if (empty($mappings)) {
            return ['created' => 0, 'updated' => 0, 'skipped' => 0, 'total' => 0];
        }

        $sheets = new GoogleSheetsService($spreadsheetId, $sheetName);
        $rows = $sheets->getRowsRaw();

        $created = 0;
        $updated = 0;
        $skipped = 0;

        $publisher = new \App\Services\WebSocket\EventPublisher();
        $stmt = $pdo->prepare("SELECT code FROM boards WHERE id = ? LIMIT 1");
        $stmt->execute([$boardId]);
        $boardCode = (string)($stmt->fetchColumn() ?: '');

        foreach ($rows as $row) {
            $rowNumber = (int)$row['_row_number'];

            $base = [
                'name' => '',
                'creator_name' => null,
                'assignee_name' => null,
                'description' => null,
                'status' => 'NEW',
                'priority' => 'MEDIUM',
                'crm_id' => null,
                'completed_flag' => 0,
                'task_created_at' => date('Y-m-d H:i:s'),
                'planned_at' => null,
                'completed_at' => null,
            ];

            $custom = [];

            foreach ($mappings as $map) {
                $col = strtoupper((string)$map['source_column_name']);
                $value = $row[$col] ?? null;

                if ($map['target_type'] === 'base') {
                    $this->applyBase($base, (string)$map['target_key'], $value);
                }

                if ($map['target_type'] === 'custom') {
                    $custom[(string)$map['target_key']] = trim((string)$value);
                }
            }

            $normalizedName = mb_strtolower(trim((string)$base['name']));
            if (
                $normalizedName === '' ||
                $normalizedName === 'наименование детали' ||
                $normalizedName === 'наименование'
            ) {
                $skipped++;
                continue;
            }

            $base['status'] = ((int)$base['completed_flag'] === 1) ? 'DONE' : 'IN_PROGRESS';

            $existing = $this->findByRow($pdo, $boardId, $rowNumber);

//            if (!$existing) {
//                $existing = $this->findByName($pdo, $boardId, $base['name']);
//            }

            if ($existing) {
                $taskId = (int)$existing['id'];

                $stmt = $pdo->prepare("
                    UPDATE tasks SET
                        google_row_id = ?,
                        name = ?,
                        description = ?,
                        creator_name = ?,
                        assignee_name = ?,
                        priority = ?,
                        completed_flag = ?,
                        task_created_at = ?,
                        planned_at = ?,
                        completed_at = ?,
                        updated_at = NOW()
                    WHERE id = ?
                ");

                $stmt->execute([
                    $rowNumber,
                    $base['name'],
                    $base['description'],
                    $base['creator_name'],
                    $base['assignee_name'],
                    $base['status'],
                    $base['priority'],
                    $base['completed_flag'],
                    $base['task_created_at'],
                    $base['planned_at'],
                    $base['completed_at'],
                    $taskId
                ]);

                $this->saveCustom($pdo, $taskId, $boardId, $custom);

                $updated++;

                $publisher->publish([
                    'type' => 'task_updated',
                    'task_id' => $taskId,
                    'board_id' => $boardId,
                    'board_code' => $boardCode,
                    'name' => $base['name'],
                    'status' => $base['status'],
                    'updated_at' => date('Y-m-d H:i:s'),
                ]);
            } else {
                $crmId = !empty($base['crm_id'])
                    ? (string)$base['crm_id']
                    : $this->generateCrmId();

                $stmt = $pdo->prepare("
               INSERT INTO tasks (
                crm_id, board_id, google_row_id, source_type,
                name, description, creator_name, assignee_name,
                status, priority, completed_flag,
                task_created_at, planned_at, completed_at,
                created_at, updated_at
                ) VALUES (?, ?, ?, 'google', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
                ");

                $stmt->execute([
                    $crmId,
                    $boardId,
                    $rowNumber,
                    $base['name'],
                    $base['description'],
                    $base['creator_name'],
                    $base['assignee_name'],
                    $base['status'],
                    $base['priority'],
                    $base['completed_flag'],
                    $base['task_created_at'],
                    $base['planned_at'],
                    $base['completed_at'],
                ]);

                $taskId = (int)$pdo->lastInsertId();

                $this->saveCustom($pdo, $taskId, $boardId, $custom);

                $created++;

                $notificationService->createForUsers(
                    $userIds,
                    'task_created',
                    'Новая задача',
                    $base['name'],
                    [
                        'task_id' => $taskId,
                        'board_id' => $boardId,
                        'board_code' => $boardCode,
                        'crm_id' => $crmId,
                    ]
                );

                $publisher->publish([
                    'type' => 'task_created',
                    'task_id' => $taskId,
                    'board_id' => $boardId,
                    'board_code' => $boardCode,
                    'name' => $base['name'],
                    'crm_id' => $crmId,
                    'created_at' => date('Y-m-d H:i:s'),
                ]);
            }
        }

        $publisher->publish([
            'type' => 'import_finished',
            'board_id' => $boardId,
            'board_code' => $boardCode,
            'created' => $created,
            'updated' => $updated,
            'skipped' => $skipped,
            'total' => count($rows),
            'finished_at' => date('Y-m-d H:i:s'),
        ]);

//        if ($created > 0 || $updated > 0) {
//            $notificationService->createForUsers(
//                $userIds,
//                'import_finished',
//                'Импорт завершен',
//                'Новых: ' . $created . ', обновлено: ' . $updated,
//                [
//                    'board_id' => $boardId,
//                    'board_code' => $boardCode,
//                    'created' => $created,
//                    'updated' => $updated,
//                ]
//            );
//        }

        return [
            'created' => $created,
            'updated' => $updated,
            'skipped' => $skipped,
            'total' => count($rows)
        ];
    }

    private function applyBase(array &$base, string $key, mixed $value): void
    {
        $value = trim((string)$value);

        switch ($key) {
            case 'name': $base['name'] = $value; break;
            case 'description': $base['description'] = $value ?: null; break;
            case 'creator_name': $base['creator_name'] = $value ?: null; break;
            case 'assignee_name': $base['assignee_name'] = $value ?: null; break;
            case 'crm_id': $base['crm_id'] = $value ?: null; break;
            case 'completed_flag': $base['completed_flag'] = $this->toBool($value); break;
            case 'task_created_at':
                $base['task_created_at'] = $this->normalizeImportDateTime($value, true);
                break;

            case 'planned_at':
                $base['planned_at'] = $this->normalizeImportDateTime($value, false);
                break;

            case 'completed_at':
                $base['completed_at'] = $this->normalizeImportDateTime($value, false);
                break;
        }
    }

    private function saveCustom(PDO $pdo, int $taskId, int $boardId, array $data): void
    {
        if (!$data) return;

        $stmt = $pdo->prepare("
            SELECT id, code FROM board_fields WHERE board_id = ?
        ");
        $stmt->execute([$boardId]);
        $fields = $stmt->fetchAll();

        $map = [];
        foreach ($fields as $f) $map[$f['code']] = $f['id'];

        foreach ($data as $code=>$val) {

            if (!isset($map[$code])) continue;

            $stmt = $pdo->prepare("
                INSERT INTO task_field_values (task_id, field_id, value_text)
                VALUES (?, ?, ?)
                ON DUPLICATE KEY UPDATE value_text = VALUES(value_text)
            ");

            $stmt->execute([$taskId, $map[$code], $val]);
        }
    }

    private function findByRow(PDO $pdo, int $boardId, int $row): array|false
    {
        $stmt = $pdo->prepare("
            SELECT id FROM tasks WHERE board_id=? AND google_row_id=?
        ");
        $stmt->execute([$boardId, $row]);
        return $stmt->fetch();
    }

    private function findByName(PDO $pdo, int $boardId, string $name): array|false
    {
        $stmt = $pdo->prepare("
            SELECT id FROM tasks WHERE board_id=? AND name=? LIMIT 1
        ");
        $stmt->execute([$boardId, $name]);
        return $stmt->fetch();
    }

    private function generateCrmId(): string
    {
        return 'CRM-' . date('Ymd-His') . '-' . bin2hex(random_bytes(2));
    }

    private function toBool($v): int
    {
        $v = mb_strtolower(trim((string)$v));
        return in_array($v,['1','true','да']) ? 1 : 0;
    }
    private function getAllUserIds(PDO $pdo): array
    {
        $stmt = $pdo->query("SELECT id FROM users");
        return array_map('intval', array_column($stmt->fetchAll(), 'id'));
    }
    private function normalizeImportDateTime(mixed $value, bool $useNowIfEmpty = false): ?string
    {
        $value = trim((string)$value);

        if ($value === '') {
            return $useNowIfEmpty ? date('Y-m-d H:i:s') : null;
        }

        $formats = [
            'd.m.Y H:i:s',
            'd.m.Y H:i',
            'Y-m-d H:i:s',
            'Y-m-d H:i',
            'd.m.Y',
            'Y-m-d',
        ];

        foreach ($formats as $format) {
            $date = \DateTime::createFromFormat($format, $value);
            if ($date instanceof \DateTime) {
                if ($format === 'd.m.Y' || $format === 'Y-m-d') {
                    return $date->format('Y-m-d') . ' 00:00:00';
                }

                return $date->format('Y-m-d H:i:s');
            }
        }

        return $useNowIfEmpty ? date('Y-m-d H:i:s') : null;
    }
}