import datetime
from datetime import datetime
import json
from sqlalchemy.exc import IntegrityError
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
import os
import sqlite3

app = Flask(__name__)
database = 'sqlite:///sqlite.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy()
Migrate(app, db)

db.init_app(app)

class Highscore(db.Model):
    __tablename__ = 'highscores'
    id = db.Column(db.Integer, primary_key=True)
    _username = db.Column(db.String(255))
    _hscore = db.Column(db.Integer, unique=False, nullable=False)

    def __init__(self, username, hscore):
        self._username = username
        self._hscore = hscore

    @property
    def username(self):
        return self._username
   
    @username.setter
    def username(self, username):
        self._username = username
   
    @property
    def hscore(self):
        return self._hscore

    @hscore.setter
    def hscore(self, hscore):
        self._hscore = hscore

    def __str__(self):
        return json.dumps(self.read())

    def create(self):
        try:
            existing = Highscore.query.filter_by(_username=self.username).first()
            if existing:
                if self.hscore > existing.hscore:
                    existing.hscore = self.hscore
                    db.session.commit()
                    return existing
                else:
                    return None
            else:
                db.session.add(self)
                db.session.commit()
                return self
        except IntegrityError:
            db.session.remove()
            return None

    def read(self):
        return {'username': self.username, 'hscore': self.hscore}

    def update(self, username, hscore):
        if username != "null" and username != None:
            self.username = username
        if hscore >= self.hscore:
            self.hscore = hscore
        return self
    def delete(self):
        db.session.delete(self)
        db.session.commit()
        return None
def initHighscores():
    with app.app_context():
        db.create_all()
        """Tester data for table"""
        highscore1 = Highscore(username='somewhere', hscore=7)
        highscore2 = Highscore(username='out', hscore=7)
        highscore3 = Highscore(username='there', hscore=7)

        highscores = [highscore1, highscore2, highscore3]

        for highscore in highscores:
            try:
                highscore.create()
            except IntegrityError:
                '''fails with bad or duplicate data'''
                db.session.remove()
                print(f"Records exist, duplicate email, or error: {highscore.username}")
                
        db.session.commit()
                
initHighscores()
def create():
    name = input("Enter your name:")
    uid = input("Enter your user id:")
    password = input("Enter your password")
    dob = input("Enter your date of birth 'YYYY-MM-DD'")
    
    conn = sqlite3.connect(database)

    cursor = conn.cursor()

    try:
        cursor.execute("INSERT INTO users (_name, _uid, _password, _dob) VALUES (?, ?, ?, ?)", (name, uid, password, dob))
        
        conn.commit()
        print(f"A new user record {uid} has been created")
                
    except sqlite3.Error as error:
        print("Error while executing the INSERT:", error)

    cursor.close()
    conn.close()

def update():
    uid = input("Enter user id to update")
    password = input("Enter updated password")
    if len(password) < 2:
        message = "hacked"
        password = 'gothackednewpassword123'
    else:
        message = "successfully updated"

    conn = sqlite3.connect(database)

    cursor = conn.cursor()

    try:
        cursor.execute("UPDATE users SET _password = ? WHERE _uid = ?", (password, uid))
        if cursor.rowcount == 0:
            print(f"No uid {uid} was not found in the table")
        else:
            print(f"The row with user id {uid} the password has been {message}")
            conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the UPDATE:", error)
        
    
    cursor.close()
    conn.close()
    
def delete():
    uid = input("Enter user id to delete")

    conn = sqlite3.connect(database)

    cursor = conn.cursor()
    
    try:
        cursor.execute("DELETE FROM users WHERE _uid = ?", (uid,))
        if cursor.rowcount == 0:
            print(f"No uid {uid} was not found in the table")
        else:
            print(f"The row with uid {uid} was successfully deleted")
        conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the DELETE:", error)
        
    cursor.close()
    conn.close()
    
def menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete")
    if operation.lower() == 'c':
        create()
    elif operation.lower() == 'r':
        read()
    elif operation.lower() == 'u':
        update()
    elif operation.lower() == 'd':
        delete()
    elif len(operation)==0: 
        return
    else:
        print("Please enter c, r, u, or d") 
    menu()
        
try:
    menu()
except:
    print("Perform Jupyter 'Run All' prior to starting menu")