from datetime import datetime, timedelta
import logging
import json
from sqlalchemy.exc import NoResultFound
from sqlmodel import select, update, Session
import time

from app.settings import settings
from app.models import DBInstance, InstanceStatus
from app.utils.tidb_serverless import TiDBServerlessClient

logging.basicConfig(
    level=settings.LOG_LEVEL,
    format="[%(filename)s:%(lineno)d | %(funcName)s()] - %(asctime)s - %(levelname)s - %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
)
log = logging.getLogger(__name__)


class DbInstanceRepo:

    def __init__(self, tcs: TiDBServerlessClient):
        self.tcs = tcs

    def get_all_available_db_instance(self, session: Session) -> list[DBInstance]:
        results = session.exec(
            select(DBInstance).where(DBInstance.status == InstanceStatus.FREE)
        ).all()
        return list(results)

    def get_db_instance_by_id(
        self, session: Session, db_instance_id: int
    ) -> DBInstance:
        statement = select(DBInstance).where(DBInstance.id == db_instance_id)
        db_instance = session.exec(statement).first()
        if db_instance is None:
            raise NoResultFound("No database instance found.")
        return db_instance

    def create_branch_instance(self, session: Session, branch_name: str) -> DBInstance:
        try:
            branch_id = self.tcs.create_branch(branch_name)
        except Exception as e:
            raise ValueError("Fail to create branch: {e}")

        log.info("Branch created: %s", branch_id)

        try:
            branch_info = self.tcs.get_branch_info(branch_id)
        except Exception as e:
            raise ValueError(f"Fail to get branch info: {e}")

        while branch_info["state"] == "CREATING":
            time.sleep(10)
            branch_info = self.tcs.get_branch_info(branch_id)

        if (
            "endpoints" not in branch_info
            or "public_endpoint" not in branch_info["endpoints"]
        ):
            raise ValueError(
                f"Cannot get branch host and port with: {json.dumps(branch_info)}"
            )

        host = branch_info["endpoints"]["public_endpoint"]["host"]
        port = branch_info["endpoints"]["public_endpoint"]["port"]

        try:
            username, password = self.tcs.get_user_info(branch_name)
        except Exception as e:
            raise ValueError(f"Fail to get user info: {e}")

        db_instance = DBInstance(
            db_host=host,
            db_port=port,
            db_user=username,
            db_password=password,
            tidb_cloud_cluster_id=branch_info["cluster_id"],
            tidb_cloud_branch_id=branch_id,
            tidb_cloud_branch_display_name=branch_name,
            status=InstanceStatus.FREE,
        )

        session.add(db_instance)
        session.commit()
        return db_instance

    def update_instance_status(
        self, session: Session, db_instance: DBInstance, status: InstanceStatus
    ):
        db_instance.status = status
        session.add(db_instance)
        session.commit()

    def clear_branch_instances(self, session: Session):
        one_hour_ago = datetime.now() - timedelta(seconds=3600)
        db_instances = session.exec(
            select(DBInstance).where(
                DBInstance.status == InstanceStatus.INACTIVE,
                DBInstance.updated_at < one_hour_ago,
            )
        ).all()
        try:
            for db_instance in db_instances:
                self.tcs.delete_branch(db_instance.tidb_cloud_branch_id)
                db_instance.status = InstanceStatus.DELETED
                session.add(db_instance)
            session.commit()
        except Exception as e:
            session.rollback()
            raise ValueError(f"Fail to delete branch: {e}")