SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS chart_dataset (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_type VARCHAR(16) NOT NULL DEFAULT 'CM93',
    name VARCHAR(255) NOT NULL,
    source_root TEXT NOT NULL,
    source_fingerprint CHAR(64) NULL,
    parser_version VARCHAR(32) NOT NULL,
    normalized_srid INT UNSIGNED NOT NULL DEFAULT 4326,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_dataset_source_type (source_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_dictionary_object (
    dataset_id BIGINT UNSIGNED NOT NULL,
    object_class_id SMALLINT UNSIGNED NOT NULL,
    object_class VARCHAR(16) NOT NULL,
    geometry_code VARCHAR(8) NULL,
    display_code VARCHAR(8) NULL,
    description VARCHAR(255) NULL,
    raw_line TEXT NOT NULL,
    PRIMARY KEY (dataset_id, object_class_id),
    KEY idx_dict_object_class (object_class),
    CONSTRAINT fk_dict_object_dataset FOREIGN KEY (dataset_id)
      REFERENCES chart_dataset(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_dictionary_attribute (
    dataset_id BIGINT UNSIGNED NOT NULL,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    attribute_name VARCHAR(16) NOT NULL,
    source_type_name VARCHAR(32) NOT NULL,
    decoder_type CHAR(1) NOT NULL,
    min_value DOUBLE NULL,
    max_value DOUBLE NULL,
    raw_line TEXT NOT NULL,
    PRIMARY KEY (dataset_id, attribute_id),
    KEY idx_dict_attribute_name (attribute_name),
    CONSTRAINT fk_dict_attr_dataset FOREIGN KEY (dataset_id)
      REFERENCES chart_dataset(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_cell (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    dataset_id BIGINT UNSIGNED NOT NULL,
    source_relative_path VARCHAR(1024) NOT NULL,
    source_file_name VARCHAR(255) NOT NULL,
    source_size BIGINT UNSIGNED NOT NULL,
    source_sha256 CHAR(64) NULL,
    scale_code CHAR(1) NOT NULL,
    native_scale INT UNSIGNED NOT NULL,
    cell_name VARCHAR(64) NOT NULL,

    prolog_word0 INT UNSIGNED NULL,
    table1_length BIGINT UNSIGNED NULL,
    table2_length BIGINT UNSIGNED NULL,

    easting_min DOUBLE NULL,
    northing_min DOUBLE NULL,
    easting_max DOUBLE NULL,
    northing_max DOUBLE NULL,

    bbox_min_lon DOUBLE NULL,
    bbox_min_lat DOUBLE NULL,
    bbox_max_lon DOUBLE NULL,
    bbox_max_lat DOUBLE NULL,

    vector_record_count INT UNSIGNED NULL,
    vector_point_count BIGINT UNSIGNED NULL,
    point2d_count INT UNSIGNED NULL,
    point3d_count INT UNSIGNED NULL,
    feature_count INT UNSIGNED NULL,
    attribute_count BIGINT UNSIGNED NULL,

    import_status ENUM('DISCOVERED','IMPORTING','DONE','FAILED') NOT NULL DEFAULT 'DISCOVERED',
    error_message TEXT NULL,
    imported_at DATETIME NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_cell_dataset_path (dataset_id, source_relative_path(255)),
    KEY idx_cell_scale (dataset_id, scale_code),
    KEY idx_cell_status (dataset_id, import_status),
    CONSTRAINT fk_cell_dataset FOREIGN KEY (dataset_id)
      REFERENCES chart_dataset(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_coverage (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    dataset_id BIGINT UNSIGNED NOT NULL,
    cell_id BIGINT UNSIGNED NOT NULL,
    source_record_index INT UNSIGNED NOT NULL,
    object_class_id SMALLINT UNSIGNED NOT NULL,
    compilation_scale INT UNSIGNED NULL,
    horizontal_datum INT NULL,
    vertical_datum INT NULL,
    wgsox DOUBLE NULL,
    wgsoy DOUBLE NULL,
    attributes_json JSON NULL,
    geom GEOMETRY NOT NULL,
    geometry_repaired TINYINT(1) NOT NULL DEFAULT 0,
    geometry_issue VARCHAR(255) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_cov_cell_record (cell_id, source_record_index),
    KEY idx_cov_cell (cell_id),
    SPATIAL KEY spx_cov_geom (geom),
    CONSTRAINT fk_cov_dataset FOREIGN KEY (dataset_id)
      REFERENCES chart_dataset(id) ON DELETE CASCADE,
    CONSTRAINT fk_cov_cell FOREIGN KEY (cell_id)
      REFERENCES chart_cell(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_feature (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    dataset_id BIGINT UNSIGNED NOT NULL,
    cell_id BIGINT UNSIGNED NOT NULL,
    coverage_id BIGINT UNSIGNED NULL,
    source_record_index INT UNSIGNED NOT NULL,

    object_class_id SMALLINT UNSIGNED NULL,
    object_class VARCHAR(16) NOT NULL,
    geometry_type ENUM('POINT','LINE','AREA','SOUNDING','META','UNKNOWN') NOT NULL,

    scale_code CHAR(1) NOT NULL,
    native_scale INT UNSIGNED NOT NULL,

    raw_geotype SMALLINT UNSIGNED NULL,
    normalized_srid INT UNSIGNED NOT NULL DEFAULT 4326,

    bbox_min_lon DOUBLE NULL,
    bbox_min_lat DOUBLE NULL,
    bbox_max_lon DOUBLE NULL,
    bbox_max_lat DOUBLE NULL,

    attributes_json JSON NULL,
    raw_feature_blob MEDIUMBLOB NULL,
    raw_attribute_blob MEDIUMBLOB NULL,

    geometry_repaired TINYINT(1) NOT NULL DEFAULT 0,
    geometry_issue VARCHAR(255) NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_feature_cell_record (cell_id, source_record_index),
    KEY idx_feature_class (dataset_id, object_class),
    KEY idx_feature_scale (dataset_id, native_scale),
    KEY idx_feature_cell (cell_id),
    KEY idx_feature_coverage (coverage_id),
    CONSTRAINT fk_feature_dataset FOREIGN KEY (dataset_id)
      REFERENCES chart_dataset(id) ON DELETE CASCADE,
    CONSTRAINT fk_feature_cell FOREIGN KEY (cell_id)
      REFERENCES chart_cell(id) ON DELETE CASCADE,
    CONSTRAINT fk_feature_coverage FOREIGN KEY (coverage_id)
      REFERENCES chart_coverage(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_feature_attribute (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    feature_id BIGINT UNSIGNED NOT NULL,
    ordinal_no SMALLINT UNSIGNED NOT NULL,
    attribute_id SMALLINT UNSIGNED NOT NULL,
    attribute_name VARCHAR(16) NOT NULL,
    value_type ENUM('BYTE','INT','LONG','REAL','WORD10','STRING','LIST','COMPLEX','UNKNOWN') NOT NULL,
    value_int BIGINT NULL,
    value_real DOUBLE NULL,
    value_text TEXT NULL,
    value_json JSON NULL,
    raw_value_blob BLOB NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_feature_attr_ordinal (feature_id, ordinal_no),
    KEY idx_feature_attr_name (attribute_name),
    KEY idx_feature_attr_id (attribute_id),
    CONSTRAINT fk_feature_attr_feature FOREIGN KEY (feature_id)
      REFERENCES chart_feature(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_point_geometry (
    feature_id BIGINT UNSIGNED NOT NULL,
    geom POINT NOT NULL,
    PRIMARY KEY (feature_id),
    SPATIAL KEY spx_point_geom (geom),
    CONSTRAINT fk_point_feature FOREIGN KEY (feature_id)
      REFERENCES chart_feature(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_line_geometry (
    feature_id BIGINT UNSIGNED NOT NULL,
    geom GEOMETRY NOT NULL,
    PRIMARY KEY (feature_id),
    SPATIAL KEY spx_line_geom (geom),
    CONSTRAINT fk_line_feature FOREIGN KEY (feature_id)
      REFERENCES chart_feature(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_area_geometry (
    feature_id BIGINT UNSIGNED NOT NULL,
    geom GEOMETRY NOT NULL,
    PRIMARY KEY (feature_id),
    SPATIAL KEY spx_area_geom (geom),
    CONSTRAINT fk_area_feature FOREIGN KEY (feature_id)
      REFERENCES chart_feature(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_sounding_point (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    feature_id BIGINT UNSIGNED NOT NULL,
    point_index INT UNSIGNED NOT NULL,
    raw_x INT UNSIGNED NOT NULL,
    raw_y INT UNSIGNED NOT NULL,
    raw_z INT UNSIGNED NOT NULL,
    depth DOUBLE NOT NULL,
    geom POINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_sounding_feature_point (feature_id, point_index),
    KEY idx_sounding_depth (depth),
    SPATIAL KEY spx_sounding_geom (geom),
    CONSTRAINT fk_sounding_feature FOREIGN KEY (feature_id)
      REFERENCES chart_feature(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_raw_record (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    cell_id BIGINT UNSIGNED NOT NULL,
    record_kind ENUM('HEADER','VECTOR','POINT2D','POINT3D','FEATURE','ATTRIBUTE','OTHER') NOT NULL,
    source_record_index INT UNSIGNED NOT NULL DEFAULT 0,
    raw_blob MEDIUMBLOB NOT NULL,
    raw_metadata_json JSON NULL,
    PRIMARY KEY (id),
    KEY idx_raw_cell_kind (cell_id, record_kind, source_record_index),
    CONSTRAINT fk_raw_cell FOREIGN KEY (cell_id)
      REFERENCES chart_cell(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_import_job (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    dataset_id BIGINT UNSIGNED NOT NULL,
    status ENUM('RUNNING','DONE','FAILED','CANCELLED') NOT NULL DEFAULT 'RUNNING',
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    cells_discovered INT UNSIGNED NOT NULL DEFAULT 0,
    cells_done INT UNSIGNED NOT NULL DEFAULT 0,
    cells_failed INT UNSIGNED NOT NULL DEFAULT 0,
    features_source BIGINT UNSIGNED NOT NULL DEFAULT 0,
    features_imported BIGINT UNSIGNED NOT NULL DEFAULT 0,
    attributes_source BIGINT UNSIGNED NOT NULL DEFAULT 0,
    attributes_imported BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unknown_object_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unknown_attribute_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    geometry_invalid_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    geometry_repaired_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    summary_json JSON NULL,
    PRIMARY KEY (id),
    KEY idx_job_dataset (dataset_id, started_at),
    CONSTRAINT fk_job_dataset FOREIGN KEY (dataset_id)
      REFERENCES chart_dataset(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chart_import_event (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_id BIGINT UNSIGNED NULL,
    cell_id BIGINT UNSIGNED NULL,
    level ENUM('DEBUG','INFO','WARNING','ERROR') NOT NULL,
    event_code VARCHAR(64) NOT NULL,
    message TEXT NOT NULL,
    details_json JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_event_job (job_id, id),
    KEY idx_event_cell (cell_id, id),
    KEY idx_event_code (event_code),
    CONSTRAINT fk_event_job FOREIGN KEY (job_id)
      REFERENCES chart_import_job(id) ON DELETE CASCADE,
    CONSTRAINT fk_event_cell FOREIGN KEY (cell_id)
      REFERENCES chart_cell(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;