#! .\sbsheriff\scripts\python.exe
import json
from time import time
import disnake
from requests import session
from sqlalchemy import Integer, create_engine, String, Column, ForeignKey,Table,Boolean,BigInteger,SmallInteger,DateTime,func
from sqlalchemy.orm import declarative_base, relationship, scoped_session, sessionmaker
import sqlite3
from dotenv import load_dotenv
from os import getenv

load_dotenv(".env", override=True)

engine = create_engine(getenv("DB_CON"),pool_pre_ping=True)
Base = declarative_base()
Session = sessionmaker(engine)

class World(Base):
    __tablename__ = 'world'
    id = Column(Integer, primary_key=True)
    short_name = Column(String(50))
    world_name = Column(String(50))
    shk_id = Column(Integer)
    age = Column(Integer)
    ended = Column(Boolean)
    hoh_scraped = Column(Boolean)
    servers = relationship('Server',back_populates='worlds') #Checked
    hoh_entries = relationship('Hall_Of_Heroes',back_populates='world') #Checked
    house_histories = relationship('House_History',back_populates='world') #Checked
    parishes = relationship("Parish_Data",back_populates='world')  #Checked

class Player(Base): 
    __tablename__ = 'player'
    id = Column(Integer, primary_key=True)
    player_name = Column(String(100))
    user_id = Column(Integer,ForeignKey('user.id'))

    user = relationship('User',back_populates='player') #Checked
    vm_entries = relationship('Vm_Entry',back_populates='player') #Checked
    hoh_entries = relationship("Hall_Of_Heroes",back_populates="player") #Checked
    house_history = relationship("House_History",back_populates="player") #Checked
    castle_designs = relationship("Player_Castle_Designs",back_populates="player") #Checked
    checks = relationship('Check_Player_Data',back_populates='player')
    world_data = relationship("WorldPlayerData",back_populates="player")#C

class PlayerBan(Base):
    __tablename__ = "player_ban"
    id = Column(Integer,primary_key=True)
    player_id = Column(Integer,ForeignKey("player.id"))
    date_added = Column(DateTime,server_default=func.now())
    last_check = Column(DateTime,server_default=func.now())
    unbanned = Column(Boolean,default=False)
    permanent = Column(Boolean,default=False)

class BanTrackedHouse(Base):
    __tablename__ = "ban_tracked_house"
    id = Column(Integer,primary_key=True)
    house_id = Column(Integer,ForeignKey("house.id"))
    tracked_by_server_id = Column(Integer,ForeignKey("server.id"))

class BanTrackedPlayer(Base):
    __tablename__ = "ban_tracked_player"
    id = Column(Integer,primary_key=True)
    player_id = Column(Integer,ForeignKey("player.id"))
    tracked_by_server_id = Column(Integer,ForeignKey("server.id"))    

server_user = Table(
    "association",
    Base.metadata,
    Column("server_id",ForeignKey("server.id")),
    Column("user_id",ForeignKey("user.id")),
)

class WorldPlayerData(Base):
    __tablename__ = 'world_player_data'
    id = Column(Integer,primary_key=True)
    player_id = Column(Integer,ForeignKey("player.id"))
    world_id = Column(Integer,ForeignKey("world.id"))
    cp_rank = Column(Integer)
    player = relationship("Player",back_populates="world_data")#C

class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True)
    server_name = Column(String)
    discord_guild_id = Column(BigInteger,unique=True)
    world_id = Column(Integer,ForeignKey('world.id'))
    api_key = Column(String)

    worlds = relationship("World",back_populates="servers") #Checked
    users = relationship("User",secondary=server_user,back_populates='servers') #Checked
    roles = relationship("Server_Roles", back_populates="server")
    adverts = relationship("Advert",back_populates="server")


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    discord_id = Column(BigInteger,unique=True)
    display_name = Column(String(50))
    servers = relationship("Server",secondary=server_user,back_populates='users') #Checked

    #Parent in user preferences relationship
    user_preference =  relationship("User_Preference",back_populates="user",uselist=False) #Checked

    #Parent in user player relationship
    player = relationship('Player',back_populates='user',uselist=False) #Checked

    parish_actions =  relationship("Parish_Action",back_populates='user')
    time_data =  relationship("Time_Data",back_populates='user')

class User_Preference(Base):
    __tablename__ = 'user_preference'
    id = Column(Integer,primary_key=True)
    preferred_pikes = Column(Integer)
    preferred_archers = Column(Integer)
    user_id = Column(Integer,ForeignKey('user.id'))
    user = relationship('User',back_populates='user_preference') #Checked

class Advert_Type(Base):
    __tablename__ = 'advert_type'
    id = Column(Integer, primary_key=True)
    advert_name = Column(String(50))
    adverts = relationship("Advert",back_populates="advert_type") #Checked

