alter table model_artifacts
    add column if not exists package_format varchar(32) not null default 'onnx-package',
    add column if not exists manifest jsonb not null default '{}'::jsonb,
    add column if not exists source_type varchar(32) not null default 'seed',
    add column if not exists lifecycle_status varchar(32) not null default 'registered',
    add column if not exists created_by varchar(128) not null default '算法平台',
    add column if not exists released_at timestamptz,
    add column if not exists recycled_at timestamptz;

create table if not exists model_artifact_files (
    id varchar(64) primary key,
    artifact_id varchar(64) not null references model_artifacts(id) on delete cascade,
    component_type varchar(32) not null,
    file_name varchar(512) not null,
    object_key text not null,
    storage_url text not null,
    sha256 char(64) not null,
    size_bytes bigint not null,
    content_type varchar(128),
    validation_status varchar(32) not null default 'uploaded',
    validation_report jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    unique(artifact_id, component_type, file_name)
);

create index if not exists idx_model_artifact_files_artifact
    on model_artifact_files(artifact_id, component_type);

create table if not exists model_artifact_installations (
    id varchar(64) primary key,
    artifact_id varchar(64) not null references model_artifacts(id),
    runtime_deployment_id varchar(64) not null references model_runtime_deployments(id),
    runtime_node_id varchar(128) not null default 'shared-volume',
    status varchar(32) not null,
    install_path text,
    installed_manifest jsonb not null default '{}'::jsonb,
    loaded boolean not null default false,
    active_request_count int not null default 0,
    installed_at timestamptz,
    loaded_at timestamptz,
    last_verified_at timestamptz,
    error_code varchar(64),
    error_detail jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    unique(artifact_id, runtime_deployment_id, runtime_node_id)
);

create index if not exists idx_model_artifact_installations_deployment
    on model_artifact_installations(runtime_deployment_id, status, updated_at desc);

create table if not exists model_artifact_jobs (
    id varchar(64) primary key,
    job_type varchar(32) not null,
    artifact_id varchar(64) references model_artifacts(id),
    runtime_deployment_id varchar(64) not null references model_runtime_deployments(id),
    installation_id varchar(64) references model_artifact_installations(id),
    status varchar(32) not null,
    progress numeric(5,2) not null default 0,
    current_stage varchar(64),
    idempotency_key varchar(128),
    approval_ticket varchar(128),
    options jsonb not null default '{}'::jsonb,
    trace_id varchar(64) not null,
    operator_name varchar(128) not null,
    reason text,
    error_code varchar(64),
    error_message text,
    created_at timestamptz not null default now(),
    started_at timestamptz,
    finished_at timestamptz,
    updated_at timestamptz not null default now()
);

create unique index if not exists uk_model_artifact_jobs_idempotency
    on model_artifact_jobs(idempotency_key) where idempotency_key is not null;

create index if not exists idx_model_artifact_jobs_query
    on model_artifact_jobs(runtime_deployment_id, status, created_at desc);

create table if not exists model_artifact_job_events (
    id varchar(64) primary key,
    job_id varchar(64) not null references model_artifact_jobs(id) on delete cascade,
    sequence_no bigint not null,
    stage varchar(64),
    status varchar(32) not null,
    progress numeric(5,2) not null,
    message text not null,
    payload jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    unique(job_id, sequence_no)
);

alter table model_runtime_deployments
    add column if not exists desired_artifact_id varchar(64) references model_artifacts(id),
    add column if not exists installed_artifact_id varchar(64) references model_artifacts(id),
    add column if not exists artifact_sync_status varchar(32) not null default 'unknown';

update model_artifacts
set package_format = case when lower(artifact_type)='open3d' then 'builtin' else 'onnx-package' end,
    lifecycle_status = case when lower(artifact_type)='open3d' then 'released' else lifecycle_status end,
    released_at = case when lower(artifact_type)='open3d' then coalesce(released_at, created_at) else released_at end;

with latest_artifact as (
    select distinct on (model_version_id) id, model_version_id, artifact_type
    from model_artifacts
    order by model_version_id, created_at desc
)
update model_runtime_deployments d
set desired_artifact_id = a.id,
    installed_artifact_id = case when lower(a.artifact_type)='open3d' then a.id else d.installed_artifact_id end,
    artifact_sync_status = case when lower(a.artifact_type)='open3d' then 'ready' else d.artifact_sync_status end
from latest_artifact a
where a.model_version_id=d.model_version_id and d.desired_artifact_id is null;

insert into model_artifact_installations(
    id, artifact_id, runtime_deployment_id, runtime_node_id, status,
    install_path, installed_manifest, loaded, installed_at, last_verified_at
)
select 'install-' || substr(md5(d.id || a.id), 1, 18), a.id, d.id, 'builtin', 'ready',
       'builtin://open3d-geometry', '{"runtime_component":true}'::jsonb, true, now(), now()
from model_runtime_deployments d
join model_artifacts a on a.id=d.installed_artifact_id
where lower(a.artifact_type)='open3d'
on conflict (artifact_id, runtime_deployment_id, runtime_node_id) do nothing;
