-- ERP Naurua - Phase 1 Draft Schema
-- Status: Draft (Intentional minimal constraints; to be hardened during implementation)
-- Target DB: PostgreSQL-compatible SQL
-- Scope: Lagerverwaltung (Chargen/MHD), Bestellerfassung, Kontaktangaben

-- NOTE
-- - Use this as a starting point for migrations, not as final truth.
-- - Add stricter constraints once business rules are confirmed.

CREATE TABLE party (
  id BIGSERIAL PRIMARY KEY,
  type TEXT NOT NULL DEFAULT 'customer', -- customer | supplier | both
  name TEXT,
  email TEXT,
  phone TEXT,
  phone_alt TEXT,
  tax_id TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_party_email ON party(email);

CREATE TABLE address (
  id BIGSERIAL PRIMARY KEY,
  party_id BIGINT NOT NULL REFERENCES party(id) ON DELETE CASCADE,
  type TEXT NOT NULL, -- billing | shipping
  first_name TEXT,
  last_name TEXT,
  company_name TEXT,
  street TEXT,
  house_number TEXT,
  zip TEXT,
  city TEXT,
  state_code TEXT,
  country_name TEXT,
  country_iso2 CHAR(2),
  raw_payload JSONB, -- preserves source formatting variants
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_address_party ON address(party_id);
CREATE INDEX idx_address_country_iso2 ON address(country_iso2);

CREATE TABLE contact (
  id BIGSERIAL PRIMARY KEY,
  party_id BIGINT NOT NULL REFERENCES party(id) ON DELETE CASCADE,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  phone TEXT,
  position_title TEXT,
  note TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_contact_party ON contact(party_id);

-- Lagergefuehrtes Produkt (Bestandsfuehrung, Charge, MHD)
CREATE TABLE product (
  id BIGSERIAL PRIMARY KEY,
  sku TEXT NOT NULL,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  uom TEXT NOT NULL DEFAULT 'unit',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX uq_product_sku ON product(sku);

-- Verkaufbarer Shop-Artikel (kann Bundle sein)
CREATE TABLE sellable_item (
  id BIGSERIAL PRIMARY KEY,
  item_code TEXT NOT NULL, -- interne stabile Kennung
  display_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX uq_sellable_item_code ON sellable_item(item_code);

-- Mapping externer Shop-Daten auf internen Artikel
CREATE TABLE external_item_alias (
  id BIGSERIAL PRIMARY KEY,
  source_system TEXT NOT NULL DEFAULT 'wix',
  external_article_number TEXT,
  external_title TEXT,
  title_normalized TEXT,
  sellable_item_id BIGINT NOT NULL REFERENCES sellable_item(id) ON DELETE CASCADE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ext_alias_article_number ON external_item_alias(external_article_number);
CREATE INDEX idx_ext_alias_title_norm ON external_item_alias(title_normalized);

-- Stueckliste: welcher Artikel enthaelt welche lagergefuehrten Produkte
CREATE TABLE sellable_item_component (
  id BIGSERIAL PRIMARY KEY,
  sellable_item_id BIGINT NOT NULL REFERENCES sellable_item(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES product(id) ON DELETE RESTRICT,
  qty_per_item NUMERIC(14, 4) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_component_qty_positive CHECK (qty_per_item > 0)
);

CREATE UNIQUE INDEX uq_item_component ON sellable_item_component(sellable_item_id, product_id);
CREATE INDEX idx_item_component_product ON sellable_item_component(product_id);

CREATE TABLE warehouse (
  id BIGSERIAL PRIMARY KEY,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX uq_warehouse_code ON warehouse(code);

CREATE TABLE location (
  id BIGSERIAL PRIMARY KEY,
  warehouse_id BIGINT NOT NULL REFERENCES warehouse(id) ON DELETE RESTRICT,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL, -- storage | receiving | dispatch | adjustment
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_location_warehouse ON location(warehouse_id);
CREATE UNIQUE INDEX uq_location_code_per_warehouse ON location(warehouse_id, code);

CREATE TABLE stock_lot (
  id BIGSERIAL PRIMARY KEY,
  product_id BIGINT NOT NULL REFERENCES product(id) ON DELETE RESTRICT,
  lot_number TEXT,
  mfg_date DATE,
  expiry_date DATE,
  status TEXT NOT NULL DEFAULT 'open', -- open | current | closed
  sellout_date DATE,
  warning_state TEXT NOT NULL DEFAULT 'none', -- none | due_60d | due_now
  supplier_lot_number TEXT,
  supplier_name TEXT,
  purchase_date DATE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_stock_lot_status CHECK (status IN ('open', 'current', 'closed')),
  CONSTRAINT chk_stock_lot_warning_state CHECK (warning_state IN ('none', 'due_60d', 'due_now')),
  CONSTRAINT chk_stock_lot_number_required_for_non_open CHECK (
    status = 'open' OR lot_number IS NOT NULL
  )
);

CREATE INDEX idx_stock_lot_product ON stock_lot(product_id);
CREATE UNIQUE INDEX uq_stock_lot_product_number ON stock_lot(product_id, lot_number);
CREATE UNIQUE INDEX uq_stock_lot_one_current_per_product ON stock_lot(product_id) WHERE status = 'current';
CREATE UNIQUE INDEX uq_stock_lot_one_open_per_product ON stock_lot(product_id) WHERE status = 'open';

CREATE TABLE payment_method (
  id BIGSERIAL PRIMARY KEY,
  code TEXT NOT NULL,
  label TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX uq_payment_method_code ON payment_method(code);

CREATE TABLE shipping_method (
  id BIGSERIAL PRIMARY KEY,
  code TEXT NOT NULL,
  label TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX uq_shipping_method_code ON shipping_method(code);

CREATE TABLE sales_order (
  id BIGSERIAL PRIMARY KEY,
  external_ref TEXT NOT NULL, -- Wix BestellungNr oder ERP-Ref (DIR-...)
  order_source TEXT NOT NULL DEFAULT 'wix', -- wix | direct
  party_id BIGINT REFERENCES party(id) ON DELETE RESTRICT, -- bei direct optional
  order_date TIMESTAMP NOT NULL DEFAULT NOW(),
  order_status TEXT NOT NULL DEFAULT 'received', -- received | imported | fulfilled | cancelled
  payment_status TEXT NOT NULL DEFAULT 'paid', -- phase 1 fuehrt nur bezahlte Auftraege
  payment_method_id BIGINT REFERENCES payment_method(id) ON DELETE RESTRICT,
  shipping_method_id BIGINT REFERENCES shipping_method(id) ON DELETE RESTRICT,
  amount_net NUMERIC(14, 2),
  amount_shipping NUMERIC(14, 2),
  amount_tax NUMERIC(14, 2),
  amount_discount NUMERIC(14, 2),
  total_amount NUMERIC(14, 2),
  currency TEXT NOT NULL DEFAULT 'CHF',
  webhook_payload JSONB,
  imported_at TIMESTAMP NOT NULL DEFAULT NOW(),
  cancelled_at TIMESTAMP,
  cancelled_reason TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_sales_order_status CHECK (order_status IN ('received', 'imported', 'fulfilled', 'cancelled')),
  CONSTRAINT chk_sales_order_payment_status CHECK (payment_status IN ('paid')),
  CONSTRAINT chk_sales_order_source CHECK (order_source IN ('wix', 'direct')),
  CONSTRAINT chk_sales_order_direct_ref_prefix CHECK (
    order_source <> 'direct' OR external_ref LIKE 'DIR-%'
  )
);

CREATE UNIQUE INDEX uq_sales_order_external_ref ON sales_order(external_ref);
CREATE INDEX idx_sales_order_party ON sales_order(party_id);
CREATE INDEX idx_sales_order_source ON sales_order(order_source);

CREATE TABLE sales_order_line (
  id BIGSERIAL PRIMARY KEY,
  sales_order_id BIGINT NOT NULL REFERENCES sales_order(id) ON DELETE CASCADE,
  line_no INTEGER NOT NULL,
  sellable_item_id BIGINT REFERENCES sellable_item(id) ON DELETE RESTRICT,
  raw_external_article_number TEXT,
  raw_external_title TEXT,
  qty NUMERIC(14, 4) NOT NULL,
  qty_cancelled NUMERIC(14, 4) NOT NULL DEFAULT 0,
  line_status TEXT NOT NULL DEFAULT 'allocated', -- allocated | partially_cancelled | cancelled
  unit_price NUMERIC(14, 4),
  line_total NUMERIC(14, 2),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_sales_order_line_qty_positive CHECK (qty > 0),
  CONSTRAINT chk_sales_order_line_qty_cancelled_range CHECK (qty_cancelled >= 0 AND qty_cancelled <= qty),
  CONSTRAINT chk_sales_order_line_status CHECK (line_status IN ('allocated', 'partially_cancelled', 'cancelled'))
);

CREATE UNIQUE INDEX uq_sales_order_line_no ON sales_order_line(sales_order_id, line_no);
CREATE INDEX idx_sales_order_line_order ON sales_order_line(sales_order_id);
CREATE INDEX idx_sales_order_line_sellable_item ON sales_order_line(sellable_item_id);
CREATE INDEX idx_sales_order_line_status ON sales_order_line(line_status);

CREATE TABLE stock_move (
  id BIGSERIAL PRIMARY KEY,
  product_id BIGINT NOT NULL REFERENCES product(id) ON DELETE RESTRICT,
  lot_id BIGINT NOT NULL REFERENCES stock_lot(id) ON DELETE RESTRICT,
  from_location_id BIGINT REFERENCES location(id) ON DELETE RESTRICT,
  to_location_id BIGINT REFERENCES location(id) ON DELETE RESTRICT,
  qty NUMERIC(14, 4) NOT NULL,
  move_type TEXT NOT NULL, -- in | out | transfer | adjustment
  move_date TIMESTAMP NOT NULL DEFAULT NOW(),
  note TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_stock_move_qty_positive CHECK (qty > 0),
  CONSTRAINT chk_stock_move_type CHECK (move_type IN ('in', 'out', 'transfer', 'adjustment'))
);

CREATE INDEX idx_stock_move_product ON stock_move(product_id);
CREATE INDEX idx_stock_move_lot ON stock_move(lot_id);
CREATE INDEX idx_stock_move_from_location ON stock_move(from_location_id);
CREATE INDEX idx_stock_move_to_location ON stock_move(to_location_id);

-- Explizite Rueckverfolgung: welche Charge wurde fuer welche Bestellposition verwendet
CREATE TABLE sales_order_line_lot_allocation (
  id BIGSERIAL PRIMARY KEY,
  sales_order_line_id BIGINT NOT NULL REFERENCES sales_order_line(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES product(id) ON DELETE RESTRICT,
  lot_id BIGINT NOT NULL REFERENCES stock_lot(id) ON DELETE RESTRICT,
  qty NUMERIC(14, 4) NOT NULL,
  allocation_status TEXT NOT NULL DEFAULT 'allocated', -- reserved | allocated | released | cancelled
  released_at TIMESTAMP,
  stock_move_id BIGINT REFERENCES stock_move(id) ON DELETE SET NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_line_lot_qty_positive CHECK (qty > 0),
  CONSTRAINT chk_line_lot_alloc_status CHECK (allocation_status IN ('reserved', 'allocated', 'released', 'cancelled'))
);

CREATE INDEX idx_line_lot_alloc_line ON sales_order_line_lot_allocation(sales_order_line_id);
CREATE INDEX idx_line_lot_alloc_product ON sales_order_line_lot_allocation(product_id);
CREATE INDEX idx_line_lot_alloc_lot ON sales_order_line_lot_allocation(lot_id);
CREATE INDEX idx_line_lot_alloc_stock_move ON sales_order_line_lot_allocation(stock_move_id);
CREATE INDEX idx_line_lot_alloc_status ON sales_order_line_lot_allocation(allocation_status);

-- Berechnete Chargensalden (Lagereingang/-ausgang/-netto)
CREATE VIEW v_stock_lot_balance AS
SELECT
  sl.id AS stock_lot_id,
  sl.product_id,
  COALESCE(SUM(CASE WHEN sm.move_type = 'in' THEN sm.qty ELSE 0 END), 0) AS qty_in,
  COALESCE(SUM(CASE WHEN sm.move_type = 'out' THEN sm.qty ELSE 0 END), 0) AS qty_out,
  COALESCE(SUM(
    CASE
      WHEN sm.move_type = 'in' THEN sm.qty
      WHEN sm.move_type = 'out' THEN -sm.qty
      ELSE 0
    END
  ), 0) AS qty_net
FROM stock_lot sl
LEFT JOIN stock_move sm ON sm.lot_id = sl.id
GROUP BY sl.id, sl.product_id;

-- Operative Guardrail-Trigger:
-- 1) keine negativen Chargensalden
-- 2) wenn aktuelle Charge <= 0, auf offene Charge umschalten und neue offene Charge vorbereiten
-- Hinweis: Die konkrete Trigger-Implementierung erfolgt in der Migrationsphase.

-- Standard Audit Trail (vorbereitet fuer spaetere Module)
CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  entity_name TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  action TEXT NOT NULL, -- insert | update | delete | import | allocate
  changed_by TEXT,
  changed_at TIMESTAMP NOT NULL DEFAULT NOW(),
  before_data JSONB,
  after_data JSONB,
  context JSONB
);

CREATE INDEX idx_audit_log_entity ON audit_log(entity_name, entity_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log(changed_at);

-- Outbound-Webhook Queue (ERP -> n8n)
CREATE TABLE outbound_webhook_event (
  id BIGSERIAL PRIMARY KEY,
  event_type TEXT NOT NULL, -- order.imported | order.cancelled.partial | order.cancelled.full | lot.auto_switched
  event_key TEXT NOT NULL, -- idempotency key for consumers
  aggregate_type TEXT NOT NULL, -- sales_order | stock_lot | sales_order_line
  aggregate_id TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending', -- pending | processing | sent | failed | dead_letter
  attempt_count INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TIMESTAMP NOT NULL DEFAULT NOW(),
  last_attempt_at TIMESTAMP,
  last_error TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  sent_at TIMESTAMP,
  CONSTRAINT chk_outbound_webhook_status CHECK (status IN ('pending', 'processing', 'sent', 'failed', 'dead_letter'))
);

CREATE UNIQUE INDEX uq_outbound_webhook_event_key ON outbound_webhook_event(event_key);
CREATE INDEX idx_outbound_webhook_status_next ON outbound_webhook_event(status, next_attempt_at);