-- UmamiEdge database compatibility repair pack
-- Run this first when upgrading an existing Supabase database or after any partial failed migration.
-- Safe execution order after this file:
-- 1. docs/supabase-schema.sql
-- 2. docs/rls-policies.sql
-- 3. docs/seed-demo.sql optional

create extension if not exists pgcrypto;

-- 1) Enum safety. PostgreSQL does not support CREATE TYPE IF NOT EXISTS for enums.
do $$
begin
  if not exists (select 1 from pg_type where typname = 'organization_role' and typnamespace = 'public'::regnamespace) then
    create type public.organization_role as enum ('owner', 'admin', 'operator', 'viewer');
  end if;
  if not exists (select 1 from pg_type where typname = 'node_status' and typnamespace = 'public'::regnamespace) then
    create type public.node_status as enum ('provisioning', 'online', 'degraded', 'offline', 'retired');
  end if;
  if not exists (select 1 from pg_type where typname = 'telemetry_severity' and typnamespace = 'public'::regnamespace) then
    create type public.telemetry_severity as enum ('info', 'warning', 'critical');
  end if;
  if not exists (select 1 from pg_type where typname = 'incident_status' and typnamespace = 'public'::regnamespace) then
    create type public.incident_status as enum ('open', 'acknowledged', 'resolved');
  end if;
  if not exists (select 1 from pg_type where typname = 'incident_priority' and typnamespace = 'public'::regnamespace) then
    create type public.incident_priority as enum ('low', 'medium', 'high', 'critical');
  end if;
  if not exists (select 1 from pg_type where typname = 'usage_event_kind' and typnamespace = 'public'::regnamespace) then
    create type public.usage_event_kind as enum ('chat_completion', 'embedding', 'routing', 'gpu_second', 'telemetry');
  end if;
end $$;

alter type public.organization_role add value if not exists 'owner';
alter type public.organization_role add value if not exists 'admin';
alter type public.organization_role add value if not exists 'operator';
alter type public.organization_role add value if not exists 'viewer';
alter type public.node_status add value if not exists 'provisioning';
alter type public.node_status add value if not exists 'online';
alter type public.node_status add value if not exists 'degraded';
alter type public.node_status add value if not exists 'offline';
alter type public.node_status add value if not exists 'retired';
alter type public.telemetry_severity add value if not exists 'info';
alter type public.telemetry_severity add value if not exists 'warning';
alter type public.telemetry_severity add value if not exists 'critical';
alter type public.incident_status add value if not exists 'open';
alter type public.incident_status add value if not exists 'acknowledged';
alter type public.incident_status add value if not exists 'resolved';
alter type public.incident_priority add value if not exists 'low';
alter type public.incident_priority add value if not exists 'medium';
alter type public.incident_priority add value if not exists 'high';
alter type public.incident_priority add value if not exists 'critical';
alter type public.usage_event_kind add value if not exists 'chat_completion';
alter type public.usage_event_kind add value if not exists 'embedding';
alter type public.usage_event_kind add value if not exists 'routing';
alter type public.usage_event_kind add value if not exists 'gpu_second';
alter type public.usage_event_kind add value if not exists 'telemetry';

-- 2) Older edge_sites tables may lack connectivity_tier and other normalized columns.
do $$
begin
  if to_regclass('public.edge_sites') is not null then
    alter table public.edge_sites add column if not exists name text;
    alter table public.edge_sites add column if not exists site_type text;
    alter table public.edge_sites add column if not exists city text;
    alter table public.edge_sites add column if not exists country text;
    alter table public.edge_sites add column if not exists power_capacity_kw numeric default 0;
    alter table public.edge_sites add column if not exists connectivity_tier text;
    alter table public.edge_sites add column if not exists created_at timestamptz default now();

    update public.edge_sites set name = coalesce(nullif(name, ''), 'Unassigned site') where name is null or name = '';
    update public.edge_sites set site_type = coalesce(nullif(site_type, ''), 'commercial site') where site_type is null or site_type = '';
    update public.edge_sites set city = coalesce(nullif(city, ''), 'Unknown') where city is null or city = '';
    update public.edge_sites set country = coalesce(nullif(country, ''), 'Unknown') where country is null or country = '';
    update public.edge_sites set power_capacity_kw = coalesce(power_capacity_kw, 0) where power_capacity_kw is null;
    update public.edge_sites set created_at = coalesce(created_at, now()) where created_at is null;

    alter table public.edge_sites alter column name set not null;
    alter table public.edge_sites alter column site_type set not null;
    alter table public.edge_sites alter column city set not null;
    alter table public.edge_sites alter column country set not null;
    alter table public.edge_sites alter column power_capacity_kw set default 0;
    alter table public.edge_sites alter column power_capacity_kw set not null;
    alter table public.edge_sites alter column created_at set default now();
    alter table public.edge_sites alter column created_at set not null;
  end if;
end $$;

