create extension if not exists postgis;

create table if not exists inspection_tasks (
    id varchar(64) primary key,
    external_task_id varchar(128),
    line_id varchar(64) not null,
    mileage_start varchar(64),
    mileage_end varchar(64),
    route_id varchar(64),
    scene_set jsonb not null,
    priority varchar(32) not null,
    status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists inspection_resources (
    id varchar(64) primary key,
    task_id varchar(64) not null references inspection_tasks(id),
    resource_type varchar(32) not null,
    storage_url text not null,
    capture_time timestamptz,
    metadata jsonb not null default '{}'::jsonb,
    geom geometry(Geometry, 4326),
    created_at timestamptz not null
);

create table if not exists analysis_jobs (
    id varchar(64) primary key,
    task_id varchar(64) not null references inspection_tasks(id),
    resource_ids jsonb not null,
    scene_set jsonb not null,
    analysis_mode varchar(32) not null,
    status varchar(32) not null,
    summary jsonb not null default '{}'::jsonb,
    created_at timestamptz not null,
    completed_at timestamptz
);

create table if not exists ai_results (
    id varchar(64) primary key,
    job_id varchar(64) not null references analysis_jobs(id),
    task_id varchar(64) not null references inspection_tasks(id),
    resource_id varchar(64) not null references inspection_resources(id),
    scene varchar(128) not null,
    category varchar(128) not null,
    confidence numeric(5,4) not null,
    geometry jsonb not null,
    geom geometry(Geometry, 4326),
    measurements jsonb not null default '{}'::jsonb,
    attributes jsonb not null default '{}'::jsonb,
    model_name varchar(128) not null,
    model_version varchar(64) not null,
    created_at timestamptz not null
);

create table if not exists alarms (
    id varchar(64) primary key,
    task_id varchar(64) not null references inspection_tasks(id),
    result_id varchar(64) not null references ai_results(id),
    scene varchar(128) not null,
    category varchar(128) not null,
    severity varchar(32) not null,
    confidence numeric(5,4) not null,
    location jsonb not null,
    evidence jsonb not null,
    rule_hits jsonb not null,
    status varchar(32) not null,
    suppressed boolean not null default false,
    suppression_reason text,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists work_orders (
    id varchar(64) primary key,
    alarm_id varchar(64) not null references alarms(id),
    status varchar(32) not null,
    assignee varchar(128),
    close_result varchar(128),
    comment text,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists baseline_objects (
    id varchar(64) primary key,
    object_type varchar(128) not null,
    scene varchar(128) not null,
    geometry jsonb not null,
    geom geometry(Geometry, 4326),
    attributes jsonb not null default '{}'::jsonb,
    status varchar(32) not null,
    first_seen_at timestamptz not null,
    last_seen_at timestamptz not null
);

create table if not exists exemption_areas (
    id varchar(64) primary key,
    scene varchar(128) not null,
    line_id varchar(64),
    geometry jsonb not null,
    geom geometry(Geometry, 4326),
    valid_from timestamptz not null,
    valid_to timestamptz not null,
    reason text not null,
    created_at timestamptz not null
);

create table if not exists model_versions (
    id varchar(64) primary key,
    model_group varchar(128) not null,
    version varchar(64) not null,
    status varchar(32) not null,
    scenes jsonb not null,
    metrics jsonb not null,
    created_at timestamptz not null default now()
);

create table if not exists platform_events (
    id varchar(64) primary key,
    event_type varchar(128) not null,
    payload jsonb not null,
    created_at timestamptz not null
);

create index if not exists idx_resources_geom on inspection_resources using gist(geom);
create index if not exists idx_results_geom on ai_results using gist(geom);
create index if not exists idx_baselines_geom on baseline_objects using gist(geom);
create index if not exists idx_exemptions_geom on exemption_areas using gist(geom);
create index if not exists idx_alarms_status on alarms(status);
create index if not exists idx_tasks_status on inspection_tasks(status);
