db.py

from sqlalchemy import select
from sqlalchemy.pool import NullPool
from sqlmodel import SQLModel
from sqlalchemy.ext.asyncio import create_async_engine
from dal import context
from dal.models import Users,  User_prognoz, Balance_history, Balance_history_refer, \
    User_chat_history, User_scenary, Scenary_compatibility, Scenary_prognoz, Scenary_people, Scenary_dream, User_info,Referer_partners,Referer_partners_links,Activity
import settings
import sqlalchemy as sa
from sqlalchemy.exc import SQLAlchemyError
from datetime import datetime
from sqlalchemy import func
from sqlalchemy import desc

database_url = "postgresql+asyncpg://0123010110_data_admin:0123fituro_Admin010110@" + settings.get_db_host() + ":5432/db"

context.async_session_factory.configure(
    bind=create_async_engine(database_url, poolclass=NullPool, echo=False)
)


async def get_user(user_tg_id):
    try:
        async with context.Session() as session:
            q = select(Users).where(Users.user_tg_id == user_tg_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass

async def get_user_by_id(user_id):
    try:
        async with context.Session() as session:
            q = select(Users).where(Users.id == user_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass

async def get_user_from_name(user_name):
    try:
        async with context.Session() as session:
            q = select(Users).where(Users.user_name == user_name)
            result = await session.execute(q)
            return result.first()
    except:
        pass

async def get_all_users():
    try:
        async with context.Session() as session:
            q = select(Users.user_tg_id)
            result = await session.execute(q)
            return result.fetchall()
    except:
        pass


async def get_anoncement_users(anoncement_id):
    try:
        async with context.Session() as session:
            q = select(Users.user_tg_id)
            result = await session.execute(q)
            return result.fetchall()
    except:
        pass

async def get_all_banned_users():
    try:
        async with context.Session() as session:
            q = select(Users.user_tg_id).where(Users.is_banned == True)
            result = await session.execute(q)
            return result.fetchall()
    except:
        pass

async def update_user(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Users).where(
                    Users.id == obj.id
                ).values(
                    user_tg_id=obj.user_tg_id,
                    chat_id=obj.chat_id,
                    balance=obj.balance,
                    referer_id=obj.referer_id,
                    referer_link=obj.referer_link,
                    partner_referer_link=obj.partner_referer_link,
                    last_update=datetime.now(),
                    scenary_id=obj.scenary_id,
                    is_solvent=obj.is_solvent,
                    is_banned=obj.is_banned,
                    last_step_handler=obj.last_step_handler,
                    subscription_date=obj.subscription_date,
                    last_anoncement_id=obj.last_anoncement_id,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def update_user_last_step_handler(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Users).where(
                    Users.id == obj.id
                ).values(
                    last_step_handler=obj.last_step_handler,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def insert_user(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Users).values(
                    user_tg_id=obj.user_tg_id,
                    chat_id=obj.chat_id,
                    balance=obj.balance,
                    referer_id=obj.referer_id,
                    registration_date=datetime.now(),
                    referer_link=obj.referer_link,
                    last_update=datetime.now(),
                    scenary_id=obj.scenary_id,
                    is_solvent=obj.is_solvent,
                    is_banned=obj.is_banned,
                    last_step_handler=obj.last_step_handler,
                    last_anoncement_id=obj.last_anoncement_id,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Users).where(
                    Users.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_prognoz(user_id):
    try:
        async with context.Session() as session:
            q = select(User_prognoz).where(User_prognoz.user_id == user_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass


async def get_users_prognoz_is_ready():
    try:
        async with context.Session() as session:
            q = select(User_prognoz, Users).join(Users, Users.id == User_prognoz.user_id, isouter=True)\
                .where(User_prognoz.is_active == True)\
                .where(User_prognoz.is_ready == True)
            result = await session.execute(q)
            return result.fetchall()
    except:
        pass

async def get_users_prognoz_is_not_ready():
    try:
        async with context.Session() as session:
            q = select(User_prognoz, Users).join(Users, Users.id == User_prognoz.user_id, isouter=True)\
                .where(User_prognoz.is_active == True)\
                .where(User_prognoz.town != None)\
                .where(User_prognoz.description == None)
            result = await session.execute(q)
            return result.fetchall()
    except:
        pass

async def update_prognoz(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(User_prognoz).where(
                    User_prognoz.user_id == obj.user_id
                ).values(
                    date=obj.date,
                    town=obj.town,
                    lat=obj.lat,
                    lng=obj.lng,
                    tz_info=obj.tz_info,
                    transits=obj.transits,
                    transits_aspects=obj.transits_aspects,
                    is_active=obj.is_active,
                    description=obj.description,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def update_prognoz_ready(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(User_prognoz).where(
                    User_prognoz.user_id == obj.user_id
                ).values(
                    description=obj.description,
                    is_ready=obj.is_ready,
                    date=obj.date,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def insert_prognoz(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(User_prognoz).values(
                    user_id=obj.user_id,
                    date=obj.date,
                    town=obj.town,
                    lat=obj.lat,
                    lng=obj.lng,
                    tz_info=obj.tz_info,
                    transits=obj.transits,
                    transits_aspects=obj.transits_aspects,
                    description=obj.description,
                    is_active=obj.is_active,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(User_prognoz).where(
                    User_prognoz.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def insert_transaction(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Balance_history).values(
                    user_id=obj.user_id,
                    count=obj.count,
                    is_up_operation=obj.is_up_operation,
                    create_date=obj.create_date,
                    confirm_date=obj.confirm_date,
                    decline_date=obj.decline_date,
                    pay_type=obj.pay_type,
                    price=obj.price,
                    transaction_id=obj.transaction_id,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Balance_history).where(
                    Balance_history.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def insert_chat_history(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(User_chat_history).values(
                    user_id=obj.user_id,
                    date=obj.date,
                    user_question=obj.user_question,
                    bot_answer=obj.bot_answer,
                    type=obj.type,
                    is_closed=obj.is_closed,
                    scenary_id=obj.scenary_id,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(User_chat_history).where(
                    User_chat_history.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_user_chat_history(obj):
    async with context.Session() as session:
        try:
            rows = await session.execute(
                select(User_chat_history).where(
                    User_chat_history.user_id == obj.id
                ).where(User_chat_history.is_closed == False
                        ).order_by(User_chat_history.date)
            )
            return rows.fetchall()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def close_chat_history(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(User_chat_history).where(
                    User_chat_history.user_id == obj.id
                ).where(User_chat_history.is_closed == False
                        ).values(
                    is_closed=True,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def insert_referer_payment(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Balance_history_refer).values(
                    user_refer_id=obj.user_refer_id,
                    follower_id=obj.follower_id,
                    summ=obj.summ,
                    date=obj.date,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Balance_history_refer).where(
                    Balance_history_refer.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def update_referer_payment(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Balance_history_refer).where(
                    Balance_history_refer.id == obj.id
                ).values(
                    received=obj.received,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def insert_scenary(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(User_scenary).values(
                    user_id=obj.user_id,
                    type=obj.type,
                    date=obj.date,
                )
            )
            await session.commit()
            return response.inserted_primary_key[0]
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_scenary(user):
    async with context.Session() as session:
        try:
            rows = await session.execute(
                select(User_scenary).where(
                    User_scenary.id == user.scenary_id
                ).where(User_scenary.is_closed == False or User_scenary.is_closed == None
                        )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def update_scenary(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(User_scenary).where(
                    User_scenary.id == obj.id
                ).values(
                    is_closed=obj.is_closed,
                    spent_kosmik=obj.spent_kosmik,
                    received_description=obj.received_description,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_scenary_prognoz(scenary_id):
    try:
        async with context.Session() as session:
            q = select(Scenary_prognoz).where(Scenary_prognoz.scenary_id == scenary_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass

async def get_user_scenary_prognoz_towns(user_id):
    try:
        async with context.Session() as session:
            q = select(func.max(Scenary_prognoz.scenary_id),Scenary_prognoz.town)\
                .where(Scenary_prognoz.user_id == user_id)\
                .where(Scenary_prognoz.town != None)\
                .group_by(Scenary_prognoz.town)
            result = await session.execute(q)
            return result.fetchall()
    except:
        pass

async def update_scenary_prognoz(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Scenary_prognoz).where(
                    Scenary_prognoz.scenary_id == obj.scenary_id
                ).values(
                    date=obj.date,
                    type=obj.type,
                    town=obj.town,
                    lat=obj.lat,
                    lng=obj.lng,
                    tz_info=obj.tz_info,
                    transits=obj.transits,
                    transits_aspects=obj.transits_aspects,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def insert_scenary_prognoz(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Scenary_prognoz).values(
                    user_id=obj.user_id,
                    date=obj.date,
                    type=obj.type,
                    town=obj.town,
                    lat=obj.lat,
                    lng=obj.lng,
                    tz_info=obj.tz_info,
                    transits=obj.transits,
                    transits_aspects=obj.transits_aspects,
                    scenary_id=obj.scenary_id,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Scenary_prognoz).where(
                    Scenary_prognoz.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_scenary_people(scenary_id):
    try:
        async with context.Session() as session:
            q = select(Scenary_people).where(Scenary_people.scenary_id == scenary_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass


async def update_scenary_people(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Scenary_people).where(
                    Scenary_people.scenary_id == obj.scenary_id
                ).values(
                    people_birthday=obj.people_birthday,
                    town=obj.town,
                    tz_info=obj.tz_info,
                    lat=obj.lat,
                    lng=obj.lng,
                    name=obj.name,
                    gender=obj.gender,
                    horoscope=obj.horoscope,
                    aspects=obj.aspects,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise
        except:
            await session.rollback()
            raise


async def insert_scenary_people(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Scenary_people).values(
                    user_id=obj.user_id,
                    scenary_id=obj.scenary_id,
                    people_birthday=obj.people_birthday,
                    town=obj.town,
                    tz_info=obj.tz_info,
                    lat=obj.lat,
                    lng=obj.lng,
                    name=obj.name,
                    gender=obj.gender,
                    horoscope=obj.horoscope,
                    aspects=obj.aspects,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Scenary_people).where(
                    Scenary_people.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_scenary_compatibility(scenary_id):
    try:
        async with context.Session() as session:
            q = select(Scenary_compatibility).where(Scenary_compatibility.scenary_id == scenary_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass


async def update_scenary_compatibility(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Scenary_compatibility).where(
                    Scenary_compatibility.scenary_id == obj.scenary_id
                ).values(
                    partner_birthday=obj.partner_birthday,
                    town=obj.town,
                    tz_info=obj.tz_info,
                    lat=obj.lat,
                    lng=obj.lng,
                    name=obj.name,
                    gender=obj.gender,
                    couple_natal=obj.couple_natal,
                    couple_aspects=obj.couple_aspects,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise
        except:
            await session.rollback()
            raise


async def insert_scenary_compatibility(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Scenary_compatibility).values(
                    user_id=obj.user_id,
                    partner_birthday=obj.partner_birthday,
                    town=obj.town,
                    tz_info=obj.tz_info,
                    lat=obj.lat,
                    lng=obj.lng,
                    name=obj.name,
                    gender=obj.gender,
                    couple_natal=obj.couple_natal,
                    couple_aspects=obj.couple_aspects,
                    scenary_id=obj.scenary_id,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Scenary_compatibility).where(
                    Scenary_compatibility.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_scenary_dream(scenary_id):
    try:
        async with context.Session() as session:
            q = select(Scenary_dream).where(Scenary_dream.scenary_id == scenary_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass


async def update_scenary_dream(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Scenary_dream).where(
                    Scenary_dream.scenary_id == obj.scenary_id
                ).values(
                    description=obj.description,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise
        except:
            await session.rollback()
            raise


async def insert_scenary_dream(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Scenary_dream).values(
                    user_id=obj.user_id,
                    scenary_id=obj.scenary_id,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Scenary_dream).where(
                    Scenary_dream.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_user_info(user_id):
    try:
        async with context.Session() as session:
            q = select(User_info).where(User_info.user_id == user_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass


async def update_user_info(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(User_info).where(
                    User_info.user_id == obj.user_id
                ).values(
                    birthday=obj.birthday,
                    town=obj.town,
                    name=obj.name,
                    tz_info=obj.tz_info,
                    lat=obj.lat,
                    lng=obj.lng,
                    gender=obj.gender,
                    user_name=obj.user_name,
                    language_code=obj.language_code,
                    horoscope=obj.horoscope,
                    aspects=obj.aspects,
                    description=obj.description,
                    review=obj.review,
                    review_date=obj.review_date,
                    is_updated=obj.is_updated,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise
        except:
            await session.rollback()
            raise


async def insert_user_info(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(User_info).values(
                    user_id=obj.user_id,
                    birthday=obj.birthday,
                    town=obj.town,
                    name=obj.name,
                    tz_info=obj.tz_info,
                    lat=obj.lat,
                    lng=obj.lng,
                    gender=obj.gender,
                    user_name=obj.user_name,
                    language_code=obj.language_code,
                    horoscope=obj.horoscope,
                    aspects=obj.aspects,
                    description=obj.description,
                    review=obj.review,
                    review_date=obj.review_date,
                    is_updated=obj.review_date,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(User_info).where(
                    User_info.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def get_referer_partners(user_tg_id):
    try:
        async with context.Session() as session:
            q = select(Referer_partners).where(Referer_partners.user_tg_id == user_tg_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass


async def update_referer_partners(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Referer_partners).where(
                    Referer_partners.user_tg_id == obj.user_tg_id
                ).values(
                    user_tg_id=obj.user_tg_id,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise
        except:
            await session.rollback()
            raise


async def insert_referer_partners(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Referer_partners).values(
                    user_tg_id=obj.user_tg_id,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Referer_partners).where(
                    Referer_partners.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def get_referer_partners_links(user_id):
    try:
        async with context.Session() as session:
            q = select(Referer_partners_links).where(Referer_partners_links.user_id == user_id)
            result = await session.execute(q)
            return result.first()
    except:
        pass


async def update_referer_partners_links(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Referer_partners_links).where(
                    Referer_partners_links.user_id == obj.user_id
                ).values(
                    user_id=obj.user_id,
                    referer_partner_link=obj.referer_partner_link,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise
        except:

            await session.rollback()
            raise


async def insert_referer_partners_links(obj):
    async with context.Session() as session:
        try:
            response = await session.execute(
                sa.insert(Referer_partners_links).values(
                    user_id=obj.user_id,
                    referer_partner_link=obj.referer_partner_link,
                )
            )
            await session.commit()
            rows = await session.execute(
                select(Referer_partners_links).where(
                    Referer_partners_links.id == response.inserted_primary_key[0]
                )
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise

async def get_activity():
    async with context.Session() as session:
        try:
            rows = await session.execute(
                select(Activity).where(Activity.is_ready == True)
                    .where(Activity.is_active == True)
                    .where(Activity.is_closed == False)
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise


async def update_activity(obj):
    async with context.Session() as session:
        try:
            await session.execute(
                sa.update(Activity).where(
                    Activity.id == obj.id
                ).values(
                    name=obj.name,
                    text=obj.text,
                    date=obj.date,
                    bonus_count=obj.bonus_count,
                    is_ready=obj.is_ready,
                    is_active=obj.is_active,
                    type=obj.type,
                    is_closed=obj.is_closed,
                    user_id=obj.user_id,
                    user_name=obj.user_name,
                )
            )
            await session.commit()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise
        except:

            await session.rollback()
            raise

async def get_partner_id(link):
    async with context.Session() as session:
        try:
            rows = await session.execute(
                select(Referer_partners_links.user_id).where(Referer_partners_links.referer_partner_link == link)
            )
            return rows.first()
        except sa.exc.IntegrityError:
            await session.rollback()
            raise