alter table inspection_tasks
    add column if not exists plan_id varchar(64),
    add column if not exists task_type varchar(32) not null default 'SPECIAL',
    add column if not exists template_id varchar(64),
    add column if not exists approval_status varchar(32) not null default 'DRAFT',
    add column if not exists object_scope jsonb not null default '[]'::jsonb,
    add column if not exists planned_start_at timestamptz,
    add column if not exists parent_task_id varchar(64),
    add column if not exists created_by varchar(64),
    add column if not exists version_no bigint not null default 0;

alter table inspection_resources
    add column if not exists upload_session_id varchar(64),
    add column if not exists vendor varchar(32),
    add column if not exists device_id varchar(64),
    add column if not exists payload_id varchar(64),
    add column if not exists mission_id varchar(64),
    add column if not exists waypoint_index integer,
    add column if not exists sha256 varchar(64),
    add column if not exists size_bytes bigint,
    add column if not exists mime_type varchar(128),
    add column if not exists quality_status varchar(32) not null default 'PENDING',
    add column if not exists source_crs varchar(64),
    add column if not exists canonical_crs varchar(64) not null default 'EPSG:4326';

alter table alarms
    add column if not exists policy_id varchar(64),
    add column if not exists dedup_key varchar(256),
    add column if not exists master_alarm_id varchar(64),
    add column if not exists acknowledged_at timestamptz,
    add column if not exists ack_due_at timestamptz,
    add column if not exists handle_due_at timestamptz,
    add column if not exists owner_org_id varchar(64);

alter table work_orders
    add column if not exists owner_org_id varchar(64),
    add column if not exists assignee_user_id varchar(64),
    add column if not exists sla_policy_id varchar(64),
    add column if not exists accepted_at timestamptz,
    add column if not exists arrived_at timestamptz,
    add column if not exists submitted_at timestamptz,
    add column if not exists reviewed_at timestamptz,
    add column if not exists archived_at timestamptz,
    add column if not exists version_no bigint not null default 0;

create table if not exists organizations (
    id varchar(64) primary key,
    parent_id varchar(64) references organizations(id),
    org_type varchar(32) not null,
    name varchar(128) not null,
    code varchar(64) not null unique,
    status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists platform_users (
    id varchar(64) primary key,
    org_id varchar(64) references organizations(id),
    account varchar(128) not null unique,
    display_name varchar(128) not null,
    status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists roles (
    id varchar(64) primary key,
    code varchar(64) not null unique,
    name varchar(128) not null,
    data_scope varchar(32) not null default 'ORG',
    permissions jsonb not null default '[]'::jsonb,
    created_at timestamptz not null
);

create table if not exists user_roles (
    user_id varchar(64) not null references platform_users(id),
    role_id varchar(64) not null references roles(id),
    primary key (user_id, role_id)
);

create table if not exists inspection_plans (
    id varchar(64) primary key,
    name varchar(128) not null,
    plan_type varchar(32) not null,
    schedule_rule varchar(128),
    template_id varchar(64),
    object_ids jsonb not null default '[]'::jsonb,
    scene_set jsonb not null default '[]'::jsonb,
    priority varchar(32) not null default 'normal',
    status varchar(32) not null,
    next_run_at timestamptz,
    owner_org_id varchar(64) references organizations(id),
    created_by varchar(64),
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists inspection_objects (
    id varchar(64) primary key,
    object_type varchar(32) not null,
    name varchar(128) not null,
    line_id varchar(64) not null,
    mileage_start varchar(64),
    mileage_end varchar(64),
    geom geometry(Geometry, 4326) not null,
    attributes jsonb not null default '{}'::jsonb,
    owner_org_id varchar(64) references organizations(id),
    risk_level varchar(32) not null default 'NORMAL',
    inspection_cycle_days integer,
    source_crs varchar(64) not null default 'EPSG:4326',
    transform_version varchar(64),
    accuracy_m numeric(10,3),
    status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create index if not exists idx_inspection_objects_geom on inspection_objects using gist(geom);
create index if not exists idx_inspection_objects_line on inspection_objects(line_id, object_type, status);

create table if not exists inspection_task_objects (
    task_id varchar(64) not null references inspection_tasks(id),
    object_id varchar(64) not null references inspection_objects(id),
    required_actions jsonb not null default '[]'::jsonb,
    primary key (task_id, object_id)
);

create table if not exists route_templates (
    id varchar(64) primary key,
    name varchar(128) not null,
    object_type varchar(32) not null,
    payload_rules jsonb not null default '{}'::jsonb,
    flight_rules jsonb not null default '{}'::jsonb,
    status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists routes (
    id varchar(64) primary key,
    name varchar(128) not null,
    object_id varchar(64) references inspection_objects(id),
    template_id varchar(64) references route_templates(id),
    status varchar(32) not null,
    current_version_id varchar(64),
    created_by varchar(64),
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists route_versions (
    id varchar(64) primary key,
    route_id varchar(64) not null references routes(id),
    version_no integer not null,
    status varchar(32) not null,
    geom geometry(LineStringZ, 4326),
    waypoints jsonb not null default '[]'::jsonb,
    flight_parameters jsonb not null default '{}'::jsonb,
    payload_actions jsonb not null default '[]'::jsonb,
    validation_result jsonb not null default '{}'::jsonb,
    checksum varchar(64),
    created_by varchar(64),
    approved_by varchar(64),
    created_at timestamptz not null,
    published_at timestamptz,
    unique(route_id, version_no)
);

create index if not exists idx_route_versions_geom on route_versions using gist(geom);

create table if not exists route_waypoints (
    id varchar(64) primary key,
    route_version_id varchar(64) not null references route_versions(id) on delete cascade,
    sequence_no integer not null,
    geom geometry(PointZ, 4326) not null,
    altitude_m numeric(10,2) not null,
    speed_mps numeric(10,2) not null,
    heading_deg numeric(10,2),
    gimbal_pitch_deg numeric(10,2),
    actions jsonb not null default '[]'::jsonb,
    unique(route_version_id, sequence_no)
);

create table if not exists uav_vendor_connections (
    id varchar(64) primary key,
    name varchar(128) not null,
    vendor_code varchar(32) not null,
    connection_mode varchar(32) not null,
    environment varchar(32) not null,
    endpoint_url text,
    workspace_id varchar(128),
    secret_ref varchar(256),
    capabilities jsonb not null default '[]'::jsonb,
    enabled boolean not null default true,
    status varchar(32) not null,
    health_detail jsonb not null default '{}'::jsonb,
    last_checked_at timestamptz,
    last_event_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create index if not exists idx_uav_connections_vendor on uav_vendor_connections(vendor_code, environment);

create table if not exists uav_devices (
    id varchar(64) primary key,
    connection_id varchar(64) not null references uav_vendor_connections(id),
    vendor_device_id varchar(128) not null,
    vendor_code varchar(32) not null,
    device_type varchar(32) not null,
    model varchar(128),
    serial_number varchar(128),
    capabilities jsonb not null default '[]'::jsonb,
    status varchar(32) not null,
    health_detail jsonb not null default '{}'::jsonb,
    geom geometry(Point, 4326),
    last_seen_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null,
    unique(connection_id, vendor_device_id)
);

create index if not exists idx_uav_devices_geom on uav_devices using gist(geom);

create table if not exists uav_docks (
    id varchar(64) primary key,
    connection_id varchar(64) not null references uav_vendor_connections(id),
    vendor_dock_id varchar(128) not null,
    model varchar(128),
    capabilities jsonb not null default '[]'::jsonb,
    status varchar(32) not null,
    geom geometry(Point, 4326),
    last_seen_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null,
    unique(connection_id, vendor_dock_id)
);

create table if not exists uav_payloads (
    id varchar(64) primary key,
    device_id varchar(64) not null references uav_devices(id),
    payload_type varchar(32) not null,
    model varchar(128),
    capabilities jsonb not null default '[]'::jsonb,
    calibration jsonb not null default '{}'::jsonb,
    status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists flight_missions (
    id varchar(64) primary key,
    task_id varchar(64) not null references inspection_tasks(id),
    route_version_id varchar(64) references route_versions(id),
    connection_id varchar(64) not null references uav_vendor_connections(id),
    device_id varchar(64) not null references uav_devices(id),
    dock_id varchar(64) references uav_docks(id),
    vendor_code varchar(32) not null,
    vendor_mission_id varchar(128),
    status varchar(32) not null,
    progress integer not null default 0,
    current_waypoint integer,
    validation_snapshot jsonb not null default '{}'::jsonb,
    error_code varchar(128),
    error_message text,
    planned_start_at timestamptz,
    started_at timestamptz,
    completed_at timestamptz,
    version_no bigint not null default 0,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create index if not exists idx_flight_missions_task on flight_missions(task_id, created_at);
create index if not exists idx_flight_missions_status on flight_missions(status, updated_at);

create table if not exists uav_commands (
    id varchar(64) primary key,
    mission_id varchar(64) not null references flight_missions(id),
    command_type varchar(64) not null,
    idempotency_key varchar(128) not null unique,
    requested_by varchar(128) not null,
    reason text,
    request_payload jsonb not null default '{}'::jsonb,
    vendor_transaction_id varchar(128),
    response_payload jsonb not null default '{}'::jsonb,
    status varchar(32) not null,
    attempt_count integer not null default 0,
    requested_at timestamptz not null,
    completed_at timestamptz
);

create table if not exists vendor_event_inbox (
    id varchar(64) primary key,
    connection_id varchar(64) not null references uav_vendor_connections(id),
    vendor_code varchar(32) not null,
    vendor_event_id varchar(128) not null,
    event_type varchar(128) not null,
    event_time timestamptz,
    received_at timestamptz not null,
    payload jsonb not null,
    processed_at timestamptz,
    processing_error text,
    unique(connection_id, vendor_event_id)
);

create table if not exists telemetry_points (
    id bigserial primary key,
    mission_id varchar(64) references flight_missions(id),
    device_id varchar(64) not null references uav_devices(id),
    event_time timestamptz not null,
    received_at timestamptz not null,
    geom geometry(PointZ, 4326),
    metrics jsonb not null default '{}'::jsonb,
    quality varchar(32) not null default 'GOOD',
    sequence_no bigint
);

create index if not exists idx_telemetry_mission_time on telemetry_points(mission_id, event_time desc);
create index if not exists idx_telemetry_geom on telemetry_points using gist(geom);

create table if not exists flight_tracks (
    mission_id varchar(64) primary key references flight_missions(id),
    geom geometry(LineStringZ, 4326),
    point_count integer not null default 0,
    quality jsonb not null default '{}'::jsonb,
    start_at timestamptz,
    end_at timestamptz,
    updated_at timestamptz not null
);

create index if not exists idx_flight_tracks_geom on flight_tracks using gist(geom);

create table if not exists upload_sessions (
    id varchar(64) primary key,
    task_id varchar(64) references inspection_tasks(id),
    mission_id varchar(64) references flight_missions(id),
    resource_type varchar(32) not null,
    file_name varchar(512) not null,
    content_type varchar(128),
    total_size_bytes bigint not null,
    part_size_bytes bigint not null,
    total_parts integer not null,
    expected_sha256 varchar(64),
    actual_sha256 varchar(64),
    object_key text,
    status varchar(32) not null,
    expires_at timestamptz not null,
    created_by varchar(128),
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists upload_parts (
    session_id varchar(64) not null references upload_sessions(id) on delete cascade,
    part_no integer not null,
    size_bytes bigint not null,
    sha256 varchar(64),
    etag varchar(128),
    status varchar(32) not null,
    uploaded_at timestamptz not null,
    primary key(session_id, part_no)
);

create table if not exists preprocess_jobs (
    id varchar(64) primary key,
    resource_id varchar(64) not null references inspection_resources(id),
    pipeline_version varchar(64) not null,
    status varchar(32) not null,
    quality_status varchar(32) not null default 'PENDING',
    quality_metrics jsonb not null default '{}'::jsonb,
    error_message text,
    created_at timestamptz not null,
    started_at timestamptz,
    completed_at timestamptz
);

create table if not exists preprocess_step_runs (
    id varchar(64) primary key,
    job_id varchar(64) not null references preprocess_jobs(id) on delete cascade,
    sequence_no integer not null,
    step_code varchar(64) not null,
    status varchar(32) not null,
    input_ref text,
    output_ref text,
    metrics jsonb not null default '{}'::jsonb,
    error_message text,
    started_at timestamptz,
    completed_at timestamptz,
    unique(job_id, sequence_no)
);

create table if not exists alarm_policies (
    id varchar(64) primary key,
    name varchar(128) not null,
    scene varchar(128),
    severity_rules jsonb not null,
    notification_rules jsonb not null default '{}'::jsonb,
    sla_rules jsonb not null default '{}'::jsonb,
    status varchar(32) not null,
    version_no integer not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists notification_deliveries (
    id varchar(64) primary key,
    alarm_id varchar(64) references alarms(id),
    channel varchar(32) not null,
    recipient varchar(256) not null,
    status varchar(32) not null,
    attempt_count integer not null default 0,
    receipt jsonb not null default '{}'::jsonb,
    sent_at timestamptz,
    delivered_at timestamptz,
    read_at timestamptz,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists responsibility_rules (
    id varchar(64) primary key,
    line_id varchar(64),
    object_type varchar(32),
    scene varchar(128),
    owner_org_id varchar(64) not null references organizations(id),
    assignee_user_id varchar(64) references platform_users(id),
    priority integer not null default 100,
    geom geometry(Geometry, 4326),
    status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create index if not exists idx_responsibility_rules_geom on responsibility_rules using gist(geom);

create table if not exists duty_rosters (
    id varchar(64) primary key,
    org_id varchar(64) not null references organizations(id),
    user_id varchar(64) not null references platform_users(id),
    shift_start timestamptz not null,
    shift_end timestamptz not null,
    status varchar(32) not null,
    created_at timestamptz not null
);

create table if not exists workorder_actions (
    id varchar(64) primary key,
    workorder_id varchar(64) not null references work_orders(id),
    action_type varchar(64) not null,
    from_status varchar(32),
    to_status varchar(32),
    operator_id varchar(64),
    operator_name varchar(128) not null,
    result varchar(256),
    comment text,
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null
);

create index if not exists idx_workorder_actions_order on workorder_actions(workorder_id, created_at);

create table if not exists workorder_attachments (
    id varchar(64) primary key,
    workorder_id varchar(64) not null references work_orders(id),
    phase varchar(32) not null,
    resource_id varchar(64) references inspection_resources(id),
    file_name varchar(512) not null,
    object_key text not null,
    storage_url text not null,
    mime_type varchar(128),
    size_bytes bigint,
    sha256 varchar(64),
    capture_time timestamptz,
    geom geometry(Point, 4326),
    uploaded_by varchar(128) not null,
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null
);

create table if not exists review_records (
    id varchar(64) primary key,
    workorder_id varchar(64) not null references work_orders(id),
    review_type varchar(32) not null,
    decision varchar(32) not null,
    criteria jsonb not null default '[]'::jsonb,
    evidence jsonb not null default '[]'::jsonb,
    reviewer_id varchar(64),
    reviewer_name varchar(128) not null,
    comment text,
    created_at timestamptz not null
);

create table if not exists reinspection_requests (
    id varchar(64) primary key,
    workorder_id varchar(64) not null references work_orders(id),
    source_task_id varchar(64) not null references inspection_tasks(id),
    reinspection_task_id varchar(64) references inspection_tasks(id),
    route_id varchar(64),
    target_geom geometry(Point, 4326),
    required_methods jsonb not null default '[]'::jsonb,
    status varchar(32) not null,
    requested_by varchar(128) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create table if not exists archive_packages (
    id varchar(64) primary key,
    business_type varchar(32) not null,
    business_id varchar(64) not null,
    archive_no varchar(128) not null unique,
    manifest jsonb not null,
    manifest_url text,
    checksum varchar(64),
    retention_until date,
    status varchar(32) not null,
    created_by varchar(128) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null,
    unique(business_type, business_id)
);

create table if not exists report_jobs (
    id varchar(64) primary key,
    report_type varchar(64) not null,
    scope jsonb not null,
    template_version varchar(64) not null,
    status varchar(32) not null,
    output_url text,
    checksum varchar(64),
    requested_by varchar(128) not null,
    error_message text,
    created_at timestamptz not null,
    completed_at timestamptz
);

create table if not exists capability_completion_status (
    capability_code varchar(64) primary key,
    capability_name varchar(128) not null,
    stage varchar(32) not null,
    status varchar(32) not null,
    implementation_ref text,
    blocker_type varchar(32),
    blocker_reason text,
    updated_at timestamptz not null
);

insert into organizations(id, parent_id, org_type, name, code, status, created_at, updated_at) values
('org-railway', null, 'UNIT', '铁路巡检中心', 'RAILWAY', 'ACTIVE', now(), now()),
('org-works', 'org-railway', 'DEPARTMENT', '工务巡检部门', 'WORKS', 'ACTIVE', now(), now()),
('org-power', 'org-railway', 'DEPARTMENT', '供电巡检部门', 'POWER', 'ACTIVE', now(), now()),
('org-flood', 'org-railway', 'DEPARTMENT', '防洪巡检部门', 'FLOOD', 'ACTIVE', now(), now())
on conflict (id) do nothing;

insert into platform_users(id, org_id, account, display_name, status, created_at, updated_at) values
('user-dispatcher', 'org-railway', 'dispatcher', '综合调度员', 'ACTIVE', now(), now()),
('user-reviewer', 'org-works', 'reviewer', '专业复核人员', 'ACTIVE', now(), now()),
('user-field', 'org-works', 'field-worker', '现场处置人员', 'ACTIVE', now(), now())
on conflict (id) do nothing;

insert into roles(id, code, name, data_scope, permissions, created_at) values
('role-dispatcher', 'TASK_DISPATCHER', '任务调度员', 'ALL', '["inspection:plan","inspection:task","uav:dispatch","uav:command"]'::jsonb, now()),
('role-reviewer', 'WORKORDER_REVIEWER', '工单复核员', 'ORG', '["alarm:review","workorder:review","archive:read"]'::jsonb, now()),
('role-field', 'FIELD_WORKER', '现场处置人员', 'ASSIGNED', '["workorder:accept","workorder:process","evidence:upload"]'::jsonb, now())
on conflict (id) do nothing;

insert into user_roles(user_id, role_id) values
('user-dispatcher', 'role-dispatcher'),
('user-reviewer', 'role-reviewer'),
('user-field', 'role-field')
on conflict do nothing;

insert into inspection_objects(id, object_type, name, line_id, mileage_start, mileage_end, geom, attributes, owner_org_id, risk_level, inspection_cycle_days, status, created_at, updated_at) values
('object-line-demo', 'RAILWAY', '试点线路 K123-K130', 'line-demo', 'K123+000', 'K130+000', ST_GeomFromText('LINESTRING(116.090 39.095,116.125 39.115,116.165 39.105)', 4326), '{"track":"up","source":"seed"}'::jsonb, 'org-works', 'HIGH', 7, 'ACTIVE', now(), now()),
('object-bridge-demo', 'BRIDGE', '试点铁路桥梁', 'line-demo', 'K126+200', 'K126+900', ST_GeomFromText('LINESTRING(116.124 39.113,116.131 39.115)', 4326), '{"span_count":8,"source":"seed"}'::jsonb, 'org-works', 'HIGH', 14, 'ACTIVE', now(), now()),
('object-flood-demo', 'FLOOD', '重点防洪点', 'line-demo', 'K128+100', 'K128+400', ST_GeomFromText('POLYGON((116.145 39.101,116.152 39.101,116.152 39.108,116.145 39.108,116.145 39.101))', 4326), '{"source":"seed"}'::jsonb, 'org-flood', 'CRITICAL', 3, 'ACTIVE', now(), now())
on conflict (id) do nothing;

insert into route_templates(id, name, object_type, payload_rules, flight_rules, status, created_at, updated_at) values
('template-railway', '铁路沿线巡检模板', 'RAILWAY', '{"required":["VISIBLE","RTK"]}'::jsonb, '{"altitude_m":80,"speed_mps":8,"side_overlap":0.6}'::jsonb, 'ACTIVE', now(), now()),
('template-bridge', '桥梁环绕巡检模板', 'BRIDGE', '{"required":["VISIBLE","ZOOM"]}'::jsonb, '{"altitude_m":60,"speed_mps":4,"orbit_radius_m":35}'::jsonb, 'ACTIVE', now(), now())
on conflict (id) do nothing;

insert into routes(id, name, object_id, template_id, status, current_version_id, created_by, created_at, updated_at) values
('route-manual', '试点沿线航线', 'object-line-demo', 'template-railway', 'PUBLISHED', 'route-version-manual-1', 'user-dispatcher', now(), now()),
('route-demo', '全链路试点航线', 'object-line-demo', 'template-railway', 'PUBLISHED', 'route-version-demo-1', 'user-dispatcher', now(), now())
on conflict (id) do nothing;

insert into route_versions(id, route_id, version_no, status, geom, waypoints, flight_parameters, payload_actions, validation_result, checksum, created_by, approved_by, created_at, published_at) values
('route-version-manual-1', 'route-manual', 1, 'PUBLISHED', ST_GeomFromText('LINESTRING Z(116.090 39.095 80,116.125 39.115 80,116.165 39.105 80)', 4326), '[{"sequence":1,"longitude":116.09,"latitude":39.095,"altitude_m":80},{"sequence":2,"longitude":116.125,"latitude":39.115,"altitude_m":80},{"sequence":3,"longitude":116.165,"latitude":39.105,"altitude_m":80}]'::jsonb, '{"speed_mps":8,"rth_altitude_m":100}'::jsonb, '[{"type":"TAKE_PHOTO","interval_s":2}]'::jsonb, '{"valid":true,"errors":[],"warnings":[]}'::jsonb, 'seed-route-manual-v1', 'user-dispatcher', 'user-dispatcher', now(), now()),
('route-version-demo-1', 'route-demo', 1, 'PUBLISHED', ST_GeomFromText('LINESTRING Z(116.090 39.095 75,116.130 39.112 75,116.165 39.105 75)', 4326), '[{"sequence":1,"longitude":116.09,"latitude":39.095,"altitude_m":75},{"sequence":2,"longitude":116.13,"latitude":39.112,"altitude_m":75},{"sequence":3,"longitude":116.165,"latitude":39.105,"altitude_m":75}]'::jsonb, '{"speed_mps":7,"rth_altitude_m":100}'::jsonb, '[{"type":"TAKE_PHOTO","interval_s":2}]'::jsonb, '{"valid":true,"errors":[],"warnings":[]}'::jsonb, 'seed-route-demo-v1', 'user-dispatcher', 'user-dispatcher', now(), now())
on conflict (id) do nothing;

insert into route_waypoints(id, route_version_id, sequence_no, geom, altitude_m, speed_mps, heading_deg, gimbal_pitch_deg, actions) values
('wp-manual-1', 'route-version-manual-1', 1, ST_GeomFromText('POINT Z(116.090 39.095 80)', 4326), 80, 8, 60, -45, '["TAKE_PHOTO"]'::jsonb),
('wp-manual-2', 'route-version-manual-1', 2, ST_GeomFromText('POINT Z(116.125 39.115 80)', 4326), 80, 8, 90, -45, '["TAKE_PHOTO"]'::jsonb),
('wp-manual-3', 'route-version-manual-1', 3, ST_GeomFromText('POINT Z(116.165 39.105 80)', 4326), 80, 8, 120, -45, '["TAKE_PHOTO"]'::jsonb),
('wp-demo-1', 'route-version-demo-1', 1, ST_GeomFromText('POINT Z(116.090 39.095 75)', 4326), 75, 7, 60, -45, '["TAKE_PHOTO"]'::jsonb),
('wp-demo-2', 'route-version-demo-1', 2, ST_GeomFromText('POINT Z(116.130 39.112 75)', 4326), 75, 7, 90, -45, '["TAKE_PHOTO"]'::jsonb),
('wp-demo-3', 'route-version-demo-1', 3, ST_GeomFromText('POINT Z(116.165 39.105 75)', 4326), 75, 7, 120, -45, '["TAKE_PHOTO"]'::jsonb)
on conflict (id) do nothing;

insert into uav_vendor_connections(id, name, vendor_code, connection_mode, environment, endpoint_url, workspace_id, secret_ref, capabilities, enabled, status, health_detail, last_checked_at, created_at, updated_at) values
('connection-simulator', '本地无人机模拟器', 'SIMULATOR', 'SIMULATOR', 'LOCAL', 'internal://simulator', 'local-workspace', null, '["DEVICE_TOPOLOGY","MISSION_DISPATCH","MISSION_PAUSE_RESUME","MISSION_CANCEL","TELEMETRY","MEDIA_UPLOAD","LIVE_STREAM"]'::jsonb, true, 'AVAILABLE', '{"network":"ok","authentication":"not_required","capabilities":"ready","business_handshake":"ready"}'::jsonb, now(), now(), now()),
('connection-dji', '大疆 Cloud API', 'DJI', 'CLOUD_API', 'PRODUCTION', null, null, 'secret://uav/dji', '[]'::jsonb, false, 'MATERIALS_REQUIRED', '{"network":"unchecked","authentication":"missing","reason":"缺少 AppKey、License、工作空间和设备信息"}'::jsonb, null, now(), now()),
('connection-jouav', '纵横云或本地 SDK', 'JOUAV', 'LOCAL_SDK', 'PRODUCTION', null, null, 'secret://uav/jouav', '[]'::jsonb, false, 'MATERIALS_REQUIRED', '{"network":"unchecked","authentication":"missing","reason":"缺少正式 SDK/API、授权和设备协议"}'::jsonb, null, now(), now())
on conflict (id) do nothing;

insert into uav_devices(id, connection_id, vendor_device_id, vendor_code, device_type, model, serial_number, capabilities, status, health_detail, geom, last_seen_at, created_at, updated_at) values
('uav-sim-01', 'connection-simulator', 'SIM-UAV-01', 'SIMULATOR', 'AIRCRAFT', '铁路巡检模拟机', 'SIM0001', '["RTK","VISIBLE","THERMAL","MISSION_PAUSE_RESUME","TELEMETRY"]'::jsonb, 'ONLINE', '{"battery_percent":92,"rtk":"FIXED","link_quality":98}'::jsonb, ST_GeomFromText('POINT(116.090 39.095)', 4326), now(), now(), now())
on conflict (id) do nothing;

insert into uav_docks(id, connection_id, vendor_dock_id, model, capabilities, status, geom, last_seen_at, created_at, updated_at) values
('dock-sim-01', 'connection-simulator', 'SIM-DOCK-01', '模拟无人值守机巢', '["AUTO_CHARGE","REMOTE_OPEN","WEATHER"]'::jsonb, 'ONLINE', ST_GeomFromText('POINT(116.089 39.094)', 4326), now(), now(), now())
on conflict (id) do nothing;

insert into responsibility_rules(id, line_id, object_type, scene, owner_org_id, assignee_user_id, priority, status, created_at, updated_at) values
('responsibility-works', 'line-demo', 'RAILWAY', null, 'org-works', 'user-field', 10, 'ACTIVE', now(), now()),
('responsibility-power', 'line-demo', 'POWER', null, 'org-power', null, 10, 'ACTIVE', now(), now()),
('responsibility-flood', 'line-demo', 'FLOOD', null, 'org-flood', null, 10, 'ACTIVE', now(), now())
on conflict (id) do nothing;

insert into alarm_policies(id, name, scene, severity_rules, notification_rules, sla_rules, status, version_no, created_at, updated_at) values
('alarm-policy-default', '铁路隐患默认策略', null, '{"critical":"IMMEDIATE","high":"LIMITED_TIME","medium":"OBSERVE","low":"RECORD"}'::jsonb, '{"channels":["IN_APP"],"retry":3}'::jsonb, '{"critical":{"ack_minutes":5,"handle_hours":2},"high":{"ack_minutes":30,"handle_hours":24},"medium":{"ack_hours":4,"handle_hours":72}}'::jsonb, 'ACTIVE', 1, now(), now())
on conflict (id) do nothing;

insert into capability_completion_status(capability_code, capability_name, stage, status, implementation_ref, blocker_type, blocker_reason, updated_at) values
('P0-01', '巡检类型、周期计划和对象台账', 'M1', 'IMPLEMENTED', 'inspection_plans, inspection_objects', null, null, now()),
('P0-02', '智能航线规划与航点编辑', 'M1', 'PARTIAL', 'routes, route_versions, route_waypoints', 'ALGORITHM', '已实现航线版本、航点与校验框架，复杂桥隧自动生成算法需现场参数', now()),
('P0-03', '载荷与飞行参数配置', 'M1-M2', 'IMPLEMENTED', 'route_templates, route_versions, uav_payloads', null, null, now()),
('P0-04', '真实无人机厂商任务下发', 'M2', 'EXTERNAL_BLOCKED', 'UavVendorAdapter + Simulator', 'VENDOR_MATERIAL', '缺少大疆和纵横正式授权、测试环境及设备', now()),
('P0-05', '实时遥测与实际航迹', 'M2', 'PARTIAL', 'telemetry_points, flight_tracks', 'VENDOR_MATERIAL', '平台能力已实现，真实遥测需厂商设备和协议', now()),
('P0-06', '巡检文件上传与弱网续传', 'M3', 'PARTIAL', 'upload_sessions, upload_parts', 'EDGE_ENVIRONMENT', '数据模型已实现，现场边缘代理需目标硬件与网络条件', now()),
('P0-07', '生产模型制品就绪', 'M4', 'EXTERNAL_BLOCKED', 'model artifact lifecycle', 'MODEL_ARTIFACT', '缺少四类已训练并验收的 ONNX 制品', now()),
('P0-08', '核心缺陷类别模型与验收集', 'M4', 'EXTERNAL_BLOCKED', 'model management workbench', 'DATASET', '缺少铁路专业标注样本、验收集和指标口径', now()),
('P0-09', '告警处置策略与通知中心', 'M4', 'PARTIAL', 'alarm_policies, notification_deliveries', 'EXTERNAL_SYSTEM', '站内策略可落地，短信或企业微信需账号与接口', now()),
('P0-10', '组织、部门、工段和责任规则', 'M5', 'IMPLEMENTED', 'organizations, responsibility_rules, duty_rosters', null, null, now()),
('P0-11', '现场处置与证据上传', 'M5', 'IMPLEMENTED', 'workorder_actions, workorder_attachments', null, null, now()),
('P0-12', '复飞复检与闭环销项', 'M5', 'PARTIAL', 'reinspection_requests, review_records', 'VENDOR_MATERIAL', '流程已实现，真实复飞依赖厂商任务接入', now()),
('P0-13', '正式归档与成果输出', 'M6', 'PARTIAL', 'archive_packages, report_jobs', 'TEMPLATE', '归档清单可生成，正式报告模板和电子签章待提供', now()),
('P0-14', '用户、组织与 RBAC', 'M0-M1', 'PARTIAL', 'platform_users, roles, user_roles', 'IDENTITY_PROVIDER', '本地角色模型已实现，统一身份平台参数待提供', now())
on conflict (capability_code) do update set status=excluded.status, implementation_ref=excluded.implementation_ref, blocker_type=excluded.blocker_type, blocker_reason=excluded.blocker_reason, updated_at=excluded.updated_at;