class Advert(Base):
    __tablename__ = 'advert'
    advert_id = Column(Integer, primary_key=True)
    advert_type_id = Column(SmallInteger, ForeignKey("advert_type.id"))
    server_id = Column(Integer,ForeignKey('server.id'))
    channel_id = Column(BigInteger)
    message_id = Column(BigInteger)
    advert_type = relationship("Advert_Type",back_populates="adverts") #Checked
    server = relationship("Server",back_populates="adverts")

class Vm_Entry(Base):
    __tablename__ = 'vm_entry'
    id = Column(Integer, primary_key=True)
    added_by_user_id = Column(Integer, ForeignKey("user.id"))
    player_id = Column(Integer, ForeignKey("player.id"))
    server_id = Column(Integer, ForeignKey("server.id"))
    time_added = Column(DateTime,server_default=func.now())
    time_ended = Column(DateTime,nullable=True)   
    player = relationship("Player",back_populates="vm_entries") #Checked

class House(Base):
    __tablename__ = 'house'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    color = Column(String)
    emoji = Column(String)
    relationships = relationship("House_Relationship",back_populates="house")

class Relationship_State(Base):
    __tablename__ = 'relationship_state'
    id = Column(Integer, primary_key=True)
    relationship_name = Column(String(50))
    alignment = Column(Integer)
    emoji = Column(String)
    house_relations = relationship('House_Relationship',back_populates='relationship_state') #Checked
    player_relations = relationship('Player_Relationship',back_populates='relationship_state')#Checked

class House_Relationship(Base):
    __tablename__ = 'house_relationship'
    id = Column(Integer, primary_key=True)

    server_id = Column(Integer, ForeignKey("server.id"))
    house_id = Column(Integer, ForeignKey("house.id"))
    house = relationship("House",back_populates="relationships") #Checked
    relationship_state_id = Column(Integer, ForeignKey("relationship_state.id"))
    relationship_state = relationship("Relationship_State",back_populates="house_relations")  #Checked

class Player_Relationship(Base):
    __tablename__ = 'player_relationship'
    id = Column(Integer, primary_key=True)
    note = Column(String(100),nullable=True)

    server_id = Column(Integer, ForeignKey("server.id"))
    player_id = Column(Integer, ForeignKey("player.id"))
    relationship_state_id = Column(Integer, ForeignKey("relationship_state.id"))
    relationship_state = relationship('Relationship_State',back_populates='player_relations')#Checked

class Hoh_Rank(Base):
    __tablename__ = 'hoh_rank'
    id = Column(Integer, primary_key=True)
    rank_name = Column(String(50))
    hoh_entries = relationship("Hall_Of_Heroes",back_populates="rank") #Checked

class Hall_Of_Heroes(Base):
    __tablename__ = 'hall_of_heroes'
    id = Column(Integer,primary_key=True)
    player_id = Column(Integer,ForeignKey('player.id'))
    hoh_rank_id = Column(Integer,ForeignKey('hoh_rank.id'))
    world_id = Column(Integer,ForeignKey('world.id'))

    #Parent in player hoh relationship
    player = relationship('Player',back_populates='hoh_entries') #Checked
    rank = relationship('Hoh_Rank',back_populates='hoh_entries') #Checked
    world = relationship('World',back_populates='hoh_entries') #Checked

class Player_Castle_Designs(Base):
    __tablename__ = "player_castle_designs"
    id = Column(Integer,primary_key=True)
    path = Column(String)
    player_id = Column(Integer,ForeignKey("player.id"))
    server_id = Column(Integer,ForeignKey("server.id"))
    validated = Column(Boolean,default=False)

    player = relationship("Player", back_populates="castle_designs") #Checked
    server = relationship("Server") #Checked

class House_History(Base):
    __tablename__ = 'house_history'
    id = Column(Integer,primary_key=True)
    date = Column(DateTime)

    player_id =  Column(Integer,ForeignKey("player.id"))
    world_id = Column(Integer,ForeignKey("world.id"))
    house_id = Column(Integer,ForeignKey("house.id"))
    player = relationship("Player",back_populates='house_history') #Checked
    world = relationship("World",back_populates='house_histories') #Checked
    house = relationship("House") #Checked

class Liegelord_Requests(Base):
    __tablename__ = 'liegelord_requests'
    id = Column(Integer,primary_key=True)
    village_id = Column(SmallInteger)
    confirm_message_id = Column(BigInteger)

    server_id = Column(Integer,ForeignKey("server.id"))
    request_user_id = Column(Integer,ForeignKey('user.id'))
    fulfilled_user_id = Column(Integer,ForeignKey('user.id'))
    server = relationship("Server") #Checked
    request_user = relationship("User",foreign_keys=[request_user_id])
    fulfilled_user= relationship("User",foreign_keys=[fulfilled_user_id])

