88 lines
3.3 KiB
PL/PgSQL
88 lines
3.3 KiB
PL/PgSQL
-- Tablero CDC - Tarifario / Estimación interna
|
|
-- Crea la tabla que guarda las líneas de costo por proyecto.
|
|
-- No modifica proyectos existentes, no toca created_at, no toca Google Sheets ni n8n.
|
|
|
|
create table if not exists public.tablero_cdc_project_pricing_items (
|
|
id uuid primary key default gen_random_uuid(),
|
|
project_id uuid not null references public.tablero_cdc_projects(id) on delete cascade,
|
|
source text not null default 'manual' check (source in ('tariff', 'manual')),
|
|
category text not null default '',
|
|
service_name text not null default '',
|
|
work_type text not null default '',
|
|
complexity_level text not null default '',
|
|
reference_label text not null default '',
|
|
reference_min numeric,
|
|
reference_max numeric,
|
|
amount numeric not null default 0 check (amount >= 0),
|
|
description text not null default '',
|
|
sort_order integer not null default 0,
|
|
created_by uuid references auth.users(id),
|
|
updated_by uuid references auth.users(id),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists tablero_cdc_project_pricing_items_project_id_idx
|
|
on public.tablero_cdc_project_pricing_items(project_id);
|
|
|
|
create index if not exists tablero_cdc_project_pricing_items_created_at_idx
|
|
on public.tablero_cdc_project_pricing_items(created_at desc);
|
|
|
|
alter table public.tablero_cdc_project_pricing_items enable row level security;
|
|
|
|
drop policy if exists "tablero_cdc_project_pricing_items_select_authenticated" on public.tablero_cdc_project_pricing_items;
|
|
drop policy if exists "tablero_cdc_project_pricing_items_insert_authenticated" on public.tablero_cdc_project_pricing_items;
|
|
drop policy if exists "tablero_cdc_project_pricing_items_update_authenticated" on public.tablero_cdc_project_pricing_items;
|
|
drop policy if exists "tablero_cdc_project_pricing_items_delete_authenticated" on public.tablero_cdc_project_pricing_items;
|
|
|
|
create policy "tablero_cdc_project_pricing_items_select_authenticated"
|
|
on public.tablero_cdc_project_pricing_items
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
create policy "tablero_cdc_project_pricing_items_insert_authenticated"
|
|
on public.tablero_cdc_project_pricing_items
|
|
for insert
|
|
to authenticated
|
|
with check (true);
|
|
|
|
create policy "tablero_cdc_project_pricing_items_update_authenticated"
|
|
on public.tablero_cdc_project_pricing_items
|
|
for update
|
|
to authenticated
|
|
using (true)
|
|
with check (true);
|
|
|
|
create policy "tablero_cdc_project_pricing_items_delete_authenticated"
|
|
on public.tablero_cdc_project_pricing_items
|
|
for delete
|
|
to authenticated
|
|
using (true);
|
|
|
|
create or replace function public.set_tablero_cdc_project_pricing_items_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
new.updated_at = now();
|
|
new.updated_by = auth.uid();
|
|
if tg_op = 'INSERT' then
|
|
new.created_by = coalesce(new.created_by, auth.uid());
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists trg_tablero_cdc_project_pricing_items_updated_at on public.tablero_cdc_project_pricing_items;
|
|
|
|
create trigger trg_tablero_cdc_project_pricing_items_updated_at
|
|
before insert or update on public.tablero_cdc_project_pricing_items
|
|
for each row
|
|
execute function public.set_tablero_cdc_project_pricing_items_updated_at();
|
|
|
|
select
|
|
'tablero_cdc_project_pricing_items lista' as status,
|
|
count(*) as registros_actuales
|
|
from public.tablero_cdc_project_pricing_items;
|