<?php

class ListNote
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function getAllByUser(int $userId): array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM lists WHERE user_id = :user_id ORDER BY sort_order ASC, updated_at DESC'
        );
        $stmt->execute(['user_id' => $userId]);
        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM lists WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function create(array $data): int
    {
        $sortOrder = $this->getNextSortOrder($data['user_id']);
        
        $stmt = $this->db->prepare(
            'INSERT INTO lists (user_id, title, content, reminder_email, reminder_at, reminder_sent, sort_order)
             VALUES (:user_id, :title, :content, :reminder_email, :reminder_at, 0, :sort_order)'
        );
        $stmt->execute([
            'user_id'        => $data['user_id'],
            'title'          => $data['title'],
            'content'        => $data['content'] ?? '',
            'reminder_email' => $data['reminder_email'] ?? null,
            'reminder_at'    => $data['reminder_at'] ?? null,
            'sort_order'     => $sortOrder,
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $stmt = $this->db->prepare(
            'UPDATE lists SET title = :title, content = :content, reminder_email = :reminder_email, reminder_at = :reminder_at, reminder_sent = :reminder_sent WHERE id = :id'
        );
        return $stmt->execute([
            'title'          => $data['title'],
            'content'        => $data['content'] ?? '',
            'reminder_email' => $data['reminder_email'] ?? null,
            'reminder_at'    => $data['reminder_at'] ?? null,
            'reminder_sent'  => $data['reminder_sent'] ?? 0,
            'id'             => $id,
        ]);
    }

    public function delete(int $id): bool
    {
        $stmt = $this->db->prepare('DELETE FROM lists WHERE id = :id');
        return $stmt->execute(['id' => $id]);
    }

    public function getPendingReminders(): array
    {
        $stmt = $this->db->query(
            'SELECT * FROM lists WHERE reminder_at IS NOT NULL AND reminder_at <= NOW() AND reminder_sent = 0'
        );
        return $stmt->fetchAll();
    }

    public function markReminderSent(int $id): bool
    {
        $stmt = $this->db->prepare('UPDATE lists SET reminder_sent = 1 WHERE id = :id');
        return $stmt->execute(['id' => $id]);
    }

    public function moveUp(int $id, int $userId): bool
    {
        $current = $this->findById($id);
        if (!$current || $current['user_id'] !== $userId) {
            return false;
        }

        $stmt = $this->db->prepare(
            'SELECT * FROM lists WHERE user_id = :user_id AND sort_order < :current_sort ORDER BY sort_order DESC LIMIT 1'
        );
        $stmt->execute(['user_id' => $userId, 'current_sort' => $current['sort_order']]);
        $previous = $stmt->fetch();

        if (!$previous) {
            return false;
        }

        $this->updateSortOrder($id, $previous['sort_order']);
        $this->updateSortOrder($previous['id'], $current['sort_order']);

        return true;
    }

    public function moveDown(int $id, int $userId): bool
    {
        $current = $this->findById($id);
        if (!$current || $current['user_id'] !== $userId) {
            return false;
        }

        $stmt = $this->db->prepare(
            'SELECT * FROM lists WHERE user_id = :user_id AND sort_order > :current_sort ORDER BY sort_order ASC LIMIT 1'
        );
        $stmt->execute(['user_id' => $userId, 'current_sort' => $current['sort_order']]);
        $next = $stmt->fetch();

        if (!$next) {
            return false;
        }

        $this->updateSortOrder($id, $next['sort_order']);
        $this->updateSortOrder($next['id'], $current['sort_order']);

        return true;
    }

    public function getNextSortOrder(int $userId): int
    {
        $stmt = $this->db->prepare(
            'SELECT MAX(sort_order) as max_sort FROM lists WHERE user_id = :user_id'
        );
        $stmt->execute(['user_id' => $userId]);
        $result = $stmt->fetch();
        return ($result['max_sort'] ?? 0) + 1;
    }

    private function updateSortOrder(int $id, int $sortOrder): bool
    {
        $stmt = $this->db->prepare('UPDATE lists SET sort_order = :sort_order WHERE id = :id');
        return $stmt->execute(['sort_order' => $sortOrder, 'id' => $id]);
    }
}