class Parish_Data(Base):
    __tablename__ = 'parish_data'
    id = Column(Integer,primary_key=True)
    parish_id = Column(Integer)
    parish_name = Column(String)
    world_id = Column(Integer,ForeignKey('world.id'))

    world = relationship("World",back_populates='parishes') #Checked
    actions = relationship("Parish_Action",back_populates='parish')  #Checked

class Parish_Action(Base):
    __tablename__ = 'parish_action'
    id = Column(Integer,primary_key=True)
    parish_id = Column(Integer,ForeignKey('parish_data.id'))
    performed_by_user_id = Column(Integer,ForeignKey('user.id'))
    performed_at = Column(DateTime,server_default=func.now())
    building_id = Column(Integer,ForeignKey('parish_building.id'))


    parish = relationship("Parish_Data",back_populates = 'actions') #Checked
    building = relationship("Parish_Building",back_populates='actions') #Checked
    user = relationship("User",back_populates='parish_actions') #Checked
    
class Parish_Building(Base):
    __tablename__ = 'parish_building'
    id = Column(Integer,primary_key=True)
    name = Column(String)
    actions = relationship("Parish_Action",back_populates='building') #checked

class User_Interaction(Base):
    __tablename__ = "user_interaction"
    id = Column(Integer,primary_key=True)
    date = Column(DateTime,server_default=func.now())
    action = Column(String)
    user_id =  Column(Integer,ForeignKey("user.id"))
    server_id  = Column(Integer,ForeignKey("server.id"))
    
class Time_Data(Base):
    __tablename__ = 'time_data'
    id = Column(Integer,primary_key = True)
    message_id = Column(BigInteger)
    name = Column(String)
    seconds = Column(Integer)
    multiplier = Column(Integer)
    modifier = Column(Integer)
    user_id = Column(Integer,ForeignKey('user.id'))

    user = relationship("User",back_populates='time_data')

class Check_Player_Data(Base):
    __tablename__ = 'check_player_data'
    id =  Column(Integer,primary_key = True)
    player_id = Column(Integer,ForeignKey('player.id'))

    player = relationship('Player',back_populates='checks')

class AI(Base):
    __tablename__ = 'ai'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class AI_Castle(Base):
    __tablename__ = 'ai_castle'
    id = Column(Integer, primary_key=True)
    ai_id = Column(Integer, ForeignKey('ai.id'))
    ai_level = Column(Integer)
    archers = Column(Integer)
    pikemen = Column(Integer)
    catapults = Column(Integer)
    swordmen = Column(Integer)
    captains = Column(Integer)
    parish_compatible = Column(Boolean)
    img_path = Column(String)

class Role_Type(Base):
    __tablename__ = 'role_type'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class Server_Roles(Base):
    __tablename__ = 'server_roles'
    id = Column(Integer, primary_key=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    role_type_id = Column(Integer, ForeignKey('role_type.id'))
    discord_role_id = Column(BigInteger)

    server = relationship("Server",back_populates="roles")




def get_session():
    return Session

def log_interaction(inter:disnake.ApplicationCommandInteraction,action:str):
    with Session() as session:
        #Get or Create User
        user = session.query(User).filter(User.discord_id==inter.author.id).first() 
        server = session.query(Server).filter(Server.discord_guild_id==inter.guild_id).first()
        if user == None:
            #Create user object and assosiate with server
            print(f"adding new user to database -> {inter.author.display_name}")
            user = User(display_name=inter.author.display_name,discord_id=inter.author.id)
            session.add(user)
            session.commit()
        if not server in user.servers:
            print(f"associating new user to server -> {inter.author.display_name} -> {inter.guild.name}")
            user.servers.append(server)
        interaction = User_Interaction(user_id=user.id,server_id=server.id,action=action)
        session.add(interaction)
        session.commit()

def add_players_from_search(json:dict):
    with Session() as session:
        for entry in json:
            player = session.query(Player).filter(Player.player_name==entry).first()
            if player == None:
                player = Player(player_name=entry)
                session.add(player)
        session.commit()

def get_or_create(session, model, defaults=None, **kwargs):
    instance = session.query(model).filter_by(**kwargs).one_or_none()
    if instance:
        return instance, False
    else:
        kwargs |= defaults or {}
        instance = model(**kwargs)
        try:
            session.add(instance)
            session.commit()
        except Exception:
            session.rollback()
            instance = session.query(model).filter_by(**kwargs).one()
            return instance, False
        else:
            return instance, True

def get_adverts():
    with Session() as session:
        return session.query(Advert_Type).all()

def get_advert_type_by_name(name: str):
    with Session() as session:
        advert_id = session.query(Advert_Type).filter_by(advert_name=name).one_or_none()
        if advert_id:
            return advert_id.id
        else: 
            return None
def get_ai_castles(ai_id: int):
    with Session() as session:
        return session.query(AI_Castle).filter(AI_Castle.ai_id == ai_id).all()

#Base.metadata.create_all(bind=engine)