-- 3) Older node_api_keys tables may lack lookup/hash columns used by secure telemetry.
do $$
begin
  if to_regclass('public.node_api_keys') is not null then
    alter table public.node_api_keys add column if not exists name text;
    alter table public.node_api_keys add column if not exists key_prefix text;
    alter table public.node_api_keys add column if not exists key_hash text;
    alter table public.node_api_keys add column if not exists last_used_at timestamptz;
    alter table public.node_api_keys add column if not exists revoked_at timestamptz;
    alter table public.node_api_keys add column if not exists created_at timestamptz default now();

    update public.node_api_keys set name = coalesce(nullif(name, ''), 'Recovered node key') where name is null or name = '';
    update public.node_api_keys set key_prefix = coalesce(nullif(key_prefix, ''), 'legacy') where key_prefix is null or key_prefix = '';
    update public.node_api_keys set key_hash = coalesce(nullif(key_hash, ''), encode(digest(id::text || ':legacy', 'sha256'), 'hex')) where key_hash is null or key_hash = '';
    update public.node_api_keys set created_at = coalesce(created_at, now()) where created_at is null;

    alter table public.node_api_keys alter column name set not null;
    alter table public.node_api_keys alter column key_prefix set not null;
    alter table public.node_api_keys alter column key_hash set not null;
    alter table public.node_api_keys alter column created_at set default now();
    alter table public.node_api_keys alter column created_at set not null;
  end if;
end $$;

-- 4) Older audit_events tables may lack metadata and target fields used by seed data.
do $$
begin
  if to_regclass('public.audit_events') is not null then
    alter table public.audit_events add column if not exists actor_user_id uuid references auth.users(id) on delete set null;
    alter table public.audit_events add column if not exists target_type text;
    alter table public.audit_events add column if not exists target_id uuid;
    alter table public.audit_events add column if not exists metadata jsonb default '{}'::jsonb;
    alter table public.audit_events add column if not exists created_at timestamptz default now();

    update public.audit_events set target_type = coalesce(nullif(target_type, ''), 'system') where target_type is null or target_type = '';
    update public.audit_events set metadata = coalesce(metadata, '{}'::jsonb) where metadata is null;
    update public.audit_events set created_at = coalesce(created_at, now()) where created_at is null;

    alter table public.audit_events alter column target_type set not null;
    alter table public.audit_events alter column metadata set default '{}'::jsonb;
    alter table public.audit_events alter column metadata set not null;
    alter table public.audit_events alter column created_at set default now();
    alter table public.audit_events alter column created_at set not null;
  end if;
end $$;

-- 5) Older deployment_runs tables may have a NOT NULL version column.
do $$
begin
  if to_regclass('public.deployment_runs') is not null then
    alter table public.deployment_runs add column if not exists organization_id uuid references public.organizations(id) on delete cascade;
    alter table public.deployment_runs add column if not exists environment text;
    alter table public.deployment_runs add column if not exists release_name text;
    alter table public.deployment_runs add column if not exists version text;
    alter table public.deployment_runs add column if not exists status text default 'planned';
    alter table public.deployment_runs add column if not exists commit_sha text;
    alter table public.deployment_runs add column if not exists deployed_by uuid references auth.users(id) on delete set null;
    alter table public.deployment_runs add column if not exists evidence_uri text;
    alter table public.deployment_runs add column if not exists metadata jsonb default '{}'::jsonb;
    alter table public.deployment_runs add column if not exists started_at timestamptz default now();
    alter table public.deployment_runs add column if not exists completed_at timestamptz;

    update public.deployment_runs set environment = coalesce(nullif(environment, ''), 'demo') where environment is null or environment = '';
    update public.deployment_runs set release_name = coalesce(nullif(release_name, ''), 'global-launch') where release_name is null or release_name = '';
    update public.deployment_runs set version = coalesce(nullif(version, ''), nullif(release_name, ''), 'global-launch') where version is null or version = '';
    update public.deployment_runs set status = coalesce(nullif(status, ''), 'planned') where status is null or status = '';
    update public.deployment_runs set metadata = coalesce(metadata, '{}'::jsonb) where metadata is null;
    update public.deployment_runs set started_at = coalesce(started_at, now()) where started_at is null;

    alter table public.deployment_runs alter column environment set not null;
    alter table public.deployment_runs alter column release_name set not null;
    alter table public.deployment_runs alter column version set default 'global-launch';
    alter table public.deployment_runs alter column version set not null;
    alter table public.deployment_runs alter column status set default 'planned';
    alter table public.deployment_runs alter column status set not null;
    alter table public.deployment_runs alter column metadata set default '{}'::jsonb;
    alter table public.deployment_runs alter column metadata set not null;
    alter table public.deployment_runs alter column started_at set default now();
    alter table public.deployment_runs alter column started_at set not null;
  end if;
end $$;

-- 6) Drop helper functions that may have incompatible parameter names. RLS script recreates them.
drop function if exists public.is_org_member(uuid) cascade;
drop function if exists public.is_org_admin(uuid) cascade;
drop function if exists public.current_org_role(uuid) cascade;

