"""Business rule validation beyond JSON Schema structural compliance.

Checks naming conventions, submenu ID conventions, table structure rules,
and schema-name consistency in REFERENCES clauses.
"""

import re
import json
from jsonschema import validate, ValidationError

from config import JSON_SCHEMA_PATH
from models import MantaraSchema


def validate_schema_compliance(schema: MantaraSchema) -> list[str]:
    """Validate against the mantara.schema.v1 JSON Schema file on disk."""
    errors = []
    data = schema.to_json_dict()

    with open(JSON_SCHEMA_PATH) as f:
        json_schema = json.load(f)

    try:
        validate(instance=data, schema=json_schema)
    except ValidationError as e:
        errors.append(f"JSON Schema: {e.message} (at {e.json_path})")

    return errors


def validate_naming(schema: MantaraSchema) -> list[str]:
    """Check snake_case naming conventions."""
    errors = []
    snake = re.compile(r"^[a-z][a-z0-9_]*$")

    if not snake.match(schema.schema_name):
        errors.append(f"schema_name '{schema.schema_name}' is not snake_case")

    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                if not snake.match(table.table_name):
                    errors.append(f"Table '{table.table_name}' is not snake_case")
                for col in table.columns:
                    if not snake.match(col.name):
                        errors.append(
                            f"Column '{table.table_name}.{col.name}' is not snake_case"
                        )

    if schema.enum_types:
        for enum in schema.enum_types:
            if not enum.type_name.endswith("_enum"):
                errors.append(f"Enum '{enum.type_name}' doesn't end with '_enum'")
            for val in enum.values:
                if not snake.match(val):
                    errors.append(
                        f"Enum value '{val}' in '{enum.type_name}' is not snake_case"
                    )

    return errors


def validate_submenu_ids(schema: MantaraSchema) -> list[str]:
    """Check submenu_id follows menu_id * 100 + seq convention."""
    errors = []
    for menu in schema.menus:
        for submenu in menu.submenus:
            lo = menu.menu_id * 100 + 1
            hi = menu.menu_id * 100 + 99
            if not (lo <= submenu.submenu_id <= hi):
                errors.append(
                    f"Submenu '{submenu.submenu_name}' has ID {submenu.submenu_id}, "
                    f"expected {lo}-{hi} for menu {menu.menu_id}"
                )
    return errors


def validate_table_structure(schema: MantaraSchema) -> list[str]:
    """Check every table follows the v8 canonical column shape.

    Business tables: first column must be `id`, second must be `submenu_id`.
    cfg_* lookup tables: first column must be `<table_name>_id` (the canonical
    Mantara v8 shape — NOT plain `id`); the `submenu_id` may appear later in
    the column list rather than at position 1.
    """
    errors = []
    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                if len(table.columns) < 2:
                    errors.append(
                        f"Table '{table.table_name}' has fewer than 2 columns"
                    )
                    continue

                is_cfg = table.table_name.startswith("cfg_")
                if is_cfg:
                    # Canonical cfg PK is `<table_name>_id`
                    canonical_pk = f"{table.table_name}_id"
                    first = table.columns[0].name
                    if first != canonical_pk:
                        errors.append(
                            f"Table '{table.table_name}': first column must be "
                            f"'{canonical_pk}' (cfg_* canonical PK), got '{first}'"
                        )
                    # cfg tables must include `submenu_id` somewhere — order is
                    # not pinned to position 1, since the canonical shape puts
                    # code/label/description/is_active before it.
                    col_names = {c.name for c in table.columns}
                    if "submenu_id" not in col_names:
                        errors.append(
                            f"Table '{table.table_name}': cfg_* table must "
                            f"include a 'submenu_id' column"
                        )
                else:
                    if table.columns[0].name != "id":
                        errors.append(
                            f"Table '{table.table_name}': first column must be 'id', "
                            f"got '{table.columns[0].name}'"
                        )
                    if table.columns[1].name != "submenu_id":
                        errors.append(
                            f"Table '{table.table_name}': second column must be "
                            f"'submenu_id', got '{table.columns[1].name}'"
                        )

    return errors


