shop-old/core/postgres/schema.sql

-- ============================================================================
-- KREMPL PostgreSQL Schema
-- Reduziert 61 Millionen MySQL-Zeilen auf 68k Zeilen mit Arrays
-- ============================================================================

-- Datenbank erstellen (als postgres User ausführen)
-- CREATE DATABASE krempl_data
--   WITH ENCODING 'UTF8'
--   LC_COLLATE='de_DE.UTF-8'
--   LC_CTYPE='de_DE.UTF-8';

-- User erstellen
-- CREATE USER krempl_user WITH PASSWORD 'PASSWORT_HIER_SETZEN';
-- GRANT ALL PRIVILEGES ON DATABASE krempl_data TO krempl_user;

-- Nach \c krempl_data:
-- GRANT ALL ON SCHEMA public TO krempl_user;

-- ============================================================================
-- Extensions aktivieren
-- ============================================================================

-- Trigram-Suche für ähnliche Strings
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Schnellere Array-Operationen
CREATE EXTENSION IF NOT EXISTS intarray;

-- Query-Performance Tracking (optional)
-- CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- ============================================================================
-- Tabelle 1: GERÄTE (ca. 1.4 Mio Zeilen)
-- ============================================================================

DROP TABLE IF EXISTS geraete CASCADE;

