-- v12.0 integrated hardening delta. Prior migrations remain immutable.

-- Rename ambiguous columns while preserving existing data.
do $$
begin
    if exists (
        select 1 from information_schema.columns
        where table_schema = 'signal_engine'
          and table_name = 'signal_decision'
          and column_name = 'lot_id'
    ) and not exists (
        select 1 from information_schema.columns
        where table_schema = 'signal_engine'
          and table_name = 'signal_decision'
          and column_name = 'position_lot_id'
    ) then
        alter table signal_engine.signal_decision
            rename column lot_id to position_lot_id;
    end if;

    if exists (
        select 1 from information_schema.columns
        where table_schema = 'signal_engine'
          and table_name = 'signal_decision'
          and column_name = 'sell_fraction'
    ) and not exists (
        select 1 from information_schema.columns
        where table_schema = 'signal_engine'
          and table_name = 'signal_decision'
          and column_name = 'sell_ratio_of_lot'
    ) then
        alter table signal_engine.signal_decision
            rename column sell_fraction to sell_ratio_of_lot;
    end if;
end;
$$;

alter table signal_engine.signal_decision
    add column if not exists target_portfolio_weight_after numeric(12,8),
    add column if not exists dataset_id text,
    add column if not exists model_version text,
    add column if not exists config_version text,
    add column if not exists code_sha text,
    add column if not exists idempotency_key text,
    add column if not exists correlation_id text;

update signal_engine.signal_decision d
set dataset_id = coalesce(d.dataset_id, e.dataset_id),
    model_version = coalesce(d.model_version, e.model_version),
    config_version = coalesce(d.config_version, e.config_version),
    code_sha = coalesce(d.code_sha, 'LEGACY_UNKNOWN'),
    target_portfolio_weight_after = coalesce(d.target_portfolio_weight_after, 0),
    idempotency_key = coalesce(d.idempotency_key, 'legacy:' || d.decision_id::text),
    correlation_id = coalesce(d.correlation_id, 'legacy:' || d.decision_id::text)
from signal_engine.evidence_snapshot e
where e.evidence_id = d.evidence_id;

alter table signal_engine.signal_decision
    alter column target_portfolio_weight_after set not null,
    alter column dataset_id set not null,
    alter column model_version set not null,
    alter column config_version set not null,
    alter column code_sha set not null,
    alter column idempotency_key set not null,
    alter column correlation_id set not null;

alter table signal_engine.signal_decision
    drop constraint if exists ck_signal_decision_sell_ratio_of_lot,
    add constraint ck_signal_decision_sell_ratio_of_lot
        check (sell_ratio_of_lot between 0 and 1),
    drop constraint if exists ck_signal_decision_target_weight,
    add constraint ck_signal_decision_target_weight
        check (target_portfolio_weight_after between 0 and 1);

create unique index if not exists ux_signal_decision_idempotency
    on signal_engine.signal_decision (idempotency_key);

create index if not exists ix_signal_decision_lot_created
    on signal_engine.signal_decision (position_lot_id, created_at desc);

-- Rebuildable, versioned read model. It is populated only by approved upstream slices.
create table if not exists signal_engine.sell_decision_context (
    context_id uuid primary key,
    position_lot_id uuid not null,
    cycle_id uuid not null,
    evidence_id text not null references signal_engine.evidence_snapshot(evidence_id),
    dataset_id text not null,
    model_version text not null,
    config_version text not null,
    code_sha text not null,
    as_of timestamptz not null,
    published_at_cutoff timestamptz not null,
    current_portfolio_weight numeric(12,8) not null
        check (current_portfolio_weight between 0 and 1),
    strategic_core_floor_weight numeric(12,8) not null
        check (strategic_core_floor_weight between 0 and current_portfolio_weight),
    hard_impairment_approved boolean not null,
    capital_floor_breached boolean not null,
    survival_sell_ratio_of_lot numeric(12,8) not null
        check (survival_sell_ratio_of_lot between 0 and 1),
    gap_below_floor_atr numeric(18,8) not null check (gap_below_floor_atr >= 0),
    consecutive_close_breaches integer not null check (consecutive_close_breaches >= 0),
    cooldown_satisfied boolean not null,
    concentration_sell_ratio_of_lot numeric(12,8) not null
        check (concentration_sell_ratio_of_lot between 0 and 1),
    opportunity_edge_lower_bound numeric(18,8) not null,
    opportunity_sell_ratio_of_lot numeric(12,8) not null
        check (opportunity_sell_ratio_of_lot between 0 and 1),
    quality_status text not null check (quality_status in ('PASS', 'WARN', 'QUARANTINED')),
    source_watermark text not null,
    projection_version integer not null,
    content_hash text not null unique,
    created_at timestamptz not null default now(),
    unique (position_lot_id, as_of, projection_version)
);

create index if not exists ix_sell_decision_context_lookup
    on signal_engine.sell_decision_context (position_lot_id, as_of desc)
    where quality_status = 'PASS';

drop trigger if exists sell_decision_context_immutable on signal_engine.sell_decision_context;
create trigger sell_decision_context_immutable
before update or delete on signal_engine.sell_decision_context
for each row execute function signal_engine.prevent_immutable_change();

-- Audit rows are append-only. Corrections are represented as new events.
create table if not exists signal_engine.decision_audit_event (
    audit_event_id uuid primary key,
    decision_id uuid not null references signal_engine.signal_decision(decision_id),
    event_type text not null,
    actor_id text not null,
    correlation_id text not null,
    payload_json jsonb not null,
    payload_hash text not null,
    occurred_at timestamptz not null,
    unique (decision_id, event_type, payload_hash)
);

drop trigger if exists decision_audit_event_immutable on signal_engine.decision_audit_event;
create trigger decision_audit_event_immutable
before update or delete on signal_engine.decision_audit_event
for each row execute function signal_engine.prevent_immutable_change();