def validate_ref_consistency(schema: MantaraSchema) -> list[str]:
    """Check that REFERENCES clauses use the correct schema_name prefix."""
    errors = []
    sn = schema.schema_name

    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                for col in table.columns:
                    if not col.constraints:
                        continue
                    m = re.search(
                        r"REFERENCES\s+(\w+)\.\w+\(",
                        col.constraints,
                        re.IGNORECASE,
                    )
                    if m and m.group(1) != sn:
                        errors.append(
                            f"Table '{table.table_name}'.{col.name}: REFERENCES uses "
                            f"schema '{m.group(1)}' but schema_name is '{sn}'"
                        )

                if table.foreign_keys:
                    for fk in table.foreign_keys:
                        m = re.match(r"(\w+)\.", fk.references)
                        if m and m.group(1) != sn:
                            errors.append(
                                f"Table '{table.table_name}' FK on '{fk.column}': "
                                f"references schema '{m.group(1)}' but schema_name "
                                f"is '{sn}'"
                            )

    return errors


def validate_referenced_tables(schema: MantaraSchema) -> list[str]:
    """Check that every table referenced in REFERENCES/foreign_keys actually exists."""
    errors = []
    sn = schema.schema_name

    # Collect all defined table names
    defined_tables = set()
    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                defined_tables.add(table.table_name)

    # Also include the menu and submenu tables (created by renderer)
    defined_tables.add("menu")
    defined_tables.add("submenu")

    ref_pattern = re.compile(
        r"REFERENCES\s+(?:(\w+)\.)?(\w+)\s*\(", re.IGNORECASE
    )

    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                # Check inline column constraints
                for col in table.columns:
                    if not col.constraints:
                        continue
                    m = ref_pattern.search(col.constraints)
                    if m:
                        ref_table = m.group(2)
                        if ref_table not in defined_tables:
                            errors.append(
                                f"Table '{table.table_name}'.{col.name} references "
                                f"'{ref_table}' but that table is not defined in the schema"
                            )

                # Check foreign_keys list
                if table.foreign_keys:
                    for fk in table.foreign_keys:
                        # references format: "schema.table(column)" or "table(column)"
                        fk_match = re.match(r"(?:\w+\.)?(\w+)\(", fk.references)
                        if fk_match:
                            ref_table = fk_match.group(1)
                            if ref_table not in defined_tables:
                                errors.append(
                                    f"Table '{table.table_name}' FK on '{fk.column}' "
                                    f"references '{ref_table}' but that table is not "
                                    f"defined in the schema"
                                )

    return errors


def validate_postgresql_syntax(schema: MantaraSchema) -> list[str]:
    """Check for MySQL-specific syntax that is invalid in PostgreSQL."""
    errors = []
    mysql_patterns = [
        (re.compile(r"\bON\s+UPDATE\s+CURRENT_TIMESTAMP\b", re.IGNORECASE),
         "ON UPDATE CURRENT_TIMESTAMP (MySQL syntax, not valid in PostgreSQL)"),
        (re.compile(r"\bAUTO_INCREMENT\b", re.IGNORECASE),
         "AUTO_INCREMENT (MySQL — use SERIAL or BIGSERIAL)"),
        (re.compile(r"\bTINYINT\b", re.IGNORECASE),
         "TINYINT (MySQL — use SMALLINT or INT)"),
        (re.compile(r"\bDATETIME\b", re.IGNORECASE),
         "DATETIME (MySQL — use TIMESTAMP or TIMESTAMPTZ)"),
    ]

    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                for col in table.columns:
                    if not col.constraints:
                        continue
                    for pattern, msg in mysql_patterns:
                        if pattern.search(col.constraints):
                            errors.append(
                                f"Table '{table.table_name}'.{col.name}: {msg}"
                            )

    return errors