-- 7) Indexes are created by docs/supabase-schema.sql after all tables and columns exist.

-- ----------
-- Control-room compatibility repairs
-- ----------
create table if not exists public.launch_readiness_assessments (id uuid primary key default gen_random_uuid());
create table if not exists public.customer_success_accounts (id uuid primary key default gen_random_uuid());
create table if not exists public.risk_register_entries (id uuid primary key default gen_random_uuid());
create table if not exists public.support_escalations (id uuid primary key default gen_random_uuid());
create table if not exists public.runtime_health_checks (id uuid primary key default gen_random_uuid());

alter table if exists public.launch_readiness_assessments add column if not exists organization_id uuid references public.organizations(id) on delete cascade;
alter table if exists public.launch_readiness_assessments add column if not exists title text;
alter table if exists public.launch_readiness_assessments add column if not exists posture text not null default 'watch';
alter table if exists public.launch_readiness_assessments add column if not exists owner text;
alter table if exists public.launch_readiness_assessments add column if not exists score integer not null default 0;
alter table if exists public.launch_readiness_assessments add column if not exists evidence_uri text;
alter table if exists public.launch_readiness_assessments add column if not exists metadata jsonb not null default '{}'::jsonb;
alter table if exists public.launch_readiness_assessments add column if not exists created_at timestamptz not null default now();
alter table if exists public.launch_readiness_assessments add column if not exists updated_at timestamptz not null default now();

alter table if exists public.customer_success_accounts add column if not exists organization_id uuid references public.organizations(id) on delete cascade;
alter table if exists public.customer_success_accounts add column if not exists customer_name text;
alter table if exists public.customer_success_accounts add column if not exists segment text;
alter table if exists public.customer_success_accounts add column if not exists region text;
alter table if exists public.customer_success_accounts add column if not exists lifecycle_stage text not null default 'discovery';
alter table if exists public.customer_success_accounts add column if not exists contract_value_usd numeric not null default 0;
alter table if exists public.customer_success_accounts add column if not exists next_milestone text;
alter table if exists public.customer_success_accounts add column if not exists risk_level text not null default 'medium';
alter table if exists public.customer_success_accounts add column if not exists metadata jsonb not null default '{}'::jsonb;
alter table if exists public.customer_success_accounts add column if not exists created_at timestamptz not null default now();
alter table if exists public.customer_success_accounts add column if not exists updated_at timestamptz not null default now();

alter table if exists public.risk_register_entries add column if not exists organization_id uuid references public.organizations(id) on delete cascade;
alter table if exists public.risk_register_entries add column if not exists risk_title text;
alter table if exists public.risk_register_entries add column if not exists category text;
alter table if exists public.risk_register_entries add column if not exists severity text not null default 'medium';
alter table if exists public.risk_register_entries add column if not exists likelihood text not null default 'medium';
alter table if exists public.risk_register_entries add column if not exists mitigation text;
alter table if exists public.risk_register_entries add column if not exists owner text;
alter table if exists public.risk_register_entries add column if not exists status text not null default 'open';
alter table if exists public.risk_register_entries add column if not exists metadata jsonb not null default '{}'::jsonb;
alter table if exists public.risk_register_entries add column if not exists created_at timestamptz not null default now();
alter table if exists public.risk_register_entries add column if not exists updated_at timestamptz not null default now();

alter table if exists public.support_escalations add column if not exists organization_id uuid references public.organizations(id) on delete cascade;
alter table if exists public.support_escalations add column if not exists customer_name text;
alter table if exists public.support_escalations add column if not exists priority text not null default 'p3';
alter table if exists public.support_escalations add column if not exists status text not null default 'new';
alter table if exists public.support_escalations add column if not exists summary text;
alter table if exists public.support_escalations add column if not exists current_owner text;
alter table if exists public.support_escalations add column if not exists next_update_at timestamptz;
alter table if exists public.support_escalations add column if not exists metadata jsonb not null default '{}'::jsonb;
alter table if exists public.support_escalations add column if not exists created_at timestamptz not null default now();
alter table if exists public.support_escalations add column if not exists updated_at timestamptz not null default now();

alter table if exists public.runtime_health_checks add column if not exists organization_id uuid references public.organizations(id) on delete cascade;
alter table if exists public.runtime_health_checks add column if not exists runtime_name text;
alter table if exists public.runtime_health_checks add column if not exists region_code text;
alter table if exists public.runtime_health_checks add column if not exists status text not null default 'unknown';
alter table if exists public.runtime_health_checks add column if not exists latency_p95_ms integer not null default 0;
alter table if exists public.runtime_health_checks add column if not exists queue_depth integer not null default 0;
alter table if exists public.runtime_health_checks add column if not exists metadata jsonb not null default '{}'::jsonb;
alter table if exists public.runtime_health_checks add column if not exists last_checked_at timestamptz not null default now();
