import requests
from decimal import getcontext, Decimal, ROUND_HALF_UP
from datetime import datetime, timedelta
from trytond.model import fields
from trytond.model import ModelSQL, ModelView
from trytond.pool import Pool, PoolMeta
from trytond.transaction import Transaction
import logging
from sql import Table
import traceback

logger = logging.getLogger(__name__)


class Cron(metaclass=PoolMeta):
    __name__ = 'ir.cron'

    @classmethod
    def __setup__(cls):
        super().__setup__()
        cls.method.selection.append(
            ('automation.cron|update_shipment',
                "Update Shipment from freight booking info")
        )


class AutomationCron(ModelSQL, ModelView):
    "Automation Cron"
    __name__ = 'automation.cron'

    frequency = fields.Selection([
        ('daily', "Daily"),
        ('weekly', "Weekly"),
        ('monthly', "Monthly"),
    ], "Frequency", required=True,
        help="How frequently rates must be updated.")

    last_update = fields.Date("Last Update", required=True)

    @classmethod
    def run(cls, crons):
        cls.update_shipment()

    @classmethod
    def update_shipment(cls):
        PoolObj = Pool()
        ShipmentIn = PoolObj.get('stock.shipment.in')
        Party = PoolObj.get('party.party')
        Vessel = PoolObj.get('trade.vessel')
        Location = PoolObj.get('stock.location')

        # Table externe
        t = Table('freight_booking_info')
        cursor = Transaction().connection.cursor()
        cursor.execute(*t.select(
            t.ShippingInstructionNumber,
            t.ShippingInstructionDate,
            t.ShippingInstructionQuantity,
            t.ShippingInstructionQuantityUnit,
            t.NumberOfContainers,
            t.ContainerType,
            t.Loading,
            t.Destination,
            t.BookingAgent,
            t.Carrier,
            t.Vessel,
            t.BL_Number,
            t.ETD_Date,
            t.BL_Date,
            t.ExpectedController,
            t.Comments,
            t.FintradeBookingKey,
        ))

        rows = cursor.fetchall()
        logger.info(f"Nombre total de lignes a traiter : {len(rows)}")

        # Premiere transaction : creation des objets de reference
        with Transaction().new_transaction() as trans1:
            try:
                logger.info(
                    "Debut de la creation des objets de reference...")

                parties_to_save = []
                vessels_to_save = []
                locations_to_save = []

                parties_cache = {}
                vessels_cache = {}
                locations_cache = {}

                # Collecter les donnees des objets de reference
                for row in rows:
                    (
                        si_number, si_date, si_quantity, si_unit,
                        container_number, container_type,
                        loading_name, destination_name,
                        agent_name, carrier_name,
                        vessel_name, bl_number,
                        etd_date, bl_date, controller,
                        comments, fintrade_booking_key
                    ) = row

                    def get_or_create_party(name):
                        if not name:
                            return None
                        name_upper = str(name).strip().upper()
                        if name_upper in parties_cache:
                            return parties_cache[name_upper]

                        existing = Party.search(
                            [('name', '=', name_upper)], limit=1)
                        if existing:
                            parties_cache[name_upper] = existing[0]
                            return existing[0]

                        new_p = Party()
                        new_p.name = name_upper
                        parties_cache[name_upper] = new_p
                        parties_to_save.append(new_p)
                        return new_p

                    def get_or_create_vessel(name):
                        if not name:
                            return None
                        name_upper = str(name).strip().upper()
                        if name_upper in vessels_cache:
                            return vessels_cache[name_upper]

                        existing = Vessel.search(
                            [('vessel_name', '=', name_upper)], limit=1)
                        if existing:
                            vessels_cache[name_upper] = existing[0]
                            return existing[0]

                        new_v = Vessel()
                        new_v.vessel_name = name_upper
                        vessels_cache[name_upper] = new_v
                        vessels_to_save.append(new_v)
                        return new_v

                    def get_or_create_location(name, type_):
                        if not name:
                            return None
                        name_upper = str(name).strip().upper()
                        key = f"{name_upper}_{type_}"
                        if key in locations_cache:
                            return locations_cache[key]

                        existing = Location.search([
                            ('name', '=', name_upper),
                            ('type', '=', type_),
                        ], limit=1)
                        if existing:
                            locations_cache[key] = existing[0]
                            return existing[0]

                        new_loc = Location()
                        new_loc.name = name_upper
                        new_loc.type = type_
                        locations_cache[key] = new_loc
                        locations_to_save.append(new_loc)
                        return new_loc

                    _ = get_or_create_party(carrier_name)
                    _ = get_or_create_party(agent_name)
                    _ = get_or_create_vessel(vessel_name)
                    _ = get_or_create_location(loading_name, 'supplier')
                    _ = get_or_create_location(destination_name, 'customer')

                if parties_to_save:
                    logger.info(f"Creation de {len(parties_to_save)} parties...")
                    Party.save(parties_to_save)

                if vessels_to_save:
                    logger.info(f"Creation de {len(vessels_to_save)} vessels...")
                    Vessel.save(vessels_to_save)

                if locations_to_save:
                    logger.info(
                        f"Creation de {len(locations_to_save)} locations...")
                    Location.save(locations_to_save)

                trans1.commit()
                logger.info(
                    "Premiere transaction commitee : objets de reference crees")

            except Exception as e:
                trans1.rollback()
                logger.error(
                    f"Erreur dans la creation des objets de reference : {e}")
                logger.error(traceback.format_exc())
                raise

        # Transactions individuelles pour chaque shipment
        successful_shipments = 0
        failed_shipments = []

        cursor2 = Transaction().connection.cursor()
        cursor2.execute(*t.select(
            t.ShippingInstructionNumber,
            t.ShippingInstructionDate,
            t.ShippingInstructionQuantity,
            t.ShippingInstructionQuantityUnit,
            t.NumberOfContainers,
            t.ContainerType,
            t.Loading,
            t.Destination,
            t.BookingAgent,
            t.Carrier,
            t.Vessel,
            t.BL_Number,
            t.ETD_Date,
            t.BL_Date,
            t.ExpectedController,
            t.Comments,
            t.FintradeBookingKey,
        ))

        rows2 = cursor2.fetchall()

        def normalize_bl_number(value):
            value = str(value or '').strip()
            if value.lower() in {'none', 'null'}:
                return ''
            if value.upper() in {
                    'MULTIPLE BL NUMBERS FOUND',
                    'MULTIPLE B/L NUMBERS FOUND',
                    'NO BL NUMBER FOUND',
                    'NO B/L NUMBER FOUND',
            }:
                return ''
            return value

        def normalize_booking_number(value):
            value = str(value or '').strip()
            if value.lower() in {'none', 'null'}:
                return ''
            return value

        def sync_shipment_from_booking(shipment, bl_number, bl_date, etd_date,
                controller):
            changed = False
            columns = []
            values = []
            booking_bl_number = normalize_bl_number(bl_number)
            shipment_bl_number = normalize_bl_number(shipment.bl_number)
            if booking_bl_number and booking_bl_number != shipment_bl_number:
                logger.info(
                    "FREIGHT_BOOKING_SYNC_BL shipment=%s reference=%s "
                    "old_bl=%s new_bl=%s",
                    shipment.id, shipment.reference,
                    shipment.bl_number, booking_bl_number)
                shipment.bl_number = booking_bl_number
                columns.append('bl_number')
                values.append(booking_bl_number)
                changed = True
            if bl_date and shipment.bl_date != bl_date:
                logger.info(
                    "FREIGHT_BOOKING_SYNC_BL_DATE shipment=%s reference=%s "
                    "old_bl_date=%s new_bl_date=%s",
                    shipment.id, shipment.reference,
                    shipment.bl_date, bl_date)
                shipment.bl_date = bl_date
                columns.append('bl_date')
                values.append(bl_date)
                changed = True
            if etd_date and shipment.etd != etd_date:
                logger.info(
                    "FREIGHT_BOOKING_SYNC_ETD shipment=%s reference=%s "
                    "old_etd=%s new_etd=%s",
                    shipment.id, shipment.reference,
                    shipment.etd, etd_date)
                shipment.etd = etd_date
                columns.append('etd')
                values.append(etd_date)
                changed = True
            if controller and shipment.controller_target != controller:
                shipment.controller_target = controller
                columns.append('controller_target')
                values.append(controller)
                changed = True
            if changed:
                table = ShipmentIn.__table__()
                table_columns = [getattr(table, column) for column in columns]
                cursor = Transaction().connection.cursor()
                cursor.execute(*table.update(
                    table_columns, values, where=table.id == shipment.id))
            return changed

        for i, row in enumerate(rows2, 1):
            (
                si_number, si_date, si_quantity, si_unit,
                container_number, container_type,
                loading_name, destination_name,
                agent_name, carrier_name,
                vessel_name, bl_number,
                etd_date, bl_date, controller,
                comments, fintrade_booking_key
            ) = row
            si_reference = normalize_booking_number(si_number)

            logger.info(
                "Traitement shipment %s/%s : SI %s reference=%s",
                i, len(rows2), si_number, si_reference)

            try:
                with Transaction().new_transaction() as trans_shipment:
                    logger.info(
                        "Debut transaction pour SI %s reference=%s",
                        si_number, si_reference)

                    existing_shipments = ShipmentIn.search([
                        ('reference', '=', si_reference)
                    ])

                    if existing_shipments:
                        if len(existing_shipments) > 1:
                            logger.info(
                                "FREIGHT_BOOKING_DUPLICATE_SHIPMENT_REFERENCE "
                                "reference=%s shipment_ids=%s",
                                si_reference,
                                [shipment.id for shipment in existing_shipments])
                        for existing in existing_shipments:
                            sync_shipment_from_booking(
                                existing, bl_number, bl_date, etd_date,
                                controller)
                        shipment = existing_shipments[0]
                        if shipment.incoming_moves:
                            logger.info(
                                "Shipment %s existe deja avec lots, "
                                "booking data synchronized",
                                si_reference)
                            trans_shipment.commit()
                            continue

                        logger.info(
                            "Shipment %s existe deja sans lots, verification freight_booking_lots",
                            si_reference)
                        with Transaction().set_context(
                                _purchase_trade_skip_physical_lot_tolerance_warning=True):
                            inv_date, inv_nb = (
                                shipment._create_lots_from_fintrade())
                        shipment = ShipmentIn(shipment.id)
                        if shipment.incoming_moves:
                            shipment.controller = shipment.get_controller()
                            shipment.controller_target = controller
                            if not shipment.fees:
                                shipment.create_fee(shipment.controller)
                            shipment.instructions = shipment.get_instructions_html(
                                inv_date, inv_nb)
                            ShipmentIn.save([shipment])
                            logger.info(
                                "Shipment %s mis a jour avec %s incoming move(s)",
                                si_reference, len(shipment.incoming_moves))
                        else:
                            logger.info(
                                "Shipment %s existe sans lots et aucun lot disponible pour l'instant",
                                si_reference)
                        trans_shipment.commit()
                        continue

                    carrier = None
                    if carrier_name:
                        carrier_list = Party.search([
                            ('name', '=',
                                str(carrier_name).strip().upper())
                        ], limit=1)
                        if carrier_list:
                            carrier = carrier_list[0]
                            logger.info(
                                f"Carrier trouve pour {si_number}: {carrier.name}")
                        else:
                            logger.warning(
                                f"Carrier NON TROUVE pour {si_number}: '{carrier_name}'")

                    agent = None
                    agent_list = Party.search([
                        ('name', '=', str(agent_name or 'TBN').strip().upper())
                    ], limit=1)
                    if agent_list:
                        agent = agent_list[0]

                    vessel = None
                    if vessel_name:
                        vessel_list = Vessel.search([
                            ('vessel_name', '=',
                                str(vessel_name).strip().upper())
                        ], limit=1)
                        if vessel_list:
                            vessel = vessel_list[0]

                    loc_from = None
                    if loading_name:
                        loc_from_list = Location.search([
                            ('name', '=', str(loading_name).strip().upper()),
                            ('type', '=', 'supplier')
                        ], limit=1)
                        if loc_from_list:
                            loc_from = loc_from_list[0]

                    loc_to = None
                    if destination_name:
                        loc_to_list = Location.search([
                            ('name', '=', str(destination_name).strip().upper()),
                            ('type', '=', 'customer')
                        ], limit=1)
                        if loc_to_list:
                            loc_to = loc_to_list[0]

                    if not carrier:
                        error_msg = (
                            f"ERREUR CRITIQUE: Carrier manquant pour SI {si_number} "
                            f"(valeur: '{carrier_name}')")
                        logger.error(error_msg)
                        raise ValueError(error_msg)

                    shipment = ShipmentIn()
                    shipment.reference = si_reference
                    shipment.from_location = loc_from
                    shipment.to_location = loc_to
                    shipment.carrier = None  # carrier
                    shipment.supplier = agent
                    shipment.agent = agent
                    shipment.vessel = vessel
                    shipment.cargo_mode = 'bulk'
                    shipment.bl_number = bl_number
                    shipment.bl_date = bl_date
                    shipment.etd = etd_date
                    shipment.etad = shipment.bl_date + timedelta(days=20)

                    ShipmentIn.save([shipment])
                    with Transaction().set_context(
                            _purchase_trade_skip_physical_lot_tolerance_warning=True):
                        inv_date, inv_nb = shipment._create_lots_from_fintrade()
                    shipment = ShipmentIn(shipment.id)
                    if shipment.incoming_moves:
                        shipment.controller = shipment.get_controller()
                        shipment.controller_target = controller
                        shipment.create_fee(shipment.controller)
                        shipment.instructions = shipment.get_instructions_html(
                            inv_date, inv_nb)
                        ShipmentIn.save([shipment])
                    else:
                        logger.info(
                            "Shipment %s cree sans lots : fees/instructions "
                            "non crees, voir FINTRADE_CLEANUP_NEEDED",
                            si_number)
                    trans_shipment.commit()
                    if shipment.incoming_moves:
                        successful_shipments += 1
                        logger.info(f"Shipment {si_number} cree avec succes")

            except Exception as e:
                error_details = {
                    'si_number': si_number,
                    'carrier_name': carrier_name,
                    'error': str(e),
                    'traceback': traceback.format_exc()
                }
                failed_shipments.append(error_details)

                logger.error(f"ERREUR pour shipment {si_number}: {e}")
                logger.error(f"   Carrier: '{carrier_name}'")
                logger.error(f"   Agent: '{agent_name}'")
                logger.error(f"   Vessel: '{vessel_name}'")
                logger.error("   Traceback complet:")
                for line in traceback.format_exc().split('\n'):
                    if line.strip():
                        logger.error(f"   {line}")

        logger.info("=" * 60)
        logger.info("RESUME DE L'EXECUTION")
        logger.info("=" * 60)
        logger.info(f"Total de shipments a traiter : {len(rows2)}")
        logger.info(f"Shipments crees avec succes : {successful_shipments}")
        logger.info(f"Shipments en echec : {len(failed_shipments)}")

        if failed_shipments:
            logger.info("\nDetail des echecs :")
            for i, error in enumerate(failed_shipments, 1):
                logger.info(f"  {i}. SI {error['si_number']}:")
                logger.info(f"     Carrier: '{error['carrier_name']}'")
                logger.info(f"     Erreur: {error['error']}")

            logger.info("\nAnalyse des carriers problematiques :")
            problematic_carriers = {}
            for error in failed_shipments:
                carrier = error['carrier_name']
                if carrier in problematic_carriers:
                    problematic_carriers[carrier] += 1
                else:
                    problematic_carriers[carrier] = 1

            for carrier, count in problematic_carriers.items():
                logger.info(f"  Carrier '{carrier}' : {count} echec(s)")
                existing = Party.search([
                    ('name', '=', str(carrier).strip().upper())
                ], limit=1)
                if existing:
                    logger.info(
                        f"    -> EXISTE DANS LA BASE (ID: {existing[0].id})")
                else:
                    logger.info("    -> N'EXISTE PAS DANS LA BASE")

        logger.info("=" * 60)
