BEGIN;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE SEQUENCE IF NOT EXISTS customer_code_seq START 1;
CREATE SEQUENCE IF NOT EXISTS ticket_number_seq START 1;

CREATE OR REPLACE FUNCTION colorjet_touch_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  NEW.version = COALESCE(OLD.version, 0) + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  employee_code text UNIQUE,
  name text NOT NULL,
  email text UNIQUE NOT NULL,
  phone text UNIQUE,
  password_hash text NOT NULL,
  role text NOT NULL CHECK (role IN ('owner','admin','manager','accounts','sales','office_staff','store','service_manager','engineer')),
  department text,
  is_active boolean NOT NULL DEFAULT true,
  force_password_change boolean NOT NULL DEFAULT true,
  token_version integer NOT NULL DEFAULT 0,
  last_login_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS roles (
  code text PRIMARY KEY,
  label text NOT NULL,
  description text NOT NULL,
  is_system boolean NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS role_permissions (
  role_code text NOT NULL REFERENCES roles(code) ON DELETE CASCADE,
  permission_code text NOT NULL,
  PRIMARY KEY (role_code, permission_code)
);

CREATE TABLE IF NOT EXISTS user_permission_overrides (
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  permission_code text NOT NULL,
  allowed boolean NOT NULL,
  PRIMARY KEY (user_id, permission_code)
);

CREATE TABLE IF NOT EXISTS login_audit (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  identifier text,
  success boolean NOT NULL,
  reason text,
  ip_address inet,
  user_agent text,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS refresh_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  expires_at timestamptz NOT NULL,
  revoked_at timestamptz,
  device_name text,
  ip_address inet,
  user_agent text,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  expires_at timestamptz NOT NULL,
  used_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS customers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  customer_code text UNIQUE,
  name text NOT NULL,
  company_name text,
  contact_person text,
  phone text,
  email text,
  address text,
  city text,
  district text,
  customer_type text,
  segment text,
  credit_status text,
  collection_priority text,
  due_balance numeric(18,2) NOT NULL DEFAULT 0,
  overdue_balance numeric(18,2) NOT NULL DEFAULT 0,
  action_note text,
  follow_up_at timestamptz,
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','inactive')),
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_customers_name ON customers USING gin (to_tsvector('simple', coalesce(name,'') || ' ' || coalesce(company_name,'')));
CREATE INDEX IF NOT EXISTS idx_customers_phone ON customers(phone);

CREATE TABLE IF NOT EXISTS machines (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  customer_id uuid NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  brand text,
  model text,
  serial_number text UNIQUE,
  installation_date date,
  warranty_start date,
  warranty_end date,
  warranty_status text,
  status text NOT NULL DEFAULT 'active',
  notes text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS engineers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  user_id uuid UNIQUE REFERENCES users(id) ON DELETE SET NULL,
  employee_code text UNIQUE,
  name text NOT NULL,
  phone text,
  territory text,
  status text NOT NULL DEFAULT 'available' CHECK (status IN ('available','assigned','on_job','leave','inactive')),
  completed_jobs integer NOT NULL DEFAULT 0,
  pending_jobs integer NOT NULL DEFAULT 0,
  average_hours numeric(8,2) NOT NULL DEFAULT 0,
  rating numeric(4,2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS product_categories (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  parent_id uuid REFERENCES product_categories(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS warehouses (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  address text,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  sku text UNIQUE NOT NULL,
  name text NOT NULL,
  category_id uuid REFERENCES product_categories(id) ON DELETE SET NULL,
  category_name text,
  item_type text,
  unit text NOT NULL DEFAULT 'pcs',
  tracking text NOT NULL DEFAULT 'none',
  reorder_level numeric(18,3) NOT NULL DEFAULT 0,
  current_stock numeric(18,3) NOT NULL DEFAULT 0,
  purchase_cost numeric(18,2) NOT NULL DEFAULT 0,
  selling_price numeric(18,2) NOT NULL DEFAULT 0,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);

CREATE TABLE IF NOT EXISTS stock_movements (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  product_id uuid NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  warehouse_id uuid REFERENCES warehouses(id) ON DELETE SET NULL,
  movement_type text NOT NULL CHECK (movement_type IN ('in','out','adjustment_in','adjustment_out','service_issue','sale_out','return_in')),
  reference_type text,
  reference_id text,
  lot_number text,
  supplier_name text,
  order_reference text,
  lc_number text,
  quantity_in numeric(18,3) NOT NULL DEFAULT 0,
  quantity_out numeric(18,3) NOT NULL DEFAULT 0,
  balance_after numeric(18,3),
  unit_cost numeric(18,2) NOT NULL DEFAULT 0,
  total_value numeric(18,2) NOT NULL DEFAULT 0,
  movement_date timestamptz NOT NULL DEFAULT NOW(),
  note text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_stock_movements_product_date ON stock_movements(product_id, movement_date DESC);

CREATE TABLE IF NOT EXISTS service_tickets (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  ticket_number text UNIQUE NOT NULL,
  customer_id uuid NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  machine_id uuid REFERENCES machines(id) ON DELETE SET NULL,
  assigned_engineer_id uuid REFERENCES engineers(id) ON DELETE SET NULL,
  priority text NOT NULL DEFAULT 'medium' CHECK (priority IN ('low','medium','high','critical','emergency')),
  status text NOT NULL DEFAULT 'new' CHECK (status IN ('new','assigned','accepted','in_progress','paused','completed','cancelled','revisit_required')),
  warranty_status text,
  chargeable boolean NOT NULL DEFAULT false,
  title text NOT NULL,
  description text,
  problem_category text,
  planned_at timestamptz,
  started_at timestamptz,
  completed_at timestamptz,
  customer_address text,
  location_lat numeric(10,7),
  location_lng numeric(10,7),
  work_notes text,
  diagnosis text,
  work_done text,
  pending_issue text,
  revisit_required boolean NOT NULL DEFAULT false,
  service_charge numeric(18,2) NOT NULL DEFAULT 0,
  collected_amount numeric(18,2) NOT NULL DEFAULT 0,
  payment_method text,
  customer_signature text,
  customer_signer_name text,
  customer_rating integer CHECK (customer_rating BETWEEN 1 AND 5),
  customer_feedback text,
  sync_state text NOT NULL DEFAULT 'synced',
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  updated_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_tickets_engineer_status ON service_tickets(assigned_engineer_id, status, planned_at);
CREATE INDEX IF NOT EXISTS idx_tickets_customer ON service_tickets(customer_id);

CREATE TABLE IF NOT EXISTS service_ticket_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id uuid NOT NULL REFERENCES service_tickets(id) ON DELETE CASCADE,
  event_type text NOT NULL,
  message text,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  actor_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS ticket_parts_used (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id uuid NOT NULL REFERENCES service_tickets(id) ON DELETE CASCADE,
  product_id uuid NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity numeric(18,3) NOT NULL CHECK (quantity > 0),
  billing_type text NOT NULL DEFAULT 'warranty' CHECK (billing_type IN ('warranty','free','chargeable')),
  unit_price numeric(18,2) NOT NULL DEFAULT 0,
  note text,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS engineer_schedules (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  engineer_id uuid NOT NULL REFERENCES engineers(id) ON DELETE RESTRICT,
  ticket_id uuid REFERENCES service_tickets(id) ON DELETE SET NULL,
  scheduled_start timestamptz NOT NULL,
  scheduled_end timestamptz,
  status text NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled','confirmed','in_progress','completed','cancelled')),
  note text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS engineer_location_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  engineer_id uuid NOT NULL REFERENCES engineers(id) ON DELETE CASCADE,
  ticket_id uuid REFERENCES service_tickets(id) ON DELETE SET NULL,
  latitude numeric(10,7) NOT NULL,
  longitude numeric(10,7) NOT NULL,
  accuracy_m numeric(12,2),
  recorded_at timestamptz NOT NULL DEFAULT NOW(),
  created_by uuid REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS uploaded_files (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id uuid REFERENCES service_tickets(id) ON DELETE CASCADE,
  original_name text NOT NULL,
  storage_path text NOT NULL UNIQUE,
  mime_type text NOT NULL,
  size_bytes bigint NOT NULL,
  file_type text NOT NULL DEFAULT 'other',
  uploaded_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS suppliers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  name text NOT NULL,
  contact_person text,
  phone text,
  email text,
  address text,
  country text,
  currency text DEFAULT 'BDT',
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS import_orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  order_number text UNIQUE NOT NULL,
  supplier_id uuid REFERENCES suppliers(id) ON DELETE SET NULL,
  supplier_name text,
  status text,
  currency text,
  total_value numeric(18,2) NOT NULL DEFAULT 0,
  order_date date,
  eta date,
  notes text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS lc_payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  import_order_id uuid REFERENCES import_orders(id) ON DELETE SET NULL,
  supplier_id uuid REFERENCES suppliers(id) ON DELETE SET NULL,
  lc_number text,
  payment_type text,
  due_date date,
  paid_date date,
  currency text,
  amount_foreign numeric(18,2) NOT NULL DEFAULT 0,
  exchange_rate numeric(18,4) NOT NULL DEFAULT 0,
  amount_bdt numeric(18,2) NOT NULL DEFAULT 0,
  payment_method text,
  bank_reference text,
  status text,
  notes text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS shipments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  import_order_id uuid REFERENCES import_orders(id) ON DELETE SET NULL,
  lc_number text,
  container_number text,
  bl_awb text,
  etd date,
  eta date,
  status text,
  notes text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS landed_cost_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  import_order_id uuid REFERENCES import_orders(id) ON DELETE SET NULL,
  supplier_id uuid REFERENCES suppliers(id) ON DELETE SET NULL,
  sku text,
  product_model text,
  category text,
  quantity numeric(18,3) NOT NULL DEFAULT 0,
  unit_usd numeric(18,4) NOT NULL DEFAULT 0,
  usd_rate numeric(18,4) NOT NULL DEFAULT 0,
  allocation_method text,
  shipping_allocated numeric(18,2) NOT NULL DEFAULT 0,
  customs_duty numeric(18,2) NOT NULL DEFAULT 0,
  cnf_allocated numeric(18,2) NOT NULL DEFAULT 0,
  transport_allocated numeric(18,2) NOT NULL DEFAULT 0,
  labour_allocated numeric(18,2) NOT NULL DEFAULT 0,
  warehouse_allocated numeric(18,2) NOT NULL DEFAULT 0,
  bank_charge_allocated numeric(18,2) NOT NULL DEFAULT 0,
  manual_add_cost numeric(18,2) NOT NULL DEFAULT 0,
  profit_pct numeric(8,2) NOT NULL DEFAULT 0,
  status text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS cash_bank_accounts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  name text NOT NULL,
  account_type text NOT NULL CHECK (account_type IN ('cash','bank','mfs')),
  opening_balance numeric(18,2) NOT NULL DEFAULT 0,
  current_balance numeric(18,2) NOT NULL DEFAULT 0,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS journal_entries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  entry_date date NOT NULL DEFAULT CURRENT_DATE,
  entry_type text NOT NULL,
  party_type text,
  customer_id uuid REFERENCES customers(id) ON DELETE SET NULL,
  supplier_id uuid REFERENCES suppliers(id) ON DELETE SET NULL,
  account_id uuid REFERENCES cash_bank_accounts(id) ON DELETE SET NULL,
  debit numeric(18,2) NOT NULL DEFAULT 0,
  credit numeric(18,2) NOT NULL DEFAULT 0,
  reference text,
  description text,
  status text NOT NULL DEFAULT 'posted',
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  invoice_number text UNIQUE NOT NULL,
  customer_id uuid REFERENCES customers(id) ON DELETE SET NULL,
  invoice_date date,
  due_date date,
  invoice_type text,
  total_amount numeric(18,2) NOT NULL DEFAULT 0,
  paid_amount numeric(18,2) NOT NULL DEFAULT 0,
  due_amount numeric(18,2) NOT NULL DEFAULT 0,
  status text,
  note text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS agreements (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  agreement_number text UNIQUE NOT NULL,
  customer_id uuid REFERENCES customers(id) ON DELETE SET NULL,
  machine_id uuid REFERENCES machines(id) ON DELETE SET NULL,
  agreement_type text,
  agreement_date date,
  price numeric(18,2) NOT NULL DEFAULT 0,
  down_payment numeric(18,2) NOT NULL DEFAULT 0,
  installment_months integer NOT NULL DEFAULT 0,
  status text,
  terms text,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW(),
  version integer NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id) ON DELETE CASCADE,
  title text NOT NULL,
  body text NOT NULL,
  event_type text,
  entity_type text,
  entity_id text,
  is_read boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS activity_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legacy_id text UNIQUE,
  actor_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  event_type text NOT NULL,
  entity_type text,
  entity_id text,
  message text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sync_events (
  sequence bigserial PRIMARY KEY,
  id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  client_action_id text UNIQUE,
  event_type text NOT NULL,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  actor_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sync_events_sequence ON sync_events(sequence);

CREATE TABLE IF NOT EXISTS sync_conflicts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_action_id text NOT NULL,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  server_payload jsonb NOT NULL,
  client_payload jsonb NOT NULL,
  status text NOT NULL DEFAULT 'open' CHECK (status IN ('open','resolved','discarded')),
  resolved_by uuid REFERENCES users(id) ON DELETE SET NULL,
  resolved_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS import_batches (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_name text NOT NULL,
  source_sha256 text NOT NULL,
  status text NOT NULL CHECK (status IN ('preview','running','completed','failed','rolled_back')),
  started_at timestamptz NOT NULL DEFAULT NOW(),
  completed_at timestamptz,
  created_by uuid REFERENCES users(id) ON DELETE SET NULL,
  summary jsonb NOT NULL DEFAULT '{}'::jsonb,
  error_message text
);

CREATE TABLE IF NOT EXISTS import_audit (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  batch_id uuid NOT NULL REFERENCES import_batches(id) ON DELETE CASCADE,
  entity_type text NOT NULL,
  legacy_id text,
  action text NOT NULL CHECK (action IN ('inserted','updated','skipped','warning','error')),
  message text,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION colorjet_apply_product_stock(
  p_product_id uuid,
  p_quantity_in numeric,
  p_quantity_out numeric
) RETURNS numeric AS $$
DECLARE
  next_qty numeric;
BEGIN
  UPDATE products
     SET current_stock = current_stock + COALESCE(p_quantity_in,0) - COALESCE(p_quantity_out,0)
   WHERE id = p_product_id
   RETURNING current_stock INTO next_qty;
  IF next_qty IS NULL THEN
    RAISE EXCEPTION 'Product % does not exist', p_product_id;
  END IF;
  RETURN next_qty;
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['users','customers','machines','engineers','products','service_tickets','engineer_schedules','suppliers','import_orders','lc_payments','shipments','landed_cost_items','cash_bank_accounts','invoices','agreements']
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS trg_%I_updated_at ON %I', t, t);
    EXECUTE format('CREATE TRIGGER trg_%I_updated_at BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION colorjet_touch_updated_at()', t, t);
  END LOOP;
END $$;

COMMIT;