from sqlite3 import Cursor
import time
from typing import List
from PyQt5 import QtCore
from datetime import date

from todo.model.task import Task, ValidTaskForm
from todo.model.status import Status
from todo.model import status

def get(cursor: Cursor, s: Status) -> List[Task]:
    cursor.execute(
        " SELECT"
        "   id,"
        "   created_at,"
        "   updated_at,"
        "   name,"
        "   due_date,"
        "   description"
        " FROM"
        "   tasks"
        " WHERE"
        "   status = ?",
        (status.format(s),))

    res = []

    for task in cursor.fetchall():
        res.append(Task(
            id = task[0],
            created_at = task[1],
            updated_at = task[2],
            name = task[3],
            due_date = date.fromisoformat(task[4]) if task[4] else None,
            description = task[5]
        ))

    return res

def insert(cursor: Cursor, s: Status, form: ValidTaskForm):
    now = int(time.time())
    cursor.execute(
        " INSERT INTO tasks("
        "   created_at,"
        "   updated_at,"
        "   name,"
        "   due_date,"
        "   description,"
        "   status"
        " ) VALUES (?, ?, ?, ?, ?, ?)",
        (now, now, form.name, form.due_date.isoformat() if form.due_date else "", form.description, status.format(s)))

    return Task(
        id = cursor.lastrowid,
        created_at = now,
        updated_at = now,
        name = form.name,
        due_date = form.due_date,
        description = form.description
    )

def update(cursor: Cursor, task: Task, form: ValidTaskForm):
    now = int(time.time())

    cursor.execute(
        " UPDATE tasks SET"
        "   updated_at = ?,"
        "   name = ?,"
        "   due_date = ?,"
        "   description = ?"
        " WHERE id = ?",
        (now, form.name, form.due_date.isoformat() if form.due_date else "", form.description, task.id))

    return Task(
        id = task.id,
        created_at = task.created_at,
        updated_at = now,
        name = form.name,
        due_date = form.due_date,
        description = form.description
    )

def delete(cursor: Cursor, ids: List[int]):
    if len(ids) >= 1:
        cursor.execute(
            "DELETE FROM tasks WHERE id IN (%s)" % ",".join("?"*len(ids)),
            ids)

def update_status(cursor: Cursor, ids: List[int], s: Status):
    if len(ids) >= 1:
        cursor.execute(
            "UPDATE tasks SET status = ? WHERE id IN (%s)" % ",".join("?"*len(ids)),
            [status.format(s)] + ids)