-- Schema do Adaptador. Soft delete via status para não tornar a exclusão física irreversível.

create table if not exists public.spreadsheet_adapter_settings (
  workspace_id uuid primary key references public.workspaces (id) on delete cascade,
  allow_non_admin_model_upload boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.spreadsheet_models (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces (id) on delete cascade,
  name text not null,
  original_filename text not null,
  format text not null check (format in ('xlsx', 'csv')),
  storage_key text not null,
  content_sha256 text not null,
  sheet_name text,
  header_row integer not null default 1 check (header_row >= 1),
  columns_schema jsonb not null,
  created_by uuid not null references public.profiles (id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz,
  status text not null default 'active' check (status in ('active', 'deleted'))
);

create unique index if not exists spreadsheet_models_active_name_idx
  on public.spreadsheet_models (workspace_id, lower(name))
  where status = 'active';

create index if not exists spreadsheet_models_workspace_idx
  on public.spreadsheet_models (workspace_id, status);

create table if not exists public.spreadsheet_jobs (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces (id) on delete cascade,
  user_id uuid not null references public.profiles (id),
  model_id uuid not null references public.spreadsheet_models (id) on delete restrict,
  status text not null check (status in (
    'draft', 'validating', 'ready', 'queued', 'processing', 'completed', 'failed', 'expired', 'cancelled'
  )),
  source_count integer not null default 0 check (source_count >= 0 and source_count <= 10),
  current_source integer not null default 0,
  output_format text check (output_format in ('xlsx', 'csv', 'pdf')),
  progress jsonb not null default '{}'::jsonb,
  error_code text,
  idempotency_key text,
  started_at timestamptz,
  finished_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists spreadsheet_jobs_idempotency_idx
  on public.spreadsheet_jobs (user_id, idempotency_key)
  where idempotency_key is not null;

create index if not exists spreadsheet_jobs_workspace_user_idx
  on public.spreadsheet_jobs (workspace_id, user_id, status);

create table if not exists public.spreadsheet_job_sources (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references public.spreadsheet_jobs (id) on delete cascade,
  source_name text not null,
  temp_storage_key text not null,
  selected_sheet text,
  header_row integer not null default 1 check (header_row >= 1),
  row_count integer not null default 0,
  size_bytes integer not null check (size_bytes >= 0),
  status text not null check (status in (
    'pending', 'validated', 'invalid', 'mapped', 'processing', 'completed', 'failed', 'expired'
  )),
  columns_schema jsonb not null default '[]'::jsonb,
  error_code text,
  output_storage_key text,
  output_csv_key text,
  output_xlsx_key text,
  process_idempotency_key text,
  expires_at timestamptz not null,
  processed_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists spreadsheet_job_sources_job_idx on public.spreadsheet_job_sources (job_id);
create index if not exists spreadsheet_job_sources_expires_idx on public.spreadsheet_job_sources (expires_at);

create table if not exists public.spreadsheet_job_mappings (
  id uuid primary key default gen_random_uuid(),
  job_source_id uuid not null references public.spreadsheet_job_sources (id) on delete cascade,
  source_column text not null check (char_length(source_column) > 0),
  model_column text not null check (char_length(model_column) > 0),
  is_required boolean not null default false,
  position integer not null check (position >= 0),
  unique (job_source_id, source_column),
  unique (job_source_id, model_column)
);

create or replace function public.jobs_model_same_workspace()
returns trigger
language plpgsql
as $$
declare
  model_ws uuid;
begin
  select workspace_id into model_ws from public.spreadsheet_models where id = new.model_id;
  if model_ws is null or model_ws <> new.workspace_id then
    raise exception 'MODEL_WORKSPACE_MISMATCH' using errcode = '23514';
  end if;
  if new.user_id is null then
    raise exception 'JOB_USER_REQUIRED' using errcode = '23514';
  end if;
  return new;
end;
$$;

drop trigger if exists spreadsheet_jobs_workspace_guard on public.spreadsheet_jobs;
create trigger spreadsheet_jobs_workspace_guard
  before insert or update of model_id, workspace_id on public.spreadsheet_jobs
  for each row execute function public.jobs_model_same_workspace();

create or replace function public.enforce_active_model_limit()
returns trigger
language plpgsql
as $$
declare
  total integer;
begin
  if new.status = 'active' then
    select count(*) into total
      from public.spreadsheet_models
      where workspace_id = new.workspace_id
        and status = 'active'
        and id <> new.id;
    if total >= 10 then
      raise exception 'MODEL_LIMIT' using errcode = '23514';
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists spreadsheet_models_limit on public.spreadsheet_models;
create trigger spreadsheet_models_limit
  before insert or update of status, workspace_id on public.spreadsheet_models
  for each row execute function public.enforce_active_model_limit();

create or replace function public.enforce_source_limit()
returns trigger
language plpgsql
as $$
declare
  total integer;
begin
  select count(*) into total from public.spreadsheet_job_sources where job_id = new.job_id;
  if total >= 10 then
    raise exception 'SOURCE_LIMIT' using errcode = '23514';
  end if;
  return new;
end;
$$;

drop trigger if exists spreadsheet_sources_limit on public.spreadsheet_job_sources;
create trigger spreadsheet_sources_limit
  before insert on public.spreadsheet_job_sources
  for each row execute function public.enforce_source_limit();