from crawler import Location, Meal
from collections import namedtuple
import sqlite3

Schedule = namedtuple('Schedule', 'time, day_week, location, meal, user_id, created_at')

connection = sqlite3.connect("db", isolation_level=None, check_same_thread=False)

# Create tables
with connection:
    connection.execute('''
    CREATE TABLE IF NOT EXISTS schedule (
        time TEXT NOT NULL,
        day_week INT NOT NULL,
        location TEXT NOT NULL,
        meal TEXT NOT NULL,
        user_id INT NOT NULL,
        created_at DATETIME NOT NULL
    )
    ''')

def get_schedules_for_user(user_id):
    cur = connection.execute('''
    SELECT time, day_week, location, meal, user_id, created_at
    FROM schedule
    WHERE user_id = ?
    ''', (user_id,))
    rows = cur.fetchall()
    return [Schedule(
        row[0],
        row[1],
        Location[row[2]],
        Meal[row[3]],
        row[4],
        row[5]
    ) for row in rows]

def insert_schedule(schedule):
    connection.execute('''
    INSERT INTO schedule
    (time, day_week, location, meal, user_id, created_at)
    VALUES
    (?, ?, ?, ?, ?, ?)
    ''', (
        schedule.time,
        schedule.day_week,
        schedule.location.name,
        schedule.meal.name,
        schedule.user_id,
        schedule.created_at
    ))

def get_schedules_matching_time(time):
    cur = connection.execute('''
    SELECT time, day_week, location, meal, user_id, created_at
    FROM schedule
    WHERE time = ? and day_week = ?
    ''', (time.strftime('%H:%M'), time.weekday()))
    rows = cur.fetchall()
    return [Schedule(
        row[0],
        row[1],
        Location[row[2]],
        Meal[row[3]],
        row[4],
        row[5]
    ) for row in rows]

def delete_all_schedules_from_user(user_id):
    connection.execute('''
    DELETE FROM schedule
    WHERE user_id = ?
    ''', (user_id,))