alter table demo_runs
    add column if not exists step_snapshots jsonb not null default '{}'::jsonb,
    add column if not exists next_sequence bigint not null default 0;

alter table demo_run_events
    add column if not exists sequence_no bigint,
    add column if not exists event_type varchar(128),
    add column if not exists step_progress int,
    add column if not exists attempt int not null default 1;

update demo_run_events
set sequence_no = numbered.sequence_no,
    event_type = case
        when demo_run_events.status = 'completed' then 'step.completed'
        when demo_run_events.status = 'failed' then 'step.failed'
        when demo_run_events.status = 'cancelled' then 'step.cancelled'
        else 'step.progress'
    end,
    step_progress = case when demo_run_events.status = 'completed' then 100 else 50 end
from (
    select id, row_number() over (partition by run_id order by created_at, id) as sequence_no
    from demo_run_events
) numbered
where demo_run_events.id = numbered.id
  and demo_run_events.sequence_no is null;

update demo_runs runs
set next_sequence = coalesce((
    select max(events.sequence_no)
    from demo_run_events events
    where events.run_id = runs.id
), 0);

alter table demo_run_events
    alter column sequence_no set not null,
    alter column event_type set not null,
    alter column step_progress set not null;

create unique index if not exists uk_demo_run_events_sequence
    on demo_run_events(run_id, sequence_no);

create table if not exists sample_feedback_records (
    id varchar(64) primary key,
    run_id varchar(64) not null references demo_runs(id),
    task_id varchar(64) not null references inspection_tasks(id),
    resource_id varchar(64),
    ai_result_id varchar(64),
    alarm_id varchar(64),
    workorder_id varchar(64),
    feedback_reason varchar(128) not null,
    priority varchar(32) not null,
    labeling_status varchar(32) not null,
    target_model_group varchar(128) not null,
    source_model_version varchar(64),
    quality_status varchar(32) not null,
    created_at timestamptz not null,
    updated_at timestamptz not null
);

create index if not exists idx_sample_feedback_run
    on sample_feedback_records(run_id, created_at);

create table if not exists work_order_operations (
    id varchar(64) primary key,
    workorder_id varchar(64) not null references work_orders(id),
    operation_type varchar(64) not null,
    operator_name varchar(128) not null,
    result varchar(128),
    comment text,
    created_at timestamptz not null
);

create index if not exists idx_work_order_operations_workorder
    on work_order_operations(workorder_id, created_at);
