390 lines
15 KiB
Python
390 lines
15 KiB
Python
#! .\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) |