from datetime import datetime
from models.buyer_intake import BuyerIntake, BuyerLocation, BuyerIndustry
from schemas.buyer_intake_form import BuyerOut


class BuyerIntakeService:
    @staticmethod
    async def insert_new_record(db, user_id, buyer_intake, locations, industries):
        """
        Insert a new record into the financial_information_pages table using MySQLDB's execute_insert.

        :param db: An instance of MySQLDB
        :param kwargs: Dictionary of column values for the new record
        """
        saved_buyer = await db.execute_insert(buyer_intake)

        # Check if the insert operation was successful
        if saved_buyer is None:
            raise ValueError(
                "Failed to insert buyer intake record. The returned object is None."
            )

        buyer_locations = [
            BuyerLocation(
                buyer_intake_id=saved_buyer.id,
                location=loc,
                created_by=user_id,
            ).update_modify(user_id)
            for loc in locations
        ]

        await db.execute_bulk_insert(buyer_locations)

        buyer_industries = [
            BuyerIndustry(
                buyer_intake_id=saved_buyer.id,
                industry=ind,
                created_by=user_id,
            ).update_modify(user_id)
            for ind in industries
        ]

        await db.execute_bulk_insert(buyer_industries)

        return saved_buyer

    @staticmethod
    async def get_buyer_intake_form(db, buyer_intake_id):
        """
        Get a buyer intake form by id
        """
        query = """
            SELECT
                bif.*,
                GROUP_CONCAT(DISTINCT bl.location) AS locations,
                GROUP_CONCAT(DISTINCT bi.industry) AS industries
            FROM buyer_intake_form bif
            LEFT JOIN buyer_intake_locations bl ON bif.id = bl.buyer_intake_id
            LEFT JOIN buyer_intake_industries bi ON bif.id = bi.buyer_intake_id
            WHERE bif.id = :buyer_intake_id
            GROUP BY bif.id;
        """

        values = {"buyer_intake_id": buyer_intake_id}
        result = await db.execute(query, values)

        # Assuming the result is a list of dictionaries
        if result is None:
            return None
        buyer_out_instances = [
            BuyerOut(
                **{
                    "id": row[0],
                    "first_name": row[1],
                    "last_name": row[2],
                    "email": row[3],
                    "phone": row[4],
                    "additional_info": row[5],
                    "deal_size": row[6],
                    "revenue_multiple": row[7],
                    "ebidta_multiple": row[8],
                    "irr": row[9],
                    "is_financing_needed": bool(row[10]),
                    "time_frame": row[11],
                    "created_at": row[12],
                    "modified_at": row[13],
                    "created_by": row[14],
                    "modified_by": row[15],
                    "locations": (row[16].split(",") if row[16] else []),
                    "industries": (row[17].split(",") if row[17] else []),
                }
            )
            for row in result
        ]
        return buyer_out_instances