from bottle import get, post, run, request, response
import sqlite3
import json


HOST = 'localhost'
PORT = 7007

conn = sqlite3.connect("movies.db")


def url(resource):
    return f"http://{localhost}:{7007}{resource}"


def format_response(d):
    return json.dumps(d, indent=4) + "\n"


#Pong
@get('/ping')
def ping():
   print ("pong")
   return "pong" + "\n"


@get('/movies')
def get_movies():
    query = """
        SELECT *
        FROM   Movies
        WHERE  1 = 1
        """
    params = []
    if request.query.name:
        query += "AND title = ?"
        params.append(request.query.name)
    if request.query.year:
        query += "AND year = ?"
        params.append(request.query.year)
    c = conn.cursor()
    c.execute(
        query,
        params
    )
    s = [{"title": title, "year": year, "IMBD": IMBD, "length_min": length_min}
         for (title, year, IMBD, length_min) in c]
    response.status = 200
    return json.dumps({"data": s}, indent=4)


@get('/movies/<imdb>')
def get_imbd(imdb):
    c = conn.cursor()
    c.execute(
        """
        SELECT *
        FROM   Movies
        WHERE  IMDB = ?
        """,
        [imdb]


    )
    s = [{"title": title, "year": year, "IMDB": IMDB, "length_min": length_min}
         for (title, year, IMDB, length_min) in c]
    response.status = 200
    return format_response({"data":s})



@get('/performances')
def get_preformance():

    c = conn.cursor()
    c.execute(
        """
        SELECT screen_id, date, time, title, year, remainingSeats, screening.cinema_name
        FROM Screening
        JOIN Movies
        USING (imdb)
        LEFT JOIN Ticket
        USING (screen_id)
        JOIN Cinema
        USING(cinema_name)
        GROUP by screen_id
        """
    )
    s = [{ 'screen_id': screen_id, 'date': date, 'time': time, "title": title, "year": year, 'remainingSeats': remainingSeats, 'cinema_name': cinema_name }
         for (screen_id, date, time, title, year, remainingSeats, cinema_name) in c]
    response.status = 200
    return format_response({"data":s})




def get_cinema_seats(screen_id):
    c = conn.cursor()
    c.execute(
        """
        SELECT cinema.capacity
        FROM Cinema
        JOIN Screening
        USING (cinema_name)
        WHERE screen_id == ?
        """,
        [screen_id]
    )
    g = [int(record[0]) for record in c.fetchall()]
    a = g[0]

    c.execute(
        """
        SELECT count()
        FROM Cinema
        JOIN Screening
        USING (cinema_name)
        JOIN Ticket
        USING (screen_id)
        WHERE screen_id == ?
        """,
        [screen_id]
    )
    m = [int(record[0]) for record in c.fetchall()]
    b = m[0]
    if (b == a):
        return("No seats left.")
    else:
        return("Seats available.")




@post('/performances')
def post_performance():
    response.content_type = 'application/json'
    imdb = request.query.imdb
    theater = request.query.theater
    date = request.query.date
    time = request.query.time


    if not (imdb and theater and date and time):
        response.status = 400
        return format_response({"error": "Missing parameter"})

    c = conn.cursor()
    c.execute(
        """
        INSERT
        INTO   Screening(imdb, cinema_name, date, time, remainingSeats)
        VALUES (?, ?, ?, ?, (SELECT capacity
                             FROM Cinema
                             where cinema_name == ?))
        """,
        [imdb, theater, date, time, theater]
    )
    conn.commit()




@post('/tickets')
def post_ticket():

    screen_id = request.query.performance
    username = request.query.user
    password = request.query.pwd

    if not (screen_id and username and password):
        response.status = 400
        return format_response({"error": "Missing parameter"})

    if get_cinema_seats(screen_id) == "No seats left.":
        response.status = 400
        return format_response({"error": "No seats left"})



    c = conn.cursor()
    c.execute(
        """
        INSERT
        INTO   Ticket(screen_id, username, password)
        VALUES (?, ?, ?)


        """,
        [screen_id, username, password]
    )
    conn.commit()






@post('/reset')
def post_reset():
        c = conn.cursor()
        c.executescript(
        """

        PRAGMA foreign_keys=OFF;

        DROP TABLE IF EXISTS Account;
        DROP TABLE IF EXISTS User;
        DROP TABLE IF EXISTS Ticket;
        DROP TABLE IF EXISTS Screening;
        DROP TABLE IF EXISTS Hall;
        DROP TABLE IF EXISTS Cinema;
        DROP TABLE IF EXISTS Movies;

        PRAGMA foreign_keys=ON;

        CREATE TABLE User (
            ssn int,
            first_name varchar(20),
            last_name varchar(40),
            PRIMARY KEY (ssn)
        );

        CREATE TABLE Account (
            ssn int,
            username varchar(16),
            password varchar(36),
            PRIMARY KEY (username),
            FOREIGN KEY (ssn) REFERENCES User(ssn)
        );

        CREATE TABLE Ticket (
            ticket_id TEXT DEFAULT (lower(hex(randomblob(16)))),
            screen_id varchar(20),
            username varchar(16),
            password varchar(36),

            FOREIGN KEY (username) REFERENCES Account(username),
            FOREIGN KEY (screen_id) REFERENCES Screening(screen_id)
        );

        CREATE TABLE Screening (
            screen_id TEXT DEFAULT (lower(hex(randomblob(16)))),
            cinema_name   varchar(16) not NULL,
            imdb  varchar(16) not NULL,
            date DATE,
            time TIME,
            remainingSeats int,
            PRIMARY KEY (screen_id),
            FOREIGN KEY (cinema_name) REFERENCES Cinema(cinema_name),
            FOREIGN KEY (imdb) REFERENCES Movies(IMDB)
        );



        CREATE TABLE Cinema (
          cinema_name varchar(16) not NULL UNIQUE,
          adress varchar(32),
          capacity int,
         PRIMARY KEY (cinema_name)
        );


        CREATE TABLE Movies (
            title varchar(30) not NULL,
            year int not NULL,
            IMDB varchar(20) not NULL,
            length_min varchar(3) not NULL,
            PRIMARY KEY (IMDB)
        );



        """
    )

        conn.commit()
        c.executescript(
        """
        INSERT INTO User (ssn, first_name, last_name)
        VALUES
            ('930804-4122', 'Alice', 'Persson'),
            ('931004-4123', 'Bob', 'Åke');

        INSERT INTO Account (ssn,username, password)
        VALUES
            ('930804-4122', 'alice', 'dobido'),
            ('931004-4123', 'bob', 'whatsinaname');




        INSERT INTO Cinema(cinema_name, adress, capacity)
        VALUES
            ('Kino', 'Hötorget 3, 103 91 Stockholm', '10'),
            ('Södran', 'Kungsgatan 16, 111 35 Stockholm', '16'),
            ('Skandia', 'Drottninggatan 82, 111 36 Stockholm', '100');



        INSERT INTO Movies(title, year, IMDB, length_min)
        VALUES
            ('The Shape of Water', '2017', 'tt5580390', '144'),
            ('Moonlight', '2016', 'tt4975722', '175'),
            ('Spotlight', '2015', 'tt0468569', '150'),
            ('Birdman', '2014', 'tt2562232', '150');
        """
        )
        conn.commit()
        return "OK"


run(host=HOST, port=PORT, debug=True)