_MONEY_QUANTITY_PATTERNS = re.compile(
    r"(price|cost|amount|total_amount|total_cost|total_price|budget|"
    r"salary|fee|charge|payment|revenue|balance|discount|tax_amount|"
    r"quantity|qty|hours_allocated|hours_worked|"
    r"credit_limit|unit_cost|unit_price|line_total|rental_cost|hourly_rate|"
    r"allocated_amount|spent_amount|invoice_amount|order_total)",
    re.IGNORECASE,
)


def validate_numeric_types(schema: MantaraSchema) -> list[str]:
    """Flag FLOAT/DOUBLE on money/quantity columns — should be NUMERIC."""
    errors = []
    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                for col in table.columns:
                    if col.type.upper() in ("FLOAT", "DOUBLE PRECISION", "REAL"):
                        if _MONEY_QUANTITY_PATTERNS.search(col.name):
                            errors.append(
                                f"Table '{table.table_name}'.{col.name}: "
                                f"uses {col.type} for a business value — "
                                f"use NUMERIC(12,2) instead (avoids floating-point rounding)"
                            )
    return errors


_CHECK_REQUIRED_PATTERNS = re.compile(
    r"^(price|cost|amount|total_amount|total_cost|total_price|budget|"
    r"salary|fee|charge|revenue|balance|discount|tax_amount|"
    r"quantity|qty|hours_allocated|hours_worked|actual_hours|"
    r"credit_limit|unit_cost|unit_price|line_total|rental_cost|hourly_rate|"
    r"daily_cost|allocated_amount|spent_amount|invoice_amount|order_total|"
    r"price_per_night|price_per_unit|total_budget|net_amount|gross_amount|"
    r"planned_quantity|used_quantity|reserved_quantity|reorder_level|"
    r"room_count|number_of_rooms|guest_count|max_occupancy|rate)$",
    re.IGNORECASE,
)


def validate_check_constraints(schema: MantaraSchema) -> list[str]:
    """Flag money/quantity columns missing CHECK constraints."""
    errors = []
    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                for col in table.columns:
                    if not _CHECK_REQUIRED_PATTERNS.match(col.name):
                        continue
                    constraints = col.constraints or ""
                    if not re.search(r"\bCHECK\s*\(", constraints, re.IGNORECASE):
                        errors.append(
                            f"Table '{table.table_name}'.{col.name}: "
                            f"missing CHECK constraint (e.g., CHECK ({col.name} >= 0))"
                        )
    return errors


def validate_submenu_granularity(schema: MantaraSchema) -> list[str]:
    """Check submenu granularity — flag too-flat or too-deep hierarchies.

    Single-submenu menus are a common LLM failure mode where the model creates
    a 1:1 menu-to-submenu mapping instead of meaningful decomposition.
    EVERY menu must have >= 2 submenus — this is an ERROR (triggers repair loop).
    """
    errors = []

    total_menus = len(schema.menus)
    if total_menus < 2:
        errors.append(
            f"Only {total_menus} menu(s) defined — most systems need at least 2 menus. "
            f"Consider splitting into domain-specific modules."
        )

    _DECOMPOSITION_SUGGESTIONS = {
        "customer": ["Customer Registration", "Customer Directory", "Customer Groups/Segments"],
        "order": ["Create Order", "Order History/Tracking", "Returns & Refunds"],
        "inventory": ["Stock Levels", "Stock Movements", "Reorder Management"],
        "billing": ["Invoice Generation", "Payment Processing", "Payment History"],
        "payment": ["Payment Processing", "Payment History", "Refunds"],
        "appointment": ["Schedule Appointment", "Appointment Calendar", "Appointment History"],
        "staff": ["Staff Directory", "Staff Schedules", "Performance/Commissions"],
        "employee": ["Employee Directory", "Employee Schedules", "Payroll/Benefits"],
        "setting": ["User Management", "System Configuration", "Roles & Permissions"],
        "product": ["Product Catalog", "Product Categories", "Pricing Management"],
        "report": ["Sales Reports", "Operational Reports", "Analytics Dashboard"],
        "user": ["User Management", "Roles & Permissions", "User Activity Log"],
        "project": ["Project Setup", "Project Tasks", "Project Resources"],
        "patient": ["Patient Registration", "Patient Records", "Patient History"],
        "doctor": ["Doctor Directory", "Doctor Schedules", "Doctor Specializations"],
        "room": ["Room Directory", "Room Assignments", "Room Availability"],
        "supplier": ["Supplier Directory", "Supplier Contracts", "Supplier Performance"],
        "warehouse": ["Warehouse Setup", "Warehouse Inventory", "Warehouse Transfers"],
    }

    for menu in schema.menus:
        sub_count = len(menu.submenus)
        if sub_count < 2:
            # Find decomposition suggestion based on menu name keywords
            suggestion = ""
            menu_lower = menu.menu_name.lower()
            for keyword, subs in _DECOMPOSITION_SUGGESTIONS.items():
                if keyword in menu_lower:
                    suggestion = (
                        f" Suggested submenus: {', '.join(repr(s) for s in subs)}."
                    )
                    break
            if not suggestion:
                suggestion = (
                    " Split into at least 2 submenus representing distinct user-facing "
                    "functions (e.g., a list/directory view and a create/manage view)."
                )

            if sub_count == 0:
                errors.append(
                    f"Menu '{menu.menu_name}' has 0 submenus — every menu must have "
                    f"at least 2 submenus.{suggestion}"
                )
            else:
                errors.append(
                    f"Menu '{menu.menu_name}' has only 1 submenu ('{menu.submenus[0].submenu_name}') "
                    f"— every menu MUST have at least 2 submenus.{suggestion}"
                )
        elif sub_count > 8:
            errors.append(
                f"Menu '{menu.menu_name}' has {sub_count} submenus — consider "
                f"splitting into multiple menus (target 2-6 per menu)."
            )

        # Check for submenus without tables
        empty_subs = [s for s in menu.submenus if not s.tables]
        if len(empty_subs) > 1:
            names = ", ".join(s.submenu_name for s in empty_subs)
            errors.append(
                f"Menu '{menu.menu_name}' has {len(empty_subs)} submenus without tables "
                f"({names}) — most submenus should have at least 1 table."
            )

    # Count total tables — very small schemas are suspicious
    total_tables = sum(
        len(table.columns) > 0
        for menu in schema.menus
        for submenu in menu.submenus
        if submenu.tables
        for table in submenu.tables
    )
    total_submenus = sum(len(menu.submenus) for menu in schema.menus)
    if total_menus >= 3 and total_tables < total_menus + 2:
        errors.append(
            f"THIN SCHEMA: Only {total_tables} tables across {total_menus} menus. "
            f"Expected at least {total_menus + 4} tables. Add junction tables, "
            f"line-item tables, audit/log tables, and master data tables."
        )

    return errors


def validate_entity_coverage(schema: MantaraSchema) -> list[str]:
    """Check for duplicate table names across submenus (entity mapping failures)."""
    errors = []
    table_locations: dict[str, list[str]] = {}

    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                loc = f"{menu.menu_name} > {submenu.submenu_name}"
                table_locations.setdefault(table.table_name, []).append(loc)

    for tname, locations in table_locations.items():
        if len(locations) > 1:
            errors.append(
                f"Table '{tname}' appears in {len(locations)} submenus: "
                f"{'; '.join(locations)} — a table should belong to exactly one submenu."
            )

    return errors


def validate_generic_names(schema: MantaraSchema) -> list[str]:
    """Flag overly generic menu/submenu names that indicate poor decomposition."""
    errors = []
    bad_names = {"general", "miscellaneous", "other", "misc", "main", "core", "default"}

    for menu in schema.menus:
        if menu.menu_name.lower().strip() in bad_names:
            errors.append(
                f"Menu '{menu.menu_name}' is too generic — use a domain-specific name."
            )
        for submenu in menu.submenus:
            if submenu.submenu_name.lower().strip() in bad_names:
                errors.append(
                    f"Submenu '{submenu.submenu_name}' is too generic — "
                    f"use a specific business function name."
                )

    return errors


_ENUM_CANDIDATE_PATTERNS = re.compile(
    r"^("
    # Exact match common names
    r"status|type|role|category|method|mode|frequency|priority|severity|"
    r"gender|condition|level|phase|stage|currency|blood_type|marital_status|"
    r"day_of_week|"
    # Suffix patterns: any column ending in these
    r"\w+_status|\w+_type|\w+_role|\w+_category|\w+_priority|"
    r"\w+_severity|\w+_level|\w+_mode|\w+_method|\w+_frequency|"
    r"\w+_condition|\w+_stage|\w+_phase|\w+_currency"
    r")$",
    re.IGNORECASE,
)


def validate_enum_usage(schema: MantaraSchema) -> list[str]:
    """v8: enum-class columns must be `<col>_id INT` with FK to a cfg_* table.

    A column is "enum-class" when its name matches the status/type/role/...
    pattern. The column passes if EITHER:
      - it ends with `_id` and is INT (the canonical FK shape), OR
      - it has an inline `REFERENCES cfg_*(...)` constraint, OR
      - the table is itself a cfg_* table (the lookup itself).
    Any other VARCHAR/TEXT column matching the pattern is flagged.
    """
    errors = []
    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                if table.table_name.startswith("cfg_"):
                    continue  # cfg tables are themselves the lookup
                for col in table.columns:
                    if not _ENUM_CANDIDATE_PATTERNS.match(col.name):
                        continue
                    if col.name.endswith("_id"):
                        continue  # FK shape — fine
                    constraints = (col.constraints or "").upper()
                    if "REFERENCES" in constraints and "CFG_" in constraints:
                        continue  # FK to a cfg_* table inline — fine
                    col_type = (col.type or "").upper()
                    if col_type.startswith("VARCHAR") or col_type == "TEXT":
                        errors.append(
                            f"Table '{table.table_name}'.{col.name}: uses "
                            f"{col.type} for an enumerable field. v8 requires "
                            f"renaming to '{col.name}_id INT' with FK to a "
                            f"'cfg_{col.name}' lookup table."
                        )
    return errors


_ENUM_COLUMN_KEYWORDS = re.compile(
    r"(status|type|role|category|priority|severity|level|mode|method|gender|payment)",
    re.IGNORECASE,
)


def validate_enum_completeness(schema: MantaraSchema) -> list[str]:
    """v8: any column whose name contains an enum keyword (status/type/role/...)
    must be either `<col>_id INT` (FK to cfg_*) or a cfg_* table column itself.
    Plain VARCHAR/TEXT for these columns is a v8 violation.

    Skips business-key columns (e.g. `role_name`, `permission_name`) which are
    a UNIQUE label of an admin-managed entity, not an enum value.
    """
    errors = []
    skip_columns = {"id", "submenu_id", "created_at", "updated_at",
                    "code", "label", "description"}
    # Business-key columns that LOOK like enum but aren't (they're the entity name)
    business_key_suffixes = ("_name", "_number", "_code", "_email", "_phone")

    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                if table.table_name.startswith("cfg_"):
                    continue
                for col in table.columns:
                    if col.name in skip_columns:
                        continue
                    if any(col.name.endswith(s) for s in business_key_suffixes):
                        continue
                    if not _ENUM_COLUMN_KEYWORDS.search(col.name):
                        continue
                    if col.name.endswith("_id"):
                        continue
                    constraints = (col.constraints or "").upper()
                    if "REFERENCES" in constraints and "CFG_" in constraints:
                        continue
                    col_type = (col.type or "").upper().strip()
                    if col_type.startswith("VARCHAR") or col_type.startswith("TEXT"):
                        errors.append(
                            f"CFG-FK NEEDED: Table '{table.table_name}'.{col.name} "
                            f"uses {col.type} for an enumerable field. v8: rename "
                            f"to '{col.name}_id INT' with REFERENCES cfg_{col.name}"
                            f"({col.name}_id)."
                        )
    return errors


def validate_minimum_schema_size(schema: MantaraSchema) -> list[str]:
    """Enforce minimum schema size: at least 3 menus, 6 submenus, 8 tables."""
    errors = []
    total_menus = len(schema.menus)
    total_submenus = sum(len(menu.submenus) for menu in schema.menus)
    total_tables = sum(
        1
        for menu in schema.menus
        for submenu in menu.submenus
        if submenu.tables
        for table in submenu.tables
    )

    if total_menus < 3:
        errors.append(
            f"MINIMUM SIZE: Schema has only {total_menus} menu(s) — minimum is 3. "
            f"Add more domain-specific menus (e.g., Settings, Reports, Administration)."
        )
    if total_submenus < 6:
        errors.append(
            f"MINIMUM SIZE: Schema has only {total_submenus} submenu(s) — minimum is 6. "
            f"Each menu should have at least 2 submenus."
        )
    if total_tables < 8:
        errors.append(
            f"MINIMUM SIZE: Schema has only {total_tables} table(s) — minimum is 8. "
            f"Add junction tables, line-item tables, audit/log tables, and configuration tables."
        )
    return errors


_BOILERPLATE_COLUMNS = {"id", "submenu_id", "created_at", "updated_at"}


def validate_table_richness(schema: MantaraSchema) -> list[str]:
    """Flag tables with fewer than 5 business columns (excluding id, submenu_id,
    created_at, updated_at) as 'thin entities' that likely need more attributes.

    Exempted (intentionally sparse by design):
        cfg_*       — lookup tables have a fixed canonical shape
        *_history   — audit trail with universal change-tracking columns only
        *_settings  — key/value configuration tables, sparse on purpose
    """
    errors = []
    for menu in schema.menus:
        for submenu in menu.submenus:
            if not submenu.tables:
                continue
            for table in submenu.tables:
                tname = table.table_name or ""
                if tname.startswith("cfg_"):
                    continue
                if tname.endswith("_history"):
                    continue
                if tname.endswith("_settings"):
                    continue
                business_cols = [
                    col for col in table.columns
                    if col.name not in _BOILERPLATE_COLUMNS
                ]
                if len(business_cols) < 5:
                    col_names = ", ".join(c.name for c in business_cols) if business_cols else "(none)"
                    errors.append(
                        f"THIN ENTITY: Table '{table.table_name}' has only "
                        f"{len(business_cols)} business column(s) ({col_names}) — "
                        f"excluding boilerplate. Add more domain-relevant attributes "
                        f"(target at least 5 business columns per table)."
                    )
    return errors


def validate_all(schema: MantaraSchema) -> dict:
    """Run all business validations.

    Returns dict with: errors (list[str]), warnings (list[str]), is_valid (bool).
    """
    errors: list[str] = []
    warnings: list[str] = []

    errors.extend(validate_naming(schema))
    errors.extend(validate_submenu_ids(schema))
    errors.extend(validate_table_structure(schema))
    errors.extend(validate_ref_consistency(schema))
    errors.extend(validate_referenced_tables(schema))
    errors.extend(validate_postgresql_syntax(schema))
    errors.extend(validate_numeric_types(schema))
    errors.extend(validate_check_constraints(schema))
    errors.extend(validate_enum_usage(schema))
    errors.extend(validate_schema_compliance(schema))

    # Granularity and decomposition checks (now errors — trigger repair loop)
    errors.extend(validate_submenu_granularity(schema))
    errors.extend(validate_entity_coverage(schema))
    errors.extend(validate_generic_names(schema))

    # New stricter validators
    errors.extend(validate_enum_completeness(schema))
    errors.extend(validate_minimum_schema_size(schema))
    errors.extend(validate_table_richness(schema))

    return {
        "errors": errors,
        "warnings": warnings,
        "is_valid": len(errors) == 0,
    }