-- UDS Zielarchitektur fuer PostgreSQL (Entwurf)
-- Diese Datei ist noch kein Installationsskript, sondern die erste fachliche Zielstruktur.

CREATE SCHEMA IF NOT EXISTS core;
CREATE SCHEMA IF NOT EXISTS identity;
CREATE SCHEMA IF NOT EXISTS crm;
CREATE SCHEMA IF NOT EXISTS vehicle;
CREATE SCHEMA IF NOT EXISTS workshop;
CREATE SCHEMA IF NOT EXISTS migration;
CREATE SCHEMA IF NOT EXISTS language;

CREATE TABLE IF NOT EXISTS core.tenant (
  tenant_id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  client_code TEXT NOT NULL UNIQUE,
  default_locale TEXT NOT NULL DEFAULT 'de-DE',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS core.branch (
  branch_id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES core.tenant(tenant_id),
  name TEXT NOT NULL,
  branch_code TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS identity.employee (
  employee_id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES core.tenant(tenant_id),
  branch_id UUID REFERENCES core.branch(branch_id),
  personnel_no TEXT,
  display_name TEXT NOT NULL,
  department TEXT,
  employment_status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS identity.user_account (
  user_id UUID PRIMARY KEY,
  employee_id UUID REFERENCES identity.employee(employee_id),
  username TEXT NOT NULL UNIQUE,
  password_hash TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS crm.customer (
  customer_id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES core.tenant(tenant_id),
  customer_no TEXT,
  display_name TEXT NOT NULL,
  source TEXT NOT NULL DEFAULT 'uds',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS vehicle.vehicle (
  vehicle_id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES core.tenant(tenant_id),
  customer_id UUID REFERENCES crm.customer(customer_id),
  vin TEXT,
  license_plate TEXT,
  make TEXT,
  model TEXT,
  source TEXT NOT NULL DEFAULT 'uds',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS migration.legacy_mapping (
  mapping_id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES core.tenant(tenant_id),
  source_system TEXT NOT NULL,
  source_table TEXT NOT NULL,
  source_id TEXT NOT NULL,
  target_schema TEXT NOT NULL,
  target_table TEXT NOT NULL,
  target_id UUID NOT NULL,
  migrated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, source_system, source_table, source_id)
);

CREATE TABLE IF NOT EXISTS language.translation (
  translation_id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES core.tenant(tenant_id),
  locale TEXT NOT NULL,
  namespace TEXT NOT NULL,
  translation_key TEXT NOT NULL,
  translation_value TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, locale, namespace, translation_key)
);