CREATE TABLE geraete (
  id BIGINT PRIMARY KEY,
  nr VARCHAR(100),
  marke VARCHAR(100),
  typ VARCHAR(200),
  zusatz_nummer VARCHAR(100),
  modell_bezeichnung VARCHAR(200),
  produktionsstart VARCHAR(50),
  produktionsende VARCHAR(50),
  wgtext1 VARCHAR(200),
  wgtext2 VARCHAR(200),
  zusatz TEXT,
  bezeichnung_original TEXT,
  typ_de VARCHAR(200),
  typ_fr VARCHAR(200),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Standard-Indexe für schnelle Lookups
CREATE INDEX idx_geraete_nr ON geraete(nr);
CREATE INDEX idx_geraete_marke ON geraete(marke);
CREATE INDEX idx_geraete_typ ON geraete(typ);

-- Trigram-Indexe für LIKE-Suchen (z.B. "AEG%" oder "%123%")
CREATE INDEX idx_geraete_nr_trgm ON geraete USING GIN(nr gin_trgm_ops);
CREATE INDEX idx_geraete_typ_trgm ON geraete USING GIN(typ gin_trgm_ops);
CREATE INDEX idx_geraete_marke_trgm ON geraete USING GIN(marke gin_trgm_ops);

-- Full-Text Search Index (Kombination: nr + typ + marke)
CREATE INDEX idx_geraete_fts ON geraete
  USING GIN(to_tsvector('german',
    COALESCE(nr,'') || ' ' ||
    COALESCE(typ,'') || ' ' ||
    COALESCE(marke,'') || ' ' ||
    COALESCE(modell_bezeichnung,'')
  ));

COMMENT ON TABLE geraete IS 'Geräte-Stammdaten aus Krempl (ca. 1.4 Mio Einträge)';

-- ============================================================================
-- Tabelle 2: ERSATZTEIL-MAPPING (68k Zeilen statt 61 Mio!)
-- ============================================================================

DROP TABLE IF EXISTS ersatzteil_mapping CASCADE;

CREATE TABLE ersatzteil_mapping (
  ersatzteil_id BIGINT PRIMARY KEY,
  geraet_ids BIGINT[],  -- Array statt Millionen einzelner Zeilen!
  geraet_count INT,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- GIN Index für Array-Suche "Welche Ersatzteile passen zu Gerät X?"
-- Ermöglicht: WHERE geraet_ids @> ARRAY[123456]
CREATE INDEX idx_mapping_geraet_array ON ersatzteil_mapping
  USING GIN(geraet_ids);

-- Standard B-Tree Index für schnelle Ersatzteil-Lookups
CREATE INDEX idx_mapping_ersatzteil ON ersatzteil_mapping(ersatzteil_id);

COMMENT ON TABLE ersatzteil_mapping IS 'N:N Mapping Ersatzteil↔Gerät als Arrays (reduziert 61M Zeilen auf 68k)';
COMMENT ON COLUMN ersatzteil_mapping.geraet_ids IS 'Array aller Geräte-IDs die zu diesem Ersatzteil passen';
COMMENT ON COLUMN ersatzteil_mapping.geraet_count IS 'Anzahl Geräte (für schnelle Anzeige ohne Array-Count)';

-- ============================================================================
-- Tabelle 3: ERSATZTEILE (Metadaten, optional)
-- ============================================================================

DROP TABLE IF EXISTS ersatzteile CASCADE;

CREATE TABLE ersatzteile (
  id BIGINT PRIMARY KEY,
  navision_id BIGINT,
  originalnummer VARCHAR(100),
  marke VARCHAR(100),
  ean VARCHAR(50),
  altenr VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexe für Join mit Shop-Items
CREATE INDEX idx_ersatzteile_navision ON ersatzteile(navision_id);
CREATE INDEX idx_ersatzteile_original ON ersatzteile(originalnummer);
CREATE INDEX idx_ersatzteile_ean ON ersatzteile(ean);

COMMENT ON TABLE ersatzteile IS 'Ersatzteil-Metadaten aus Krempl';

-- ============================================================================
-- Tabelle 4: PASSENDWIE (optional)
-- ============================================================================

DROP TABLE IF EXISTS passendwie CASCADE;

CREATE TABLE passendwie (
  row_id SERIAL PRIMARY KEY,  -- Auto-increment ID für interne Zwecke
  id BIGINT NOT NULL,  -- Krempl Ersatzteil-ID (NICHT unique!)
  navision_id BIGINT,
  vertreiber_id VARCHAR(100),
  vertreiber VARCHAR(200),
  bestellcode VARCHAR(200),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- Eindeutigkeit: Ein Ersatzteil kann mehrere Vertreiber-Codes haben
  UNIQUE(id, vertreiber_id, bestellcode)
);

CREATE INDEX idx_passendwie_id ON passendwie(id);
CREATE INDEX idx_passendwie_navision ON passendwie(navision_id);
CREATE INDEX idx_passendwie_vertreiber ON passendwie(vertreiber_id);
CREATE INDEX idx_passendwie_bestellcode ON passendwie(bestellcode);

COMMENT ON TABLE passendwie IS 'PassendWie-Zuordnungen: Ein Ersatzteil hat mehrere Vertreiber-Bestellcodes (206k Zeilen)';

-- ============================================================================
-- Hilfsfunktionen
-- ============================================================================

-- Funktion: Zähle Geräte in Array (Alternative zu geraet_count-Spalte)
CREATE OR REPLACE FUNCTION count_geraete(ersatzteil_id_param BIGINT)
RETURNS INT AS $$
  SELECT COALESCE(array_length(geraet_ids, 1), 0)
  FROM ersatzteil_mapping
  WHERE ersatzteil_id = ersatzteil_id_param;
$$ LANGUAGE SQL STABLE;

-- Funktion: Auto-Update updated_at Timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger für geraete.updated_at
CREATE TRIGGER update_geraete_updated_at
  BEFORE UPDATE ON geraete
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- Views für vereinfachte Queries (optional)
-- ============================================================================

-- View: Ersatzteile mit Geräte-Anzahl
CREATE OR REPLACE VIEW v_ersatzteile_stats AS
SELECT
  em.ersatzteil_id,
  em.geraet_count,
  e.navision_id,
  e.originalnummer,
  e.marke
FROM ersatzteil_mapping em
LEFT JOIN ersatzteile e ON e.id = em.ersatzteil_id
ORDER BY em.geraet_count DESC;

COMMENT ON VIEW v_ersatzteile_stats IS 'Ersatzteile mit Statistiken (Anzahl passender Geräte)';

-- ============================================================================
-- Performance-Optimierung
-- ============================================================================

-- Statistiken aktualisieren (nach Import ausführen!)
-- ANALYZE geraete;
-- ANALYZE ersatzteil_mapping;
-- ANALYZE ersatzteile;
-- ANALYZE passendwie;

-- ============================================================================
-- Grant Permissions
-- ============================================================================

-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO krempl_user;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO krempl_user;
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO krempl_user;

-- ============================================================================
-- Fertig!
-- ============================================================================

-- Beispiel-Query: Geräte zu Ersatzteil
/*
SELECT g.*
FROM ersatzteil_mapping em
JOIN geraete g ON g.id = ANY(em.geraet_ids)
WHERE em.ersatzteil_id = 7764466071
LIMIT 100;
*/

-- Beispiel-Query: Ersatzteile zu Gerät
/*
SELECT ersatzteil_id
FROM ersatzteil_mapping
WHERE geraet_ids @> ARRAY[123456]::bigint[]
LIMIT 100;
*/

-- Beispiel-Query: Full-Text Suche
/*
SELECT id, nr, marke, typ
FROM geraete
WHERE to_tsvector('german', nr || ' ' || typ) @@ plainto_tsquery('german', 'AEG Kühlschrank')
LIMIT 50;
*/