-- Tour-Domäne: Stammdaten (customers, customer_contacts, articles, warehouses)
-- plus die transaktionalen Tabellen tours, deliveries, delivery_items.
--
-- Datenfluss: das ERP pusht eine Tour via POST /sync/tour. Dieser Sync
-- legt fehlende Kunden/Artikel/Lager an oder aktualisiert sie und schreibt
-- danach die transaktionalen Zeilen.

-- ---------- Stamm: Lager -------------------------------------------------
CREATE TABLE warehouses (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    code         TEXT        NOT NULL UNIQUE,
    name         TEXT        NOT NULL,
    is_standard  BOOLEAN     NOT NULL DEFAULT FALSE
);

-- Genau ein Lager darf als "Standardlager" markiert sein. Reduziert die
-- Fertig-Logik auf der App auf einen Bool-Check.
CREATE UNIQUE INDEX warehouses_one_standard
    ON warehouses ((is_standard))
    WHERE is_standard;

-- ---------- Stamm: Artikel ------------------------------------------------
CREATE TABLE articles (
    id                   UUID  PRIMARY KEY DEFAULT gen_random_uuid(),
    article_number       TEXT  NOT NULL UNIQUE,
    name                 TEXT  NOT NULL,
    scannable            BOOLEAN NOT NULL DEFAULT TRUE,
    default_warehouse_id UUID  REFERENCES warehouses(id)
);

-- ---------- Stamm: Kunde --------------------------------------------------
CREATE TABLE customers (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    erp_customer_id BIGINT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    -- Aktuelle Anschrift (für Snapshot in deliveries gesondert geführt)
    street          TEXT NOT NULL,
    house_number    TEXT NOT NULL,
    postal_code     TEXT NOT NULL,
    city            TEXT NOT NULL,
    country         TEXT NOT NULL
);

CREATE TABLE customer_contacts (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    phone       TEXT,
    email       TEXT
);
CREATE INDEX customer_contacts_customer ON customer_contacts(customer_id);

-- ---------- Transaktional: Tour ------------------------------------------
CREATE TABLE tours (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- account_id = Personalnummer des Subunternehmers
    account_id    BIGINT NOT NULL REFERENCES accounts(personalnummer),
    tour_date     DATE NOT NULL,
    synced_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- Eine Tour pro Account und Tag. Der Sync läuft als Upsert auf diese
    -- Constraint.
    UNIQUE (account_id, tour_date)
);
CREATE INDEX tours_account_date ON tours(account_id, tour_date);

-- ---------- Transaktional: Delivery --------------------------------------
CREATE TABLE deliveries (
    id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tour_id                  UUID NOT NULL REFERENCES tours(id) ON DELETE CASCADE,

    -- Business-stabiles Beleg-Paar aus dem ERP, überlebt Archivübergang
    erp_belegart_id          BIGINT NOT NULL,
    erp_belegnummer          TEXT   NOT NULL,

    customer_id              UUID NOT NULL REFERENCES customers(id),

    -- Snapshot der Adresse zum Zeitpunkt des Tour-Syncs
    snap_street              TEXT NOT NULL,
    snap_house_number        TEXT NOT NULL,
    snap_postal_code         TEXT NOT NULL,
    snap_city                TEXT NOT NULL,
    snap_country             TEXT NOT NULL,

    assigned_car_id          UUID,                -- noch keine cars-Tabelle, FK später
    desired_time             TEXT,
    special_agreements       TEXT,
    state                    TEXT NOT NULL DEFAULT 'active'
                                CHECK (state IN ('active', 'held', 'canceled', 'completed')),
    cancellation_reason      TEXT,

    -- Sortier-Reihenfolge innerhalb der Tour. Beim Sync mit dichter
    -- Reihenfolge initialisiert, später durch PUT /tours/{id}/delivery-order
    -- überschrieben.
    sort_order               INT  NOT NULL DEFAULT 0,

    UNIQUE (erp_belegart_id, erp_belegnummer)
);
CREATE INDEX deliveries_tour ON deliveries(tour_id);
CREATE INDEX deliveries_customer ON deliveries(customer_id);

-- N:M-Tabelle Delivery → ausgewählte Ansprechpartner
CREATE TABLE delivery_contact_persons (
    delivery_id        UUID NOT NULL REFERENCES deliveries(id) ON DELETE CASCADE,
    customer_contact_id UUID NOT NULL REFERENCES customer_contacts(id) ON DELETE CASCADE,
    PRIMARY KEY (delivery_id, customer_contact_id)
);

-- ---------- Transaktional: DeliveryItem ----------------------------------
CREATE TABLE delivery_items (
    id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    delivery_id              UUID NOT NULL REFERENCES deliveries(id) ON DELETE CASCADE,

    article_id               UUID NOT NULL REFERENCES articles(id),
    required_quantity        INT  NOT NULL CHECK (required_quantity > 0),
    warehouse_id             UUID NOT NULL REFERENCES warehouses(id),

    -- ERP-Position innerhalb des Belegs
    belegzeilen_nr           INT NOT NULL,
    -- Stücklistenkomponente: ArtNr der Komponente, sonst NULL
    komponenten_artikel_nr   TEXT,

    -- Embedded ScanState (siehe Domain::ScanState)
    scanned_quantity         INT NOT NULL DEFAULT 0 CHECK (scanned_quantity >= 0),
    scan_status              TEXT NOT NULL DEFAULT 'in_progress'
                                CHECK (scan_status IN ('in_progress','done','held','removed')),
    held_reason              TEXT,
    scan_last_updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- NULLS NOT DISTINCT: zwei Items mit (delivery, belegzeilenNr) und
    -- NULL-Komponente kollidieren — sonst würde der UPSERT eine zweite
    -- Zeile anlegen statt zu aktualisieren. (Postgres 15+)
    UNIQUE NULLS NOT DISTINCT (delivery_id, belegzeilen_nr, komponenten_artikel_nr)
);
CREATE INDEX delivery_items_delivery ON delivery_items(delivery_id);

-- ---------- Seed: Smoke-Test-Daten ----------------------------------------
INSERT INTO warehouses (id, code, name, is_standard) VALUES
    ('11111111-1111-1111-1111-111111111111', '0', 'Standardlager', TRUE),
    ('11111111-1111-1111-1111-111111111112', 'A', 'Außenlager A',  FALSE);

INSERT INTO articles (id, article_number, name, scannable, default_warehouse_id) VALUES
    ('22222222-2222-2222-2222-222222222221', 'BRETT-200', 'Holzbrett 200cm', TRUE,  '11111111-1111-1111-1111-111111111111'),
    ('22222222-2222-2222-2222-222222222222', 'PALETTE-EUR', 'Europalette',   TRUE,  '11111111-1111-1111-1111-111111111111'),
    ('22222222-2222-2222-2222-222222222223', 'FRACHT-PAUSCH', 'Fracht',     FALSE, NULL);

INSERT INTO customers (id, erp_customer_id, name, street, house_number, postal_code, city, country) VALUES
    ('33333333-3333-3333-3333-333333333331', 4711, 'Bauernhof Huber',  'Dorfstraße',   '12', '83410', 'Laufen',     'DE'),
    ('33333333-3333-3333-3333-333333333332', 4712, 'Sägewerk Müller',  'Industriering', '5', '83395', 'Freilassing', 'DE');

INSERT INTO customer_contacts (id, customer_id, name, phone, email) VALUES
    ('44444444-4444-4444-4444-444444444441', '33333333-3333-3333-3333-333333333331', 'Sepp Huber',   '+49 8682 12345', NULL),
    ('44444444-4444-4444-4444-444444444442', '33333333-3333-3333-3333-333333333332', 'Anna Müller',  NULL,             'anna@muellersaege.de');

-- Eine Beispiel-Tour für Personalnummer 1001 am heutigen Tag
INSERT INTO tours (id, account_id, tour_date) VALUES
    ('55555555-5555-5555-5555-555555555555', 1001, CURRENT_DATE);

INSERT INTO deliveries (
    id, tour_id, erp_belegart_id, erp_belegnummer, customer_id,
    snap_street, snap_house_number, snap_postal_code, snap_city, snap_country,
    sort_order
) VALUES
    ('66666666-6666-6666-6666-666666666661', '55555555-5555-5555-5555-555555555555',
     1, 'AB-2026-0001', '33333333-3333-3333-3333-333333333331',
     'Dorfstraße', '12', '83410', 'Laufen', 'DE', 1),
    ('66666666-6666-6666-6666-666666666662', '55555555-5555-5555-5555-555555555555',
     1, 'AB-2026-0002', '33333333-3333-3333-3333-333333333332',
     'Industriering', '5', '83395', 'Freilassing', 'DE', 2);

INSERT INTO delivery_contact_persons (delivery_id, customer_contact_id) VALUES
    ('66666666-6666-6666-6666-666666666661', '44444444-4444-4444-4444-444444444441'),
    ('66666666-6666-6666-6666-666666666662', '44444444-4444-4444-4444-444444444442');

INSERT INTO delivery_items (
    delivery_id, article_id, required_quantity, warehouse_id,
    belegzeilen_nr, komponenten_artikel_nr
) VALUES
    ('66666666-6666-6666-6666-666666666661', '22222222-2222-2222-2222-222222222221',
     20, '11111111-1111-1111-1111-111111111111', 1, NULL),
    ('66666666-6666-6666-6666-666666666661', '22222222-2222-2222-2222-222222222222',
     2, '11111111-1111-1111-1111-111111111111', 2, NULL),
    ('66666666-6666-6666-6666-666666666662', '22222222-2222-2222-2222-222222222221',
     10, '11111111-1111-1111-1111-111111111112', 1, NULL),
    ('66666666-6666-6666-6666-666666666662', '22222222-2222-2222-2222-222222222223',
     1, '11111111-1111-1111-1111-111111111111', 2, NULL);