alter table uav_vendor_connections
    add column if not exists organization_uuid varchar(128),
    add column if not exists project_uuid varchar(128),
    add column if not exists integration_version varchar(64),
    add column if not exists reconciliation_status varchar(32);

alter table uav_devices
    add column if not exists gateway_vendor_device_id varchar(128),
    add column if not exists parent_device_id varchar(64),
    add column if not exists device_model_key varchar(128),
    add column if not exists firmware_version varchar(128),
    add column if not exists project_uuid varchar(128),
    add column if not exists source_updated_at timestamptz;

alter table flight_missions
    add column if not exists vendor_flight_id varchar(128),
    add column if not exists vendor_status varchar(64),
    add column if not exists sync_status varchar(32) not null default 'PENDING',
    add column if not exists last_reconciled_at timestamptz;

alter table uav_commands
    add column if not exists accepted_at timestamptz,
    add column if not exists failure_code varchar(128),
    add column if not exists last_checked_at timestamptz;

alter table inspection_resources
    add column if not exists vendor_source varchar(32),
    add column if not exists vendor_media_id varchar(128),
    add column if not exists vendor_flight_id varchar(128),
    add column if not exists source_checksum varchar(64),
    add column if not exists mirrored_at timestamptz;

create table if not exists uav_access_event_inbox (
    event_id varchar(128) primary key,
    event_type varchar(160) not null,
    aggregate_id varchar(128),
    occurred_at timestamptz,
    received_at timestamptz not null,
    payload jsonb not null,
    processed_at timestamptz,
    processing_error text
);

create index if not exists idx_uav_access_event_pending
    on uav_access_event_inbox(received_at)
    where processed_at is null;

update uav_vendor_connections
set integration_version='SIMULATOR_V1',
    reconciliation_status='NOT_REQUIRED'
where vendor_code='SIMULATOR' and integration_version is null;
