117 lines
4.6 KiB
SQL
117 lines
4.6 KiB
SQL
-- =====================================================================
|
|
-- Local extension migration: simple employee payment ledger used by the
|
|
-- Employee Payment Report UI. Backed by the API; not a Supabase migration.
|
|
-- =====================================================================
|
|
|
|
create table if not exists app.employees (
|
|
id uuid primary key default gen_random_uuid(),
|
|
emp_id text not null unique,
|
|
name text not null,
|
|
email text,
|
|
department text,
|
|
location text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists app.employee_transactions (
|
|
id uuid primary key default gen_random_uuid(),
|
|
employee_id uuid not null references app.employees(id) on delete cascade,
|
|
transaction_date date not null,
|
|
collection_amount numeric(18,2) not null default 0,
|
|
deposit_amount numeric(18,2) not null default 0,
|
|
currency text not null check (currency in ('USD','LBP')),
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists idx_emp_tx_emp on app.employee_transactions(employee_id, transaction_date desc);
|
|
|
|
-- These tables are owned by the API; RLS off, gated at the HTTP layer.
|
|
alter table app.employees disable row level security;
|
|
alter table app.employee_transactions disable row level security;
|
|
grant select, insert, update, delete on app.employees to authenticated;
|
|
grant select, insert, update, delete on app.employee_transactions to authenticated;
|
|
|
|
-- Report-ready bridge from the modern POS/shift ledger into the legacy
|
|
-- employee payment report shape. A negative closed-shift variance means the
|
|
-- cashier is short, so it increases outstanding collection. A positive
|
|
-- variance means the drawer is over, so it is treated as a deposit/credit.
|
|
create or replace view app.v_employee_outstanding_balances as
|
|
with manual as (
|
|
select
|
|
e.id as employee_id,
|
|
e.emp_id,
|
|
e.name,
|
|
e.email,
|
|
e.department,
|
|
e.location,
|
|
et.currency,
|
|
sum(et.collection_amount) as manual_collection,
|
|
sum(et.deposit_amount) as manual_deposit,
|
|
0::numeric as shift_shortage,
|
|
0::numeric as shift_overage,
|
|
max(et.transaction_date)::timestamptz as last_activity_at
|
|
from app.employees e
|
|
join app.employee_transactions et on et.employee_id = e.id
|
|
group by e.id, e.emp_id, e.name, e.email, e.department, e.location, et.currency
|
|
), shift_variance as (
|
|
select
|
|
e.id as employee_id,
|
|
coalesce(e.emp_id, 'AUTH-' || left(u.id::text, 8)) as emp_id,
|
|
coalesce(e.name, p.full_name, u.full_name, u.email) as name,
|
|
u.email,
|
|
e.department,
|
|
e.location,
|
|
currency_rows.currency,
|
|
0::numeric as manual_collection,
|
|
0::numeric as manual_deposit,
|
|
sum(greatest(-currency_rows.variance_amount, 0)) as shift_shortage,
|
|
sum(greatest(currency_rows.variance_amount, 0)) as shift_overage,
|
|
max(sh.closed_at) as last_activity_at
|
|
from app.shifts sh
|
|
join auth.users u on u.id = sh.user_id
|
|
left join app.user_profiles p on p.user_id = u.id
|
|
left join app.employees e on lower(e.email) = lower(u.email)
|
|
cross join lateral (values
|
|
('USD'::text, coalesce(sh.variance_usd, 0)::numeric),
|
|
('LBP'::text, coalesce(sh.variance_lbp, 0)::numeric)
|
|
) as currency_rows(currency, variance_amount)
|
|
where sh.status = 'closed'
|
|
and currency_rows.variance_amount <> 0
|
|
group by e.id, coalesce(e.emp_id, 'AUTH-' || left(u.id::text, 8)),
|
|
coalesce(e.name, p.full_name, u.full_name, u.email), u.email,
|
|
e.department, e.location, currency_rows.currency
|
|
), combined as (
|
|
select * from manual
|
|
union all
|
|
select * from shift_variance
|
|
)
|
|
select
|
|
coalesce(
|
|
employee_id,
|
|
(
|
|
substr(md5(coalesce(email, emp_id)), 1, 8) || '-' ||
|
|
substr(md5(coalesce(email, emp_id)), 9, 4) || '-' ||
|
|
substr(md5(coalesce(email, emp_id)), 13, 4) || '-' ||
|
|
substr(md5(coalesce(email, emp_id)), 17, 4) || '-' ||
|
|
substr(md5(coalesce(email, emp_id)), 21, 12)
|
|
)::uuid
|
|
) as employee_id,
|
|
emp_id,
|
|
name,
|
|
email,
|
|
department,
|
|
location,
|
|
currency,
|
|
sum(manual_collection) as manual_collection,
|
|
sum(manual_deposit) as manual_deposit,
|
|
sum(shift_shortage) as shift_shortage,
|
|
sum(shift_overage) as shift_overage,
|
|
sum(manual_collection + shift_shortage) as total_collection,
|
|
sum(manual_deposit + shift_overage) as total_deposit,
|
|
sum(manual_collection + shift_shortage - manual_deposit - shift_overage) as outstanding_amount,
|
|
max(last_activity_at) as last_activity_at
|
|
from combined
|
|
group by employee_id, emp_id, name, email, department, location, currency;
|
|
|
|
grant select on app.v_employee_outstanding_balances to authenticated;
|