DO $$
BEGIN
  IF to_regprocedure('gen_random_uuid()') IS NULL THEN
    CREATE FUNCTION public.gen_random_uuid()
    RETURNS uuid
    LANGUAGE sql
    VOLATILE
    AS $fn$
      SELECT (
        substr(md5(random()::text || clock_timestamp()::text), 1, 8) || '-' ||
        substr(md5(random()::text || clock_timestamp()::text), 1, 4) || '-' ||
        '4' || substr(md5(random()::text || clock_timestamp()::text), 1, 3) || '-' ||
        substr('89ab', (floor(random() * 4)::int + 1), 1) || substr(md5(random()::text || clock_timestamp()::text), 1, 3) || '-' ||
        substr(md5(random()::text || clock_timestamp()::text), 1, 12)
      )::uuid
    $fn$;
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'part_type') THEN
    CREATE TYPE part_type AS ENUM ('base', 'partial', 'full');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_kind') THEN
    CREATE TYPE location_kind AS ENUM ('warehouse', 'truck', 'other');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'operation_type') THEN
    CREATE TYPE operation_type AS ENUM ('receive', 'transfer', 'assemble', 'disassemble', 'adjust', 'cycle_count');
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'operation_status') THEN
    CREATE TYPE operation_status AS ENUM ('pending', 'committed', 'conflict', 'void');
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  password_hash text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS invites (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  token_hash text NOT NULL,
  accepted_at timestamptz,
  expires_at timestamptz NOT NULL,
  created_by uuid REFERENCES users(id),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS 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,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS locations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  kind location_kind NOT NULL DEFAULT 'other',
  priority integer NOT NULL DEFAULT 100,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS parts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  sku text UNIQUE,
  type part_type NOT NULL,
  average_cost numeric(14, 4) NOT NULL DEFAULT 0,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_parts_name ON parts USING gin (to_tsvector('simple', name));

CREATE TABLE IF NOT EXISTS part_aliases (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  part_id uuid NOT NULL REFERENCES parts(id) ON DELETE CASCADE,
  code text NOT NULL UNIQUE,
  label text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS bom_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  assembly_part_id uuid NOT NULL REFERENCES parts(id),
  name text NOT NULL DEFAULT 'Default',
  version integer NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (assembly_part_id, version)
);

CREATE TABLE IF NOT EXISTS bom_lines (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  bom_version_id uuid NOT NULL REFERENCES bom_versions(id) ON DELETE CASCADE,
  component_part_id uuid NOT NULL REFERENCES parts(id),
  quantity_required numeric(14, 4) NOT NULL CHECK (quantity_required > 0),
  UNIQUE (bom_version_id, component_part_id)
);

CREATE TABLE IF NOT EXISTS inventory_balances (
  part_id uuid NOT NULL REFERENCES parts(id),
  location_id uuid NOT NULL REFERENCES locations(id),
  quantity numeric(14, 4) NOT NULL DEFAULT 0 CHECK (quantity >= 0),
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (part_id, location_id)
);

CREATE TABLE IF NOT EXISTS stock_thresholds (
  part_id uuid NOT NULL REFERENCES parts(id),
  location_id uuid NOT NULL REFERENCES locations(id),
  min_quantity numeric(14, 4) NOT NULL DEFAULT 0 CHECK (min_quantity >= 0),
  reorder_quantity numeric(14, 4) NOT NULL DEFAULT 0 CHECK (reorder_quantity >= 0),
  PRIMARY KEY (part_id, location_id)
);

CREATE TABLE IF NOT EXISTS inventory_lots (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  part_id uuid NOT NULL REFERENCES parts(id),
  location_id uuid NOT NULL REFERENCES locations(id),
  received_quantity numeric(14, 4) NOT NULL CHECK (received_quantity > 0),
  remaining_quantity numeric(14, 4) NOT NULL CHECK (remaining_quantity >= 0),
  unit_cost numeric(14, 4) NOT NULL CHECK (unit_cost >= 0),
  vendor_name text,
  reference text,
  received_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS inventory_operations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  idempotency_key text NOT NULL UNIQUE,
  type operation_type NOT NULL,
  status operation_status NOT NULL DEFAULT 'committed',
  user_id uuid REFERENCES users(id),
  note text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS inventory_ledger (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  operation_id uuid NOT NULL REFERENCES inventory_operations(id) ON DELETE CASCADE,
  part_id uuid NOT NULL REFERENCES parts(id),
  location_id uuid NOT NULL REFERENCES locations(id),
  quantity_delta numeric(14, 4) NOT NULL,
  unit_cost_snapshot numeric(14, 4) NOT NULL DEFAULT 0,
  action operation_type NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_inventory_ledger_created ON inventory_ledger (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_inventory_ledger_part ON inventory_ledger (part_id, created_at DESC);

CREATE TABLE IF NOT EXISTS integration_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  provider text NOT NULL,
  job_type text NOT NULL,
  status text NOT NULL DEFAULT 'queued',
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  error text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS sync_events (
  id bigserial PRIMARY KEY,
  topic text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS schema_migrations (
  id text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now()
);