from __future__ import annotations

import logging
from datetime import date
from decimal import Decimal

from .comparison import round_decimal
from .seed_data_aggregates_sand import CATALOG_SAND
from .seed_data_aggregates_stone import CATALOG_STONE
from .seed_data_equipment_fuel import CATALOG_EQUIPMENT_FUEL
from .seed_data_infra_brick import CATALOG_BRICK_INFRA
from .seed_data_labor_artisans import CATALOG_LABOR_ARTISANS
from .seed_data_labor_wages import CATALOG_LABOR_WAGES
from .seed_data_precast import CATALOG_PRECAST
from .seed_data_steel import CATALOG_STEEL

logger = logging.getLogger("dscons.state_price_sync.seeder")


class StatePriceSeederMixin:
    """Mixin for seeding official SXD Hải Phòng state price database."""

    def seed_official_state_prices(self) -> int:
        official_catalog = (
            CATALOG_SAND
            + CATALOG_STONE
            + CATALOG_STEEL
            + CATALOG_PRECAST
            + CATALOG_BRICK_INFRA
            + CATALOG_EQUIPMENT_FUEL
            + CATALOG_LABOR_WAGES
            + CATALOG_LABOR_ARTISANS
        )

        # Region I (Urban Hai Phong City Center) items with +3.5% transport/urban premium
        region_1_catalog = []
        for item in official_catalog:
            r1_item = dict(item)
            r1_item["region_code"] = "HAI_PHONG_KHU_VUC_1"
            if item["material_group"] not in ["FUEL_OIL", "LABOR"]:
                r1_item["state_unit_price_vnd"] = round_decimal(
                    item["state_unit_price_vnd"] * Decimal("1.035"), 2
                )
            region_1_catalog.append(r1_item)

        official_catalog.extend(region_1_catalog)

        # Historical periods (2026-07, 2026-06, 2026-05, 2025-12)
        periods_config = [
            (
                "2026-07",
                date(2026, 7, 1),
                "Thông báo giá số 15/TB-LS TC-XD Hải Phòng tháng 07/2026",
                Decimal("0.985"),
            ),
            (
                "2026-06",
                date(2026, 6, 1),
                "Thông báo giá số 12/TB-LS TC-XD Hải Phòng tháng 06/2026",
                Decimal("0.970"),
            ),
            (
                "2026-05",
                date(2026, 5, 1),
                "Thông báo giá số 10/TB-LS TC-XD Hải Phòng tháng 05/2026",
                Decimal("0.965"),
            ),
            (
                "2025-12",
                date(2025, 12, 1),
                "Thông báo giá số 24/TB-LS TC-XD Hải Phòng tháng 12/2025",
                Decimal("0.940"),
            ),
        ]

        for p_code, p_date, p_doc, p_factor in periods_config:
            for item in list(official_catalog[:55]):
                new_item = dict(item)
                new_item["publish_period"] = p_code
                new_item["effective_date"] = p_date
                new_item["document_reference"] = p_doc
                if item["material_group"] == "FUEL_OIL":
                    if p_code == "2026-07":
                        new_item["state_unit_price_vnd"] = Decimal("20500.00")
                    elif p_code == "2026-06":
                        new_item["state_unit_price_vnd"] = Decimal("20100.00")
                    elif p_code == "2026-05":
                        new_item["state_unit_price_vnd"] = Decimal("19800.00")
                    else:
                        new_item["state_unit_price_vnd"] = Decimal("19200.00")
                else:
                    new_item["state_unit_price_vnd"] = round_decimal(
                        item["state_unit_price_vnd"] * p_factor, 2
                    )
                official_catalog.append(new_item)

        inserted_count = 0
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                for row in official_catalog:
                    cur.execute(
                        """
                        INSERT INTO erp_state_published_prices (
                            id, publish_period, region_code, material_group, material_code,
                            material_name, specifications, unit, state_unit_price_vnd,
                            document_reference, effective_date, notes, created_at, updated_at
                        ) VALUES (
                            gen_random_uuid(), %s, %s, %s, %s,
                            %s, %s, %s, %s,
                            %s, %s, %s, NOW(), NOW()
                        )
                        ON CONFLICT (publish_period, region_code, material_code)
                        DO UPDATE SET
                            material_name = EXCLUDED.material_name,
                            specifications = EXCLUDED.specifications,
                            unit = EXCLUDED.unit,
                            state_unit_price_vnd = EXCLUDED.state_unit_price_vnd,
                            document_reference = EXCLUDED.document_reference,
                            effective_date = EXCLUDED.effective_date,
                            notes = EXCLUDED.notes,
                            updated_at = NOW();
                    """,
                        (
                            row["publish_period"],
                            row["region_code"],
                            row["material_group"],
                            row["material_code"],
                            row["material_name"],
                            row.get("specifications"),
                            row["unit"],
                            row["state_unit_price_vnd"],
                            row["document_reference"],
                            row["effective_date"],
                            row.get("notes"),
                        ),
                    )
                    inserted_count += 1
                conn.commit()
        logger.info(
            "Successfully seeded/synced %d State published price records.",
            inserted_count,
        )
        return inserted_count