from dataclasses import dataclass
import os

import pymysql


@dataclass(frozen=True)
class DbConfig:
    host: str = os.getenv("NAVSEA_DB_HOST", "localhost")
    port: int = int(os.getenv("NAVSEA_DB_PORT", "3306"))
    user: str = os.getenv("NAVSEA_DB_USER", "root")
    password: str = os.getenv("NAVSEA_DB_PASSWORD", "2chi9ks2")
    database: str = os.getenv("NAVSEA_DB_NAME", "pbf_analysis")
    unix_socket: str | None = os.getenv("NAVSEA_DB_SOCKET", "/tmp/mysql.sock")


class SemanticOverlayValidator:
    REQUIRED_TABLES = {
        "canonical_layer_rules": {"source_layer", "semantic_granularity", "canonical_family"},
        "canonical_object_rules": {
            "canonical_object_type",
            "canonical_family",
            "classification_basis",
            "source_layer_scope",
            "allowed_geom_types",
        },
        "object_geometry_allowlist": {
            "canonical_object_type",
            "geometry_type",
            "is_allowed",
        },
        "pbf_relayer_candidates": {
            "feature_id",
            "source_layer",
            "geom_type",
            "canonical_object_type",
            "canonical_family",
            "classification_basis",
            "source_layer_scope",
            "semantic_granularity",
            "detection_key",
            "render_layer",
            "object_type_source",
            "style_bound",
            "z",
            "x",
            "y",
        },
        "pbf_render_compatibility": {
            "source_layer",
            "render_layer",
            "feature_count",
        },
        "pbf_source_object_stats": {
            "source_layer",
            "render_layer",
            "canonical_family",
            "semantic_granularity",
            "canonical_object_type",
            "geom_type",
            "feature_count",
        },
        "pbf_detection_catalog": {
            "detection_key",
            "canonical_object_type",
            "canonical_family",
            "geom_type",
            "feature_count",
        },
        "style_layers": {"source_layer"},
        "features": {"id", "z", "x", "y", "vt_layer", "geom_type"},
        "tile_density": {"z", "x", "y", "feature_count"},
        "tile_layer_density": {"z", "x", "y", "vt_layer", "feature_count"},
    }

    def __init__(self, db_config: DbConfig) -> None:
        self.db_config = db_config

    def connect(self):
        kwargs = {
            "host": self.db_config.host,
            "port": self.db_config.port,
            "user": self.db_config.user,
            "password": self.db_config.password,
            "database": self.db_config.database,
            "charset": "utf8mb4",
            "autocommit": False,
        }
        if self.db_config.unix_socket and self.db_config.host in {"localhost", "127.0.0.1"}:
            kwargs["unix_socket"] = self.db_config.unix_socket
        return pymysql.connect(**kwargs)

    def validate_schema(self) -> None:
        with self.connect() as conn:
            with conn.cursor() as cur:
                for table, required_columns in self.REQUIRED_TABLES.items():
                    cur.execute(
                        """
                        SELECT COLUMN_NAME
                        FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s
                        """,
                        (self.db_config.database, table),
                    )
                    columns = {row[0] for row in cur.fetchall()}
                    if not columns:
                        raise RuntimeError(f"required table missing: {table}")
                    missing = required_columns - columns
                    if missing:
                        raise RuntimeError(f"table {table} missing columns: {sorted(missing)}")

    def run(self) -> None:
        self.validate_schema()
        with self.connect() as conn:
            with conn.cursor() as cur:
                self.drop_validation_tables(cur)
                self.build_classification_validation(cur)
                self.build_geometry_consistency(cur)
                self.build_style_render_equivalence(cur)
                self.build_detection_catalog_integrity(cur)
                self.build_spatial_anomalies(cur)
                self.build_summary(cur)
            conn.commit()
        print(
            "Built classification_validation, geometry_consistency, "
            "style_render_equivalence, detection_catalog_integrity, "
            "spatial_anomalies, semantic_validation_summary"
        )

    @staticmethod
    def drop_validation_tables(cur) -> None:
        for table in (
            "classification_validation",
            "geometry_consistency",
            "style_render_equivalence",
            "detection_catalog_integrity",
            "spatial_anomalies",
            "semantic_validation_summary",
        ):
            cur.execute(f"DROP TABLE IF EXISTS {table}")

    @staticmethod
    def build_classification_validation(cur) -> None:
        cur.execute(
            """
            CREATE TABLE classification_validation AS
            SELECT
                base.canonical_object_type,
                base.feature_count,
                base.distinct_source_layers,
                base.distinct_geom_types,
                CASE
                    WHEN base.canonical_object_type IS NULL OR base.canonical_object_type = '' THEN 'UNKNOWN_OBJECT'
                    WHEN rules.canonical_object_type IS NULL THEN 'UNKNOWN_OBJECT'
                    WHEN rules.classification_basis = 'source_layer_preserved' THEN 'OK'
                    WHEN base.distinct_families > 1 THEN 'RULE_CONFLICT'
                    WHEN rules.source_layer_scope = 'single_source' AND base.distinct_source_layers > 1 THEN 'AMBIGUOUS_MAPPING'
                    ELSE 'OK'
                END AS classification_status
            FROM (
                SELECT
                    canonical_object_type,
                    SUM(feature_count) AS feature_count,
                    COUNT(DISTINCT source_layer) AS distinct_source_layers,
                    COUNT(DISTINCT geom_type) AS distinct_geom_types,
                    COUNT(DISTINCT canonical_family) AS distinct_families
                FROM pbf_source_object_stats
                GROUP BY canonical_object_type
            ) AS base
            LEFT JOIN canonical_object_rules rules
                ON base.canonical_object_type = rules.canonical_object_type
            """
        )
        cur.execute("ALTER TABLE classification_validation ADD KEY idx_status (classification_status)")
        cur.execute("ALTER TABLE classification_validation ADD KEY idx_object (canonical_object_type(100))")

    @staticmethod
    def build_geometry_consistency(cur) -> None:
        cur.execute(
            """
            CREATE TABLE geometry_consistency AS
            SELECT
                g.canonical_object_type,
                g.geom_type AS geometry_type,
                g.feature_count,
                CASE
                    WHEN allowlist.is_allowed = 1 THEN 'OK'
                    WHEN g.feature_share < 0.05 THEN 'GEOMETRY_DRIFT'
                    ELSE 'UNEXPECTED_GEOMETRY'
                END AS geometry_consistency_status
            FROM (
                SELECT
                    canonical_object_type,
                    canonical_family,
                    geom_type,
                    SUM(feature_count) AS feature_count,
                    SUM(feature_count) / totals.total_features AS feature_share
                FROM pbf_detection_catalog
                JOIN (
                    SELECT
                        canonical_object_type,
                        SUM(feature_count) AS total_features
                    FROM pbf_detection_catalog
                    GROUP BY canonical_object_type
                ) AS totals
                    USING (canonical_object_type)
                GROUP BY
                    canonical_object_type,
                    canonical_family,
                    geom_type,
                    totals.total_features
            ) AS g
            LEFT JOIN object_geometry_allowlist allowlist
                ON g.canonical_object_type = allowlist.canonical_object_type
               AND g.geom_type = allowlist.geometry_type
            """
        )
        cur.execute("ALTER TABLE geometry_consistency ADD KEY idx_object (canonical_object_type(100))")
        cur.execute("ALTER TABLE geometry_consistency ADD KEY idx_status (geometry_consistency_status)")

    @staticmethod
    def build_style_render_equivalence(cur) -> None:
        cur.execute(
            """
            CREATE TABLE style_render_equivalence AS
            SELECT
                style_scope.style_layer,
                COALESCE(before_counts.feature_count_before, 0) AS feature_count_before,
                COALESCE(after_counts.feature_count_after, 0) AS feature_count_after,
                COALESCE(after_counts.feature_count_after, 0) - COALESCE(before_counts.feature_count_before, 0) AS difference,
                CASE
                    WHEN COALESCE(before_counts.feature_count_before, 0) = COALESCE(after_counts.feature_count_after, 0) THEN 'OK'
                    WHEN COALESCE(before_counts.feature_count_before, 0) > 0 AND COALESCE(after_counts.feature_count_after, 0) = 0 THEN 'RENDER_BREAK_RISK'
                    ELSE 'COUNT_MISMATCH'
                END AS render_status
            FROM (
                SELECT DISTINCT source_layer AS style_layer
                FROM style_layers
                WHERE source_layer IS NOT NULL
            ) AS style_scope
            LEFT JOIN (
                SELECT
                    vt_layer AS style_layer,
                    COUNT(*) AS feature_count_before
                FROM features
                GROUP BY vt_layer
            ) AS before_counts
                ON style_scope.style_layer = before_counts.style_layer
            LEFT JOIN (
                SELECT
                    LEFT(render_layer, 100) AS style_layer,
                    SUM(feature_count) AS feature_count_after
                FROM pbf_render_compatibility
                GROUP BY LEFT(render_layer, 100)
            ) AS after_counts
                ON style_scope.style_layer = after_counts.style_layer
            """
        )
        cur.execute("ALTER TABLE style_render_equivalence ADD KEY idx_status (render_status)")
        cur.execute("ALTER TABLE style_render_equivalence ADD KEY idx_style_layer (style_layer)")

    @staticmethod
    def build_detection_catalog_integrity(cur) -> None:
        cur.execute(
            """
            CREATE TABLE detection_catalog_integrity AS
            SELECT
                d.detection_key,
                SUM(d.feature_count) AS feature_count,
                d.geom_type AS geometry_type,
                CASE
                    WHEN d.detection_key IS NULL OR d.detection_key = '' THEN 'UNKNOWN_DETECTION_OBJECT'
                    WHEN COALESCE(object_meta.distinct_objects, 0) > 1 THEN 'UNKNOWN_DETECTION_OBJECT'
                    WHEN gc.geometry_consistency_status IS NOT NULL AND gc.geometry_consistency_status <> 'OK' THEN 'GEOMETRY_INCONSISTENT'
                    ELSE 'OK'
                END AS catalog_status
            FROM pbf_detection_catalog d
            LEFT JOIN (
                SELECT
                    detection_key,
                    COUNT(DISTINCT canonical_object_type) AS distinct_objects
                FROM pbf_detection_catalog
                GROUP BY detection_key
            ) AS object_meta
                ON d.detection_key = object_meta.detection_key
            LEFT JOIN geometry_consistency gc
                ON d.canonical_object_type = gc.canonical_object_type
               AND d.geom_type = gc.geometry_type
            GROUP BY
                d.detection_key,
                d.geom_type,
                object_meta.distinct_objects,
                gc.geometry_consistency_status
            """
        )
        cur.execute("ALTER TABLE detection_catalog_integrity ADD KEY idx_status (catalog_status)")
        cur.execute("ALTER TABLE detection_catalog_integrity ADD KEY idx_detection_key (detection_key(50))")

    @staticmethod
    def build_spatial_anomalies(cur) -> None:
        cur.execute(
            """
            CREATE TABLE spatial_anomalies AS
            SELECT *
            FROM (
                SELECT
                    'OUT_OF_BOUNDS_TILE_INDEX' AS anomaly_type,
                    COUNT(*) AS affected_feature_count,
                    CONCAT('z=', MIN(z), '..', MAX(z), '; sample=', MIN(CONCAT(z, '/', x, '/', y))) AS region_hint,
                    'HIGH' AS severity
                FROM pbf_relayer_candidates
                WHERE z < 0
                   OR x < 0
                   OR y < 0
                   OR x >= POW(2, z)
                   OR y >= POW(2, z)

                UNION ALL

                SELECT
                    'DENSITY_STATISTICS_ERROR' AS anomaly_type,
                    tile_rows.feature_count AS affected_feature_count,
                    CONCAT('z=', tile_rows.z, '; tile=', tile_rows.z, '/', tile_rows.x, '/', tile_rows.y) AS region_hint,
                    'HIGH' AS severity
                FROM tile_density AS tile_rows
                CROSS JOIN (
                    SELECT COUNT(*) AS total_features
                    FROM features
                ) AS dataset_stats
                WHERE tile_rows.feature_count > dataset_stats.total_features

                UNION ALL

                SELECT
                    CASE
                        WHEN layer_hint.vt_layer IN ('L海底地形', 'L等深線', 'L概略等深線', 'L海底線', 'p底質')
                            THEN 'TILE_OVERDENSE_BATHYMETRY'
                        WHEN layer_hint.vt_layer IN ('L陸上構造物陸', 'P陸上構造物陸', 'P橋りょう等構造物', 'p陸上構造物')
                            THEN 'TILE_OVERDENSE_LAND_STRUCTURE'
                        WHEN layer_hint.vt_layer LIKE '%ククリ'
                          OR layer_hint.vt_layer IN ('P基本線', 'L基本線', 'P危険界ククリ', 'P投錨注意障害物ククリ')
                            THEN 'TILE_OVERDENSE_OUTLINE'
                        ELSE 'EXTREME_TILE_DENSITY'
                    END AS anomaly_type,
                    tile_rows.feature_count AS affected_feature_count,
                    CONCAT(
                        'z=',
                        tile_rows.z,
                        '; tile=',
                        tile_rows.z,
                        '/',
                        tile_rows.x,
                        '/',
                        tile_rows.y,
                        '; top_layer=',
                        COALESCE(layer_hint.vt_layer, '<unknown>'),
                        '; layer_features=',
                        COALESCE(layer_hint.feature_count, 0)
                    ) AS region_hint,
                    'MEDIUM' AS severity
                FROM tile_density AS tile_rows
                JOIN (
                    SELECT
                        z,
                        AVG(feature_count) AS avg_feature_count,
                        STD(feature_count) AS std_feature_count
                    FROM tile_density
                    GROUP BY z
                ) AS zoom_stats
                    ON tile_rows.z = zoom_stats.z
                LEFT JOIN (
                    SELECT
                        winners.z,
                        winners.x,
                        winners.y,
                        MIN(winners.vt_layer) AS vt_layer,
                        winners.feature_count
                    FROM tile_layer_density AS winners
                    JOIN (
                        SELECT
                            z,
                            x,
                            y,
                            MAX(feature_count) AS feature_count
                        FROM tile_layer_density
                        GROUP BY z, x, y
                    ) AS maxima
                        ON winners.z = maxima.z
                       AND winners.x = maxima.x
                       AND winners.y = maxima.y
                       AND winners.feature_count = maxima.feature_count
                    GROUP BY
                        winners.z,
                        winners.x,
                        winners.y,
                        winners.feature_count
                ) AS layer_hint
                    ON tile_rows.z = layer_hint.z
                   AND tile_rows.x = layer_hint.x
                   AND tile_rows.y = layer_hint.y
                WHERE tile_rows.z >= 1
                  AND tile_rows.feature_count > (
                      zoom_stats.avg_feature_count + (5 * COALESCE(zoom_stats.std_feature_count, 0))
                  )
            ) AS anomaly_union
            WHERE affected_feature_count IS NOT NULL AND affected_feature_count > 0
            """
        )
        cur.execute("ALTER TABLE spatial_anomalies ADD KEY idx_severity (severity)")

    @staticmethod
    def build_summary(cur) -> None:
        cur.execute(
            """
            CREATE TABLE semantic_validation_summary AS
            SELECT
                (SELECT COUNT(*) FROM pbf_relayer_candidates) AS total_features_checked,
                (SELECT COUNT(*) FROM classification_validation WHERE classification_status <> 'OK') AS classification_errors,
                (SELECT COUNT(*) FROM geometry_consistency WHERE geometry_consistency_status <> 'OK') AS geometry_errors,
                (SELECT COUNT(*) FROM style_render_equivalence WHERE render_status <> 'OK') AS render_equivalence_errors,
                (SELECT COUNT(*) FROM detection_catalog_integrity WHERE catalog_status <> 'OK') AS detection_integrity_errors,
                (SELECT COUNT(*) FROM spatial_anomalies) AS spatial_anomalies,
                CASE
                    WHEN (SELECT COUNT(*) FROM style_render_equivalence WHERE render_status = 'RENDER_BREAK_RISK') > 0 THEN 'FAIL'
                    WHEN (SELECT COUNT(*) FROM classification_validation WHERE classification_status IN ('RULE_CONFLICT', 'UNKNOWN_OBJECT')) > 0 THEN 'FAIL'
                    WHEN (SELECT COUNT(*) FROM geometry_consistency WHERE geometry_consistency_status = 'UNEXPECTED_GEOMETRY') > 0 THEN 'FAIL'
                    WHEN (SELECT COUNT(*) FROM detection_catalog_integrity WHERE catalog_status <> 'OK') > 0 THEN 'WARNING'
                    WHEN (SELECT COUNT(*) FROM spatial_anomalies) > 0 THEN 'WARNING'
                    ELSE 'PASS'
                END AS validation_status
            """
        )


def main() -> None:
    SemanticOverlayValidator(DbConfig()).run()


if __name__ == "__main__":
    main()