create table if not exists access_vendor_connections (
    id varchar(64) primary key,
    vendor_code varchar(32) not null,
    base_url text not null,
    organization_uuid varchar(128),
    project_uuid varchar(128),
    secret_ref varchar(256),
    capability_snapshot jsonb not null default '[]'::jsonb,
    status varchar(32) not null,
    health_detail jsonb not null default '{}'::jsonb,
    last_checked_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists access_device_bindings (
    connection_id varchar(64) not null,
    vendor_device_id varchar(128) not null,
    platform_device_id varchar(64),
    parent_vendor_device_id varchar(128),
    project_uuid varchar(128) not null,
    device_model_key varchar(128),
    device_class varchar(64),
    capability_snapshot jsonb not null default '[]'::jsonb,
    state_snapshot jsonb not null default '{}'::jsonb,
    online boolean not null default false,
    last_seen_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null,
    primary key(connection_id, vendor_device_id)
);

create table if not exists route_vendor_artifacts (
    id varchar(64) primary key,
    connection_id varchar(64) not null,
    project_uuid varchar(128) not null,
    platform_route_version_id varchar(64) not null,
    vendor_wayline_id varchar(128),
    vendor_request_id varchar(128),
    object_key text,
    checksum varchar(64) not null,
    import_state varchar(32) not null,
    import_code varchar(128),
    import_detail jsonb not null default '{}'::jsonb,
    created_at timestamptz not null,
    updated_at timestamptz not null,
    unique(connection_id, project_uuid, platform_route_version_id, checksum)
);

create table if not exists mission_vendor_bindings (
    id varchar(64) primary key,
    connection_id varchar(64) not null,
    project_uuid varchar(128) not null,
    platform_mission_id varchar(64) not null unique,
    platform_task_id varchar(64),
    vendor_device_id varchar(128) not null,
    vendor_task_uuid varchar(128),
    vendor_flight_id varchar(128),
    vendor_status varchar(64),
    normalized_status varchar(32) not null,
    request_snapshot jsonb not null default '{}'::jsonb,
    response_snapshot jsonb not null default '{}'::jsonb,
    last_reconciled_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create index if not exists idx_mission_vendor_task on mission_vendor_bindings(connection_id, vendor_task_uuid);

create table if not exists access_commands (
    id varchar(64) primary key,
    connection_id varchar(64) not null,
    platform_command_id varchar(64),
    platform_mission_id varchar(64),
    idempotency_key varchar(160) not null unique,
    command_type varchar(64) not null,
    endpoint text,
    request_id varchar(128) not null,
    vendor_task_id varchar(128),
    request_payload jsonb not null default '{}'::jsonb,
    response_payload jsonb not null default '{}'::jsonb,
    state varchar(32) not null,
    vendor_code varchar(128),
    vendor_message text,
    requested_at timestamptz not null,
    accepted_at timestamptz,
    completed_at timestamptz,
    updated_at timestamptz not null
);

create table if not exists control_leases (
    id varchar(64) primary key,
    connection_id varchar(64) not null,
    project_uuid varchar(128) not null,
    vendor_device_id varchar(128) not null,
    control_type varchar(32) not null,
    holder_id varchar(128) not null,
    state varchar(32) not null,
    acquired_at timestamptz,
    expires_at timestamptz,
    released_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create unique index if not exists idx_active_control_lease
    on control_leases(connection_id, project_uuid, vendor_device_id, control_type)
    where state in ('REQUESTED', 'ACTIVE');

create table if not exists access_event_inbox (
    id varchar(64) primary key,
    connection_id varchar(64) not null,
    source varchar(32) not null,
    vendor_event_id varchar(160) not null,
    event_type varchar(128) not null,
    payload_hash varchar(64) not null,
    raw_payload jsonb not null,
    event_time timestamptz,
    received_at timestamptz not null,
    processed_at timestamptz,
    processing_error text,
    unique(connection_id, source, vendor_event_id)
);

create table if not exists access_outbox_events (
    id varchar(64) primary key,
    event_type varchar(160) not null,
    aggregate_id varchar(128) not null,
    event_key varchar(160) not null,
    payload jsonb not null,
    state varchar(32) not null,
    attempt_count integer not null default 0,
    occurred_at timestamptz not null,
    published_at timestamptz,
    last_error text,
    unique(event_key)
);

create index if not exists idx_access_outbox_pending
    on access_outbox_events(state, occurred_at)
    where state in ('PENDING', 'FAILED');

create table if not exists media_vendor_bindings (
    id varchar(64) primary key,
    connection_id varchar(64) not null,
    project_uuid varchar(128) not null,
    vendor_media_id varchar(128) not null,
    vendor_flight_id varchar(128),
    platform_resource_id varchar(64),
    object_key text,
    checksum varchar(64),
    mirror_state varchar(32) not null,
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null,
    updated_at timestamptz not null,
    unique(connection_id, vendor_media_id)
);

create table if not exists reconciliation_checkpoints (
    connection_id varchar(64) not null,
    project_uuid varchar(128) not null,
    resource_type varchar(64) not null,
    cursor_value text,
    last_success_at timestamptz,
    last_error text,
    updated_at timestamptz not null,
    primary key(connection_id, project_uuid, resource_type)
);
