from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import and_
from sqlalchemy_utils import EncryptedType
from flask_login import UserMixin
from werkzeug.security import check_password_hash, generate_password_hash
from project.config import Config
from flask_migrate import Migrate

db = SQLAlchemy()
migrate = Migrate()


def create_db():
    db.create_all()
    empty = db.session.query(User).first() is None
    if empty:
        db.session.add(User(
            username="game_master",
            password="accessgames1040"
        ))
        db.session.commit()


def reset_db():
    try:
        db.drop_all()
        create_db()
    except Exception as e:
        print(e)


def get_all_games():
    return db.session.query(Game, Player, Object).join(Player).join(Object, and_(Player.game_id == Object.game_id,
                                                                                 Player.player_number == Object.player_number),
                                                                    isouter=True).order_by(Game.id.desc(),
                                                                                           Player.player_number.asc()).all()


def get_all_images():
    return db.session.query(Image).order_by(Image.game_id.desc()).all()


def get_game_images(id):
    return db.session.query(Image).filter(Image.game_id == id).all()


def get_all_action_cards():
    return db.session.query(ActionCard).all()


def get_all_char_cards():
    return db.session.query(CharacterCard).all()


def get_all_goal_cards():
    return db.session.query(GoalCard).all()


# singleplayer table, just for singleplayer games, saves game id and points for 5 players
class SingleplayerGame(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    player_1_points = db.Column(db.Integer, default=0, nullable=False)
    player_2_points = db.Column(db.Integer, default=0, nullable=False)
    player_3_points = db.Column(db.Integer, default=0, nullable=False)
    player_4_points = db.Column(db.Integer, default=0, nullable=False)
    player_5_points = db.Column(db.Integer, default=0, nullable=False)


# for now i removed game_instance since sqlite does not support sequences

PlayerActionCard = db.Table('PlayerActionCard',

                            db.Column("player",
                                      db.Integer,
                                      db.ForeignKey('player.id'),
                                      primary_key=True),

                            db.Column("action_card",
                                      db.Integer,
                                      db.ForeignKey('action_card.card_id'),
                                      primary_key=True)
                            )


class Game(db.Model):
    __tablename__ = "game"
    id = db.Column(db.Integer, primary_key=True)
    # game_instance = db.Column(db.Integer, nullable=False, unique=True, )
    number_of_players = db.Column(db.Integer, nullable=False)
    location = db.Column(db.String(200), nullable=False)
    # current_player = db.Column(db.Integer, default=1, nullable=False)
    closed = db.Column(db.Boolean, default=False, nullable=False)
    players = db.relationship("Player", back_populates="game", cascade="all, delete", passive_deletes=True)
    objects = db.relationship("Object", back_populates="game", cascade="all, delete", passive_deletes=True, )
    images = db.relationship("Image", back_populates="game", cascade="all, delete", passive_deletes=True, )


def turn_default(context):
    return context.get_current_parameters()["player_number"] == 1


class CharacterCard(db.Model):
    __tablename__ = "character_card"
    id = db.Column(db.Integer, primary_key=True)
    card_id = db.Column(db.Integer, unique=True)
    name = db.Column(db.String(100))
    age = db.Column(db.Integer)
    role = db.Column(db.String(100))
    interest = db.Column(db.String(300))
    quote = db.Column(db.String(300))
    img_path = db.Column(db.String(300), nullable=False, unique=True)
    players = db.relationship("Player", back_populates="character_card")


class GoalCard(db.Model):
    __tablename__ = "goal_card"
    id = db.Column(db.Integer, primary_key=True)
    card_id = db.Column(db.Integer, unique=True)
    goal = db.Column(db.String(500))
    img_path = db.Column(db.String(300), nullable=False, unique=True)
    players = db.relationship("Player", back_populates="goal_card")


class ActionCard(db.Model):
    __tablename__ = "action_card"
    id = db.Column(db.Integer, primary_key=True)
    card_id = db.Column(db.Integer, unique=True)
    action = db.Column(db.String(500))
    img_path = db.Column(db.String(300), nullable=False, unique=True)


# player_number : 1 - n (n = number_of_players from game)
class Player(db.Model):
    __tablename__ = "player"
    id = db.Column(db.Integer, primary_key=True)
    # game_instance = db.Column(db.Integer, db.ForeignKey(Game.game_instance), nullable=False)
    game_id = db.Column(db.Integer, db.ForeignKey(Game.id, ondelete="CASCADE"), nullable=False)
    player_number = db.Column(db.Integer, nullable=False)
    is_taken = db.Column(db.Boolean, default=False, nullable=False)
    # is_player_turn = db.Column(db.Boolean, default=turn_default, nullable=False)
    points = db.Column(db.Integer, default=0, nullable=False)
    goal_card_id = db.Column(db.Integer, db.ForeignKey(GoalCard.card_id), nullable=True)
    character_card_id = db.Column(db.Integer, db.ForeignKey(CharacterCard.card_id), nullable=True)
    game = db.relationship(Game, back_populates="players")
    character_card = db.relationship('CharacterCard', back_populates="players")
    goal_card = db.relationship('GoalCard', back_populates="players")
    drawn_action_cards = db.relationship('ActionCard',
                                         secondary=PlayerActionCard,
                                         lazy=True,
                                         backref=db.backref('players', lazy=True))


# stores the objects which are in play with affiliated game and player ids
class Object(db.Model):
    __tablename__ = "object"
    id = db.Column(db.Integer, primary_key=True)
    # game_instance = db.Column(db.Integer, db.ForeignKey(Game.game_instance), nullable=False)
    game_id = db.Column(db.Integer, db.ForeignKey(Game.id, ondelete="CASCADE"), nullable=False)
    player_number = db.Column(db.Integer, nullable=False)
    object_type = db.Column(db.Integer, nullable=False)
    object_points = db.Column(db.Integer, nullable=False)
    longitude = db.Column(db.String(200), nullable=True)
    latitude = db.Column(db.String(200), nullable=True)
    object_name = db.Column(db.String(200), nullable=True)
    game = db.relationship(Game, back_populates="objects")
    __table_args__ = (db.UniqueConstraint('game_id', 'object_type'),)

    def to_json(self):
        return {
            'owner': self.player_number,
            'latitude': self.latitude,
            'longitude': self.longitude,
            'object_name': self.object_name
        }


# in img_path save image to path: "server/images/<game_id>_<img_number>.png"
class Image(db.Model):
    __tablename__ = "image"
    id = db.Column(db.Integer, primary_key=True)
    # game_instance = db.Column(db.Integer, db.ForeignKey(Game.game_instance))
    game_id = db.Column(db.Integer, db.ForeignKey(Game.id, ondelete="CASCADE"))
    img_path = db.Column(db.String(300), nullable=False, unique=True)
    game = db.relationship(Game, back_populates="images")


class User(db.Model, UserMixin):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(EncryptedType(db.String(200), key=Config.SECRET_KEY), nullable=False)
    password = db.Column(EncryptedType(db.String(200), key=Config.SECRET_KEY), nullable=False)

    def __init__(self, username, password):
        self.username = username
        self.password = generate_password_hash(password)

    def __repr__(self):
        return f'<User {self.username}>'

    def verify_password(self, pwd):
        return check_password_hash(self.password, pwd)