CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS chart_dataset (
    id BIGSERIAL PRIMARY KEY,
    source_type VARCHAR(16) NOT NULL DEFAULT 'CM93',
    name VARCHAR(255) NOT NULL,
    source_root TEXT NOT NULL,
    source_fingerprint CHAR(64),
    parser_version VARCHAR(32) NOT NULL,
    normalized_srid INTEGER NOT NULL DEFAULT 4326,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_dataset_source_type ON chart_dataset(source_type);

CREATE TABLE IF NOT EXISTS chart_dictionary_object (
    dataset_id BIGINT NOT NULL REFERENCES chart_dataset(id) ON DELETE CASCADE,
    object_class_id SMALLINT NOT NULL,
    object_class VARCHAR(16) NOT NULL,
    geometry_code VARCHAR(8),
    display_code VARCHAR(8),
    description VARCHAR(255),
    raw_line TEXT NOT NULL,
    PRIMARY KEY (dataset_id, object_class_id)
);
CREATE INDEX IF NOT EXISTS idx_dict_object_class ON chart_dictionary_object(object_class);

CREATE TABLE IF NOT EXISTS chart_dictionary_attribute (
    dataset_id BIGINT NOT NULL REFERENCES chart_dataset(id) ON DELETE CASCADE,
    attribute_id SMALLINT NOT NULL,
    attribute_name VARCHAR(16) NOT NULL,
    source_type_name VARCHAR(32) NOT NULL,
    decoder_type CHAR(1) NOT NULL,
    min_value DOUBLE PRECISION,
    max_value DOUBLE PRECISION,
    raw_line TEXT NOT NULL,
    PRIMARY KEY (dataset_id, attribute_id)
);
CREATE INDEX IF NOT EXISTS idx_dict_attribute_name ON chart_dictionary_attribute(attribute_name);

CREATE TABLE IF NOT EXISTS chart_cell (
    id BIGSERIAL PRIMARY KEY,
    dataset_id BIGINT NOT NULL REFERENCES chart_dataset(id) ON DELETE CASCADE,
    source_relative_path VARCHAR(1024) NOT NULL,
    source_file_name VARCHAR(255) NOT NULL,
    source_size BIGINT NOT NULL,
    source_sha256 CHAR(64),
    scale_code CHAR(1) NOT NULL,
    native_scale INTEGER NOT NULL,
    cell_name VARCHAR(64) NOT NULL,
    prolog_word0 INTEGER,
    table1_length BIGINT,
    table2_length BIGINT,
    easting_min DOUBLE PRECISION,
    northing_min DOUBLE PRECISION,
    easting_max DOUBLE PRECISION,
    northing_max DOUBLE PRECISION,
    bbox_min_lon DOUBLE PRECISION,
    bbox_min_lat DOUBLE PRECISION,
    bbox_max_lon DOUBLE PRECISION,
    bbox_max_lat DOUBLE PRECISION,
    vector_record_count INTEGER,
    vector_point_count BIGINT,
    point2d_count INTEGER,
    point3d_count INTEGER,
    feature_count INTEGER,
    attribute_count BIGINT,
    import_status VARCHAR(16) NOT NULL DEFAULT 'DISCOVERED'
        CHECK (import_status IN ('DISCOVERED','IMPORTING','DONE','FAILED')),
    error_message TEXT,
    imported_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (dataset_id, source_relative_path)
);
CREATE INDEX IF NOT EXISTS idx_cell_scale ON chart_cell(dataset_id, scale_code);
CREATE INDEX IF NOT EXISTS idx_cell_status ON chart_cell(dataset_id, import_status);

CREATE TABLE IF NOT EXISTS chart_coverage (
    id BIGSERIAL PRIMARY KEY,
    dataset_id BIGINT NOT NULL REFERENCES chart_dataset(id) ON DELETE CASCADE,
    cell_id BIGINT NOT NULL REFERENCES chart_cell(id) ON DELETE CASCADE,
    source_record_index INTEGER NOT NULL,
    object_class_id SMALLINT NOT NULL,
    compilation_scale INTEGER,
    horizontal_datum INTEGER,
    vertical_datum INTEGER,
    wgsox DOUBLE PRECISION,
    wgsoy DOUBLE PRECISION,
    attributes_json JSONB,
    geom geometry(Geometry,4326) NOT NULL,
    geometry_repaired BOOLEAN NOT NULL DEFAULT FALSE,
    geometry_issue VARCHAR(255),
    UNIQUE (cell_id, source_record_index)
);
CREATE INDEX IF NOT EXISTS idx_cov_cell ON chart_coverage(cell_id);
CREATE INDEX IF NOT EXISTS spx_cov_geom ON chart_coverage USING GIST(geom);

CREATE TABLE IF NOT EXISTS chart_feature (
    id BIGSERIAL PRIMARY KEY,
    dataset_id BIGINT NOT NULL REFERENCES chart_dataset(id) ON DELETE CASCADE,
    cell_id BIGINT NOT NULL REFERENCES chart_cell(id) ON DELETE CASCADE,
    coverage_id BIGINT REFERENCES chart_coverage(id) ON DELETE SET NULL,
    source_record_index INTEGER NOT NULL,
    object_class_id SMALLINT,
    object_class VARCHAR(16) NOT NULL,
    geometry_type VARCHAR(16) NOT NULL
        CHECK (geometry_type IN ('POINT','LINE','AREA','SOUNDING','META','UNKNOWN')),
    scale_code CHAR(1) NOT NULL,
    native_scale INTEGER NOT NULL,
    raw_geotype SMALLINT,
    normalized_srid INTEGER NOT NULL DEFAULT 4326,
    bbox_min_lon DOUBLE PRECISION,
    bbox_min_lat DOUBLE PRECISION,
    bbox_max_lon DOUBLE PRECISION,
    bbox_max_lat DOUBLE PRECISION,
    attributes_json JSONB,
    raw_feature_blob BYTEA,
    raw_attribute_blob BYTEA,
    geometry_repaired BOOLEAN NOT NULL DEFAULT FALSE,
    geometry_issue VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (cell_id, source_record_index)
);
CREATE INDEX IF NOT EXISTS idx_feature_class ON chart_feature(dataset_id, object_class);
CREATE INDEX IF NOT EXISTS idx_feature_scale ON chart_feature(dataset_id, native_scale);
CREATE INDEX IF NOT EXISTS idx_feature_cell ON chart_feature(cell_id);
CREATE INDEX IF NOT EXISTS idx_feature_coverage ON chart_feature(coverage_id);

CREATE TABLE IF NOT EXISTS chart_feature_attribute (
    id BIGSERIAL PRIMARY KEY,
    feature_id BIGINT NOT NULL REFERENCES chart_feature(id) ON DELETE CASCADE,
    ordinal_no SMALLINT NOT NULL,
    attribute_id SMALLINT NOT NULL,
    attribute_name VARCHAR(16) NOT NULL,
    value_type VARCHAR(16) NOT NULL
        CHECK (value_type IN ('BYTE','INT','LONG','REAL','WORD10','STRING','LIST','COMPLEX','UNKNOWN')),
    value_int BIGINT,
    value_real DOUBLE PRECISION,
    value_text TEXT,
    value_json JSONB,
    raw_value_blob BYTEA,
    UNIQUE (feature_id, ordinal_no)
);
CREATE INDEX IF NOT EXISTS idx_feature_attr_name ON chart_feature_attribute(attribute_name);
CREATE INDEX IF NOT EXISTS idx_feature_attr_id ON chart_feature_attribute(attribute_id);

CREATE TABLE IF NOT EXISTS chart_point_geometry (
    feature_id BIGINT PRIMARY KEY REFERENCES chart_feature(id) ON DELETE CASCADE,
    geom geometry(Point,4326) NOT NULL
);
CREATE INDEX IF NOT EXISTS spx_point_geom ON chart_point_geometry USING GIST(geom);

CREATE TABLE IF NOT EXISTS chart_line_geometry (
    feature_id BIGINT PRIMARY KEY REFERENCES chart_feature(id) ON DELETE CASCADE,
    geom geometry(Geometry,4326) NOT NULL
);
CREATE INDEX IF NOT EXISTS spx_line_geom ON chart_line_geometry USING GIST(geom);

CREATE TABLE IF NOT EXISTS chart_area_geometry (
    feature_id BIGINT PRIMARY KEY REFERENCES chart_feature(id) ON DELETE CASCADE,
    geom geometry(Geometry,4326) NOT NULL
);
CREATE INDEX IF NOT EXISTS spx_area_geom ON chart_area_geometry USING GIST(geom);

CREATE TABLE IF NOT EXISTS chart_sounding_point (
    id BIGSERIAL PRIMARY KEY,
    feature_id BIGINT NOT NULL REFERENCES chart_feature(id) ON DELETE CASCADE,
    point_index INTEGER NOT NULL,
    raw_x BIGINT NOT NULL,
    raw_y BIGINT NOT NULL,
    raw_z BIGINT NOT NULL,
    depth DOUBLE PRECISION NOT NULL,
    geom geometry(Point,4326) NOT NULL,
    UNIQUE (feature_id, point_index)
);
CREATE INDEX IF NOT EXISTS idx_sounding_depth ON chart_sounding_point(depth);
CREATE INDEX IF NOT EXISTS spx_sounding_geom ON chart_sounding_point USING GIST(geom);

CREATE TABLE IF NOT EXISTS chart_raw_record (
    id BIGSERIAL PRIMARY KEY,
    cell_id BIGINT NOT NULL REFERENCES chart_cell(id) ON DELETE CASCADE,
    record_kind VARCHAR(16) NOT NULL
        CHECK (record_kind IN ('HEADER','VECTOR','POINT2D','POINT3D','FEATURE','ATTRIBUTE','OTHER')),
    source_record_index INTEGER NOT NULL DEFAULT 0,
    raw_blob BYTEA NOT NULL,
    raw_metadata_json JSONB
);
CREATE INDEX IF NOT EXISTS idx_raw_cell_kind
    ON chart_raw_record(cell_id, record_kind, source_record_index);

CREATE TABLE IF NOT EXISTS chart_import_job (
    id BIGSERIAL PRIMARY KEY,
    dataset_id BIGINT NOT NULL REFERENCES chart_dataset(id) ON DELETE CASCADE,
    status VARCHAR(16) NOT NULL DEFAULT 'RUNNING'
        CHECK (status IN ('RUNNING','DONE','FAILED','CANCELLED')),
    started_at TIMESTAMPTZ NOT NULL,
    finished_at TIMESTAMPTZ,
    cells_discovered INTEGER NOT NULL DEFAULT 0,
    cells_done INTEGER NOT NULL DEFAULT 0,
    cells_failed INTEGER NOT NULL DEFAULT 0,
    features_source BIGINT NOT NULL DEFAULT 0,
    features_imported BIGINT NOT NULL DEFAULT 0,
    attributes_source BIGINT NOT NULL DEFAULT 0,
    attributes_imported BIGINT NOT NULL DEFAULT 0,
    unknown_object_count BIGINT NOT NULL DEFAULT 0,
    unknown_attribute_count BIGINT NOT NULL DEFAULT 0,
    geometry_invalid_count BIGINT NOT NULL DEFAULT 0,
    geometry_repaired_count BIGINT NOT NULL DEFAULT 0,
    summary_json JSONB
);
CREATE INDEX IF NOT EXISTS idx_job_dataset ON chart_import_job(dataset_id, started_at);

CREATE TABLE IF NOT EXISTS chart_import_event (
    id BIGSERIAL PRIMARY KEY,
    job_id BIGINT REFERENCES chart_import_job(id) ON DELETE CASCADE,
    cell_id BIGINT REFERENCES chart_cell(id) ON DELETE CASCADE,
    level VARCHAR(16) NOT NULL CHECK (level IN ('DEBUG','INFO','WARNING','ERROR')),
    event_code VARCHAR(64) NOT NULL,
    message TEXT NOT NULL,
    details_json JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_event_job ON chart_import_event(job_id, id);
CREATE INDEX IF NOT EXISTS idx_event_cell ON chart_import_event(cell_id, id);
CREATE INDEX IF NOT EXISTS idx_event_code ON chart_import_event(event_code);