-- Canonical PostgreSQL schema for a fresh Firefly installation.
-- Creates only Firefly-managed objects; the database, role, and grants are operator-owned.

create table if not exists firefly_schema_version (
    version integer primary key,
    installed_at timestamp not null
);

insert into firefly_schema_version (version, installed_at)
select 1, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 1);

create table if not exists firefly_node (
    node_id varchar(128) primary key,
    roles varchar(512) not null,
    registered_at timestamp with time zone not null,
    last_heartbeat_at timestamp with time zone not null,
    status varchar(32) not null,
    metadata text not null
);

create table if not exists firefly_cluster_metadata (
    metadata_key varchar(128) primary key,
    metadata_value text not null,
    updated_at timestamp with time zone not null
);

insert into firefly_cluster_metadata (metadata_key, metadata_value, updated_at)
select 'scheduler.shard-count', '32', current_timestamp
where not exists (select 1 from firefly_cluster_metadata where metadata_key='scheduler.shard-count');

insert into firefly_cluster_metadata (metadata_key, metadata_value, updated_at)
select 'jobs.revision', '0', current_timestamp
where not exists (select 1 from firefly_cluster_metadata where metadata_key='jobs.revision');

create table if not exists firefly_executor (
    executor_name varchar(128) primary key,
    description varchar(1024) not null,
    protocols varchar(256) not null,
    metadata text not null,
    enabled boolean not null
);

create table if not exists firefly_job_group (
    group_id varchar(128) primary key,
    group_name varchar(256) not null,
    executor_name varchar(128) not null,
    metadata text not null,
    enabled boolean not null
);

create table if not exists firefly_job (
    job_id varchar(128) primary key,
    group_id varchar(128) not null,
    job_name varchar(256) not null,
    handler_name varchar(256) not null,
    schedule_type varchar(32) not null,
    schedule_value varchar(512) not null,
    zone_id varchar(128) not null,
    misfire_policy varchar(32) not null,
    misfire_grace varchar(64) not null,
    concurrency_policy varchar(32) not null,
    max_catch_up_count integer not null,
    timeout_value varchar(64) not null,
    parameters text not null,
    shard_id integer not null default 0,
    dispatch_mode varchar(32) not null default 'UNICAST',
    routing_strategy varchar(32) not null default 'ROUND_ROBIN',
    completion_policy varchar(32) not null default 'ALL_SUCCESS',
    shard_count integer not null default 1,
    routing_key varchar(512) not null default '',
    retry_scope varchar(32) not null default 'FAILED_TARGETS_ONLY',
    enabled boolean not null,
    next_fire_time timestamp with time zone not null,
    version bigint not null
);

create index if not exists idx_firefly_job_due
    on firefly_job (enabled, next_fire_time, job_id);

create index if not exists idx_firefly_job_shard_due
    on firefly_job (shard_id, enabled, next_fire_time, job_id);

create table if not exists firefly_execution_target (
    target_execution_id varchar(384) primary key,
    execution_id varchar(256) not null,
    instance_id varchar(256) not null,
    gateway_node_id varchar(128) not null,
    shard_index integer,
    status varchar(32) not null,
    attempt integer not null,
    acknowledged_at timestamp with time zone,
    completed_at timestamp with time zone,
    error_message text not null,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null
);

create index if not exists idx_firefly_execution_target_parent on firefly_execution_target (execution_id, target_execution_id);

create table if not exists firefly_audit_log (
    audit_id varchar(64) primary key,
    occurred_at timestamp not null,
    actor varchar(256) not null,
    role_name varchar(64) not null,
    action_name varchar(128) not null,
    resource_type varchar(128) not null,
    resource_id varchar(256) not null,
    outcome varchar(64) not null,
    before_payload text not null,
    after_payload text not null,
    detail text not null
);

create index if not exists idx_firefly_audit_time on firefly_audit_log (occurred_at, audit_id);

create table if not exists firefly_job_history (
    history_id varchar(64) primary key,
    job_id varchar(128) not null,
    job_version bigint not null,
    action_name varchar(64) not null,
    actor varchar(256) not null,
    before_payload text not null,
    after_payload text not null,
    occurred_at timestamp not null
);

create index if not exists idx_firefly_job_history_job
    on firefly_job_history (job_id, occurred_at, history_id);

create table if not exists firefly_integration_key (
    key_id varchar(64) primary key,
    key_hash varchar(512) not null,
    version bigint not null,
    created_at timestamp with time zone not null,
    updated_at timestamp with time zone not null
);

insert into firefly_schema_version (version, installed_at)
select 2, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 2);

insert into firefly_schema_version (version, installed_at)
select 3, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 3);

insert into firefly_schema_version (version, installed_at)
select 4, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 4);

insert into firefly_schema_version (version, installed_at)
select 5, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 5);

insert into firefly_schema_version (version, installed_at)
select 6, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 6);

insert into firefly_schema_version (version, installed_at)
select 7, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 7);

insert into firefly_schema_version (version, installed_at)
select 8, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 8);

insert into firefly_schema_version (version, installed_at)
select 9, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 9);

insert into firefly_schema_version (version, installed_at)
select 10, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 10);

insert into firefly_schema_version (version, installed_at)
select 11, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 11);

insert into firefly_schema_version (version, installed_at)
select 12, current_timestamp
where not exists (select 1 from firefly_schema_version where version = 12);
