-- Sign CRM — PostgreSQL schema
-- Card: https://github.com/MarquettiSolutions/sign-crm/issues/5
--
-- Design rules (see docs/plano-migracao-backend.md):
--   1. Every table name and column name is in English.
--   2. No Postgres ENUM types and no hardcoded CHECK-constrained value lists.
--      Anything that used to be a fixed string set (status, priority, role,
--      currency, ...) is a lookup table instead: {id, code, label, sort_order,
--      is_active}. Adding a new value is an INSERT, never a migration.
--   3. Every tenant-owned table carries workspace_id — the backend must
--      filter every query by it (this is what replaces the open Firestore
--      rules from the old system).
--   4. Money columns are numeric(12,2) and always paired with a currency_id,
--      even though today only USD is seeded — adding a currency is an
--      INSERT into `currencies`, never a schema change.
--
-- Requires PostgreSQL 13+ (core gen_random_uuid()).

-- =========================================================================
-- LOOKUP / CATALOG TABLES
-- Same shape everywhere on purpose: id, code (stable machine key used by
-- the backend and by the frontend i18n layer), label (English reference
-- label — actual display translation happens in the frontend), sort_order,
-- is_active. All are global (shared vocabulary across every workspace).
-- =========================================================================

create table account_statuses (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: pending, active, blocked

create table roles (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: owner, admin, employee, readonly

create table modules (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: clients, jobs, expenses, inventory, providers, purchases, users,
--       dashboard, reports, production, advance_control, backup

create table permission_levels (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: none, view, edit, delete, manage

create table currencies (
  id smallint primary key generated always as identity,
  code text not null unique,       -- ISO 4217, e.g. 'USD'
  symbol text not null,            -- e.g. '$'
  name text not null,
  is_active boolean not null default true
);
-- seed: USD

create table job_statuses (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0,
  is_terminal boolean not null default false
);
-- seed: quote, approved, design, production, installation, delivered, paid, cancelled

create table job_priorities (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: low, medium, high

create table installation_statuses (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: pending, confirmed, en_route, rescheduled, completed, cancelled

create table estimate_statuses (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: draft, sent, approved, changes_requested, rejected

create table design_statuses (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: pending, sent, approved, changes_requested

create table price_modes (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null
);
-- seed: manual, quote

create table discount_types (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null
);
-- seed: percent, fixed

create table production_stages (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: design, approval, production, ready_to_install, installation_scheduled, completed

create table advance_entry_types (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null
);
-- seed: material, purchase, labor, expense, installation, other

create table advance_entry_statuses (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: pending, purchased, paid

create table inventory_movement_types (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null
);
-- seed: in, out, adjustment

create table purchase_order_statuses (
  id smallint primary key generated always as identity,
  code text not null unique,
  label text not null,
  sort_order smallint not null default 0
);
-- seed: draft, sent, partial, received, cancelled

-- =========================================================================
-- IDENTITY & TENANCY
-- Replaces the Firestore platformUsers / platformWorkspaces / teamMembers /
-- teamAccess split (4 collections, 3 of them duplicating the same
-- membership data) with 4 clean, non-duplicated tables.
-- =========================================================================

create table users (
  id uuid primary key default gen_random_uuid(),  -- = Keycloak subject (sub)
  email text not null unique,  -- always stored lowercase, normalized by the app before insert
  name text not null,
  is_superadmin boolean not null default false,   -- platform-wide flag, not a workspace role
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  last_login_at timestamptz,
  last_seen_at timestamptz
);

create table workspaces (
  id uuid primary key default gen_random_uuid(),
  owner_user_id uuid not null references users(id),
  company_name text not null,
  plan text not null default 'starter',
  status_id smallint not null references account_statuses(id),
  default_currency_id smallint not null references currencies(id),
  requested_at timestamptz not null default now(),
  approved_at timestamptz,
  blocked_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  last_login_at timestamptz,
  last_seen_at timestamptz
);

create table workspace_members (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  user_id uuid not null references users(id),
  role_id smallint not null references roles(id),
  status_id smallint not null references account_statuses(id),
  invited boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  last_login_at timestamptz,
  unique (workspace_id, user_id)
);

-- Per-module permission, one row per (member, module). Adding a new module
-- to the product is an INSERT into `modules`, never an ALTER TABLE — this
-- is what replaces the flat moduleClientes/moduleTrabajos/... boolean
-- columns from the old teamMembers Firestore doc.
create table workspace_member_permissions (
  id uuid primary key default gen_random_uuid(),
  workspace_member_id uuid not null references workspace_members(id) on delete cascade,
  module_id smallint not null references modules(id),
  permission_level_id smallint not null references permission_levels(id),
  unique (workspace_member_id, module_id)
);

-- =========================================================================
-- CLIENTS
-- =========================================================================

create table clients (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  name text not null,
  company text,
  phone text,
  email text,
  address text,
  city text,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index on clients (workspace_id);

-- =========================================================================
-- PROVIDERS & PURCHASING
-- =========================================================================

create table providers (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  name text not null,
  contact text,
  phone text,
  email text,
  address text,
  city text,
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index on providers (workspace_id);

-- =========================================================================
-- INVENTORY
-- (declared before jobs/purchase_orders because both reference it)
-- =========================================================================

create table inventory_items (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  name text not null,
  sku text,
  category text,
  unit text not null,
  stock_qty numeric(12,2) not null default 0,
  min_stock_qty numeric(12,2) not null default 0,
  unit_cost numeric(12,2) not null default 0,
  currency_id smallint not null references currencies(id),
  location text,
  provider_id uuid references providers(id),  -- real FK: fixes the free-text
                                                -- `supplier` name-matching in
                                                -- the old Firestore model
  notes text,
  last_movement_at date,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index on inventory_items (workspace_id);

-- =========================================================================
-- JOBS (aggregate root — quotes / estimates / production / installation /
-- client approval / advance control all lived on the single Firestore job
-- doc; kept as one wide table here for the same reason: it's always read
-- and written as one unit. Only genuine 1:many arrays became child tables.)
-- =========================================================================

create table jobs (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  client_id uuid not null references clients(id),

  -- core
  title text not null,
  status_id smallint not null references job_statuses(id),
  order_date date not null default current_date,
  due_date date,
  priority_id smallint not null references job_priorities(id),
  job_type text,
  description text,
  notes text,

  -- pricing
  sale_amount numeric(12,2) not null default 0,
  currency_id smallint not null references currencies(id),
  price_mode_id smallint not null references price_modes(id),
  labor_cost numeric(12,2) not null default 0,
  extra_cost numeric(12,2) not null default 0,
  desired_margin_percent numeric(5,2) not null default 0,
  discount_type_id smallint references discount_types(id),
  discount_value numeric(12,2) not null default 0,
  tax_percent numeric(5,2) not null default 0,

  -- estimator tool snapshot (optional — only set when built from a template)
  estimator_template_key text,
  estimator_width numeric(12,2),
  estimator_height numeric(12,2),
  estimator_qty numeric(12,2),
  estimator_waste_percent numeric(5,2),
  estimator_material_rate numeric(12,2),
  estimator_sale_rate numeric(12,2),
  estimator_labor_base numeric(12,2),

  -- installation
  installation_date date,
  installation_start_time time,
  installation_end_time time,
  installation_assigned_to text,
  installation_crew text,
  installation_status_id smallint references installation_statuses(id),
  installation_address text,
  installation_window text,
  installation_notes text,

  -- client approval (public approval page)
  -- `approval_token` replaces the old model where the entire payload
  -- (amounts, items, images) was passed as URL query params with no
  -- server-side check. The public endpoint now looks this up server-side.
  approval_token text unique,
  estimate_status_id smallint references estimate_statuses(id),
  design_status_id smallint references design_statuses(id),
  deposit_amount numeric(12,2) not null default 0,
  estimate_sent_at timestamptz,
  client_approved_at timestamptz,
  client_approved_by text,
  client_visible_notes text,
  client_response_notes text,
  approval_link text,
  approval_link_expires_at timestamptz,

  -- inventory auto-deduction tracking
  inventory_applied boolean not null default false,
  inventory_applied_at timestamptz,
  inventory_applied_status_id smallint references job_statuses(id),
  inventory_applied_source text,

  -- production board
  production_stage_id smallint references production_stages(id),
  production_priority_id smallint references job_priorities(id),
  production_responsible text,
  production_blocked boolean not null default false,
  production_notes text,

  -- advance control (running total; ledger detail is job_advance_ledger)
  advance_received_amount numeric(12,2) not null default 0,

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index on jobs (workspace_id);
create index on jobs (workspace_id, status_id);
create index on jobs (client_id);

create table job_materials (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  inventory_item_id uuid references inventory_items(id),
  name text not null,
  sku text,
  unit text,
  supplier_text text,
  current_stock_snapshot numeric(12,2),
  qty numeric(12,2) not null,
  unit_price numeric(12,2) not null,
  currency_id smallint not null references currencies(id),
  total numeric(12,2) not null,
  created_at timestamptz not null default now()
);
create index on job_materials (job_id);

create table job_quote_items (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  description text not null,
  qty numeric(12,2) not null,
  unit_price numeric(12,2) not null,
  currency_id smallint not null references currencies(id),
  sort_order smallint not null default 0
);
create index on job_quote_items (job_id);

create table job_payments (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  amount numeric(12,2) not null,
  currency_id smallint not null references currencies(id),
  paid_at date not null,
  method text,
  note text,
  created_at timestamptz not null default now()
);
create index on job_payments (job_id);

create table job_images (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  url text not null,
  cloudinary_public_id text,
  file_name text,
  uploaded_at timestamptz not null default now()
);
create index on job_images (job_id);

create table job_internal_notes (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  text text not null,
  created_by_user_id uuid not null references users(id),
  created_at timestamptz not null default now()
);
create index on job_internal_notes (job_id);

create table job_activity_log (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  type text not null,
  text text not null,
  created_by_user_id uuid not null references users(id),
  created_at timestamptz not null default now()
);
create index on job_activity_log (job_id);

create table job_advance_ledger (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  entry_date date not null,
  type_id smallint not null references advance_entry_types(id),
  concept text not null,
  amount numeric(12,2) not null,
  currency_id smallint not null references currencies(id),
  status_id smallint not null references advance_entry_statuses(id),
  apply_to_advance boolean not null default false,
  created_at timestamptz not null default now()
);
create index on job_advance_ledger (job_id);

create table job_production_history (
  id uuid primary key default gen_random_uuid(),
  job_id uuid not null references jobs(id) on delete cascade,
  note text not null,
  created_by_user_id uuid not null references users(id),
  created_at timestamptz not null default now()
);
create index on job_production_history (job_id);

-- =========================================================================
-- INVENTORY MOVEMENTS
-- (declared after jobs because it optionally references jobs(id))
-- =========================================================================

create table inventory_movements (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  item_id uuid not null references inventory_items(id),
  type_id smallint not null references inventory_movement_types(id),
  qty_delta numeric(12,2) not null,
  before_stock_qty numeric(12,2) not null,
  after_stock_qty numeric(12,2) not null,
  movement_date date not null default current_date,
  reference text,
  note text,
  job_id uuid references jobs(id),
  unit_cost numeric(12,2),
  currency_id smallint references currencies(id),
  created_by_user_id uuid not null references users(id),
  created_at timestamptz not null default now()
);
create index on inventory_movements (workspace_id);
create index on inventory_movements (item_id);

-- =========================================================================
-- PURCHASE ORDERS
-- =========================================================================

create table purchase_orders (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  number text not null,
  provider_id uuid not null references providers(id),
  job_id uuid references jobs(id),
  client_id uuid references clients(id),
  order_date date not null default current_date,
  expected_date date,
  status_id smallint not null references purchase_order_statuses(id),
  notes text,
  total_amount numeric(12,2) not null default 0,
  currency_id smallint not null references currencies(id),
  received_movements_applied boolean not null default false,
  received_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (workspace_id, number)
);
create index on purchase_orders (workspace_id);

create table purchase_order_items (
  id uuid primary key default gen_random_uuid(),
  purchase_order_id uuid not null references purchase_orders(id) on delete cascade,
  inventory_item_id uuid references inventory_items(id),
  name text not null,
  sku text,
  unit text,
  qty numeric(12,2) not null,
  unit_cost numeric(12,2) not null,
  currency_id smallint not null references currencies(id),
  supplier_text text,
  total numeric(12,2) not null
);
create index on purchase_order_items (purchase_order_id);

-- =========================================================================
-- EXPENSES
-- =========================================================================

-- Workspace-scoped catalog (each company defines its own categories) —
-- kept as a real table instead of free text so reporting can group/roll up
-- reliably, while still being extensible with a plain INSERT.
create table expense_categories (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  name text not null,
  is_active boolean not null default true,
  unique (workspace_id, name)
);

create table recurring_expenses (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  concept text not null,
  category_id uuid references expense_categories(id),
  amount numeric(12,2) not null,
  currency_id smallint not null references currencies(id),
  day_of_month smallint not null check (day_of_month between 1 and 31),
  notes text,
  active boolean not null default true,
  last_generated_month date,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index on recurring_expenses (workspace_id);

create table expenses (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references workspaces(id) on delete cascade,
  job_id uuid references jobs(id),
  recurring_expense_id uuid references recurring_expenses(id),
  concept text not null,
  category_id uuid references expense_categories(id),
  amount numeric(12,2) not null,
  currency_id smallint not null references currencies(id),
  expense_date date not null default current_date,
  notes text,
  generated_month date,
  auto_generated boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index on expenses (workspace_id);
create index on expenses (job_id);

create table expense_photos (
  id uuid primary key default gen_random_uuid(),
  expense_id uuid not null references expenses(id) on delete cascade,
  url text not null,
  cloudinary_public_id text,
  file_name text,
  uploaded_at timestamptz not null default now()
);
create index on expense_photos (expense_id);

-- =========================================================================
-- SEED DATA — lookup tables only. Run once per environment.
-- =========================================================================

insert into currencies (code, symbol, name) values ('USD', '$', 'US Dollar');

insert into account_statuses (code, label, sort_order) values
  ('pending', 'Pending', 1), ('active', 'Active', 2), ('blocked', 'Blocked', 3);

insert into roles (code, label, sort_order) values
  ('owner', 'Owner', 1), ('admin', 'Admin', 2), ('employee', 'Employee', 3), ('readonly', 'Read only', 4);

insert into modules (code, label, sort_order) values
  ('clients', 'Clients', 1), ('jobs', 'Jobs', 2), ('expenses', 'Expenses', 3),
  ('inventory', 'Inventory', 4), ('providers', 'Providers', 5), ('purchases', 'Purchases', 6),
  ('users', 'Users', 7), ('dashboard', 'Dashboard', 8), ('reports', 'Reports', 9),
  ('production', 'Production', 10), ('advance_control', 'Advance control', 11), ('backup', 'Backup', 12);

insert into permission_levels (code, label, sort_order) values
  ('none', 'None', 1), ('view', 'View', 2), ('edit', 'Edit', 3), ('delete', 'Delete', 4), ('manage', 'Manage', 5);

insert into job_statuses (code, label, sort_order, is_terminal) values
  ('quote', 'Quote', 1, false), ('approved', 'Approved', 2, false), ('design', 'Design', 3, false),
  ('production', 'Production', 4, false), ('installation', 'Installation', 5, false),
  ('delivered', 'Delivered', 6, false), ('paid', 'Paid', 7, true), ('cancelled', 'Cancelled', 8, true);

insert into job_priorities (code, label, sort_order) values
  ('low', 'Low', 1), ('medium', 'Medium', 2), ('high', 'High', 3);

insert into installation_statuses (code, label, sort_order) values
  ('pending', 'Pending', 1), ('confirmed', 'Confirmed', 2), ('en_route', 'En route', 3),
  ('rescheduled', 'Rescheduled', 4), ('completed', 'Completed', 5), ('cancelled', 'Cancelled', 6);

insert into estimate_statuses (code, label, sort_order) values
  ('draft', 'Draft', 1), ('sent', 'Sent', 2), ('approved', 'Approved', 3),
  ('changes_requested', 'Changes requested', 4), ('rejected', 'Rejected', 5);

insert into design_statuses (code, label, sort_order) values
  ('pending', 'Pending', 1), ('sent', 'Sent', 2), ('approved', 'Approved', 3), ('changes_requested', 'Changes requested', 4);

insert into price_modes (code, label) values ('manual', 'Manual'), ('quote', 'Quote-based');

insert into discount_types (code, label) values ('percent', 'Percent'), ('fixed', 'Fixed amount');

insert into production_stages (code, label, sort_order) values
  ('design', 'Design', 1), ('approval', 'Approval', 2), ('production', 'Production', 3),
  ('ready_to_install', 'Ready to install', 4), ('installation_scheduled', 'Installation scheduled', 5),
  ('completed', 'Completed', 6);

insert into advance_entry_types (code, label) values
  ('material', 'Material'), ('purchase', 'Purchase'), ('labor', 'Labor'),
  ('expense', 'Expense'), ('installation', 'Installation'), ('other', 'Other');

insert into advance_entry_statuses (code, label, sort_order) values
  ('pending', 'Pending', 1), ('purchased', 'Purchased', 2), ('paid', 'Paid', 3);

insert into inventory_movement_types (code, label) values
  ('in', 'In'), ('out', 'Out'), ('adjustment', 'Adjustment');

insert into purchase_order_statuses (code, label, sort_order) values
  ('draft', 'Draft', 1), ('sent', 'Sent', 2), ('partial', 'Partial', 3),
  ('received', 'Received', 4), ('cancelled', 'Cancelled', 5);
