-- ==========================================
-- CLUB DAGUERRE CAMERA DATABASE
-- Part 1: Core Hierarchy
-- ==========================================

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- ==========================================
-- MANUFACTURERS
-- ==========================================
CREATE TABLE manufacturers (
  id SERIAL PRIMARY KEY,
  goergens_code VARCHAR(4) UNIQUE NOT NULL,
  name VARCHAR(200) NOT NULL,
  country VARCHAR(3),
  years_active INT4RANGE,
  
  -- Audit
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by INT,
  updated_at TIMESTAMP,
  updated_by INT,
  version INT DEFAULT 1,
  deleted_at TIMESTAMP,
  
  CONSTRAINT chk_mfg_code CHECK (goergens_code ~ '^[A-Z]{2,4}$')
);

CREATE INDEX idx_mfg_name ON manufacturers(name);
CREATE INDEX idx_mfg_country ON manufacturers(country);
CREATE INDEX idx_mfg_active ON manufacturers(id) WHERE deleted_at IS NULL;

COMMENT ON TABLE manufacturers IS 'Camera manufacturers - top level of Goergens hierarchy';
COMMENT ON COLUMN manufacturers.goergens_code IS 'Goergens manufacturer code (e.g., ERNM, ZEII)';

-- ==========================================
-- CAMERA MODELS
-- ==========================================
CREATE TABLE camera_models (
  id SERIAL PRIMARY KEY,
  manufacturer_id INT NOT NULL REFERENCES manufacturers(id),
  goergens_model_number VARCHAR(10) NOT NULL,
  
  -- Goergens classification codes
  body_type VARCHAR(2) NOT NULL,
  viewfinder_type VARCHAR(4),
  format_code VARCHAR(20) NOT NULL,
  
  -- Production years
  year_first INT,
  year_last INT,
  
  -- Audit
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by INT,
  updated_at TIMESTAMP,
  updated_by INT,
  version INT DEFAULT 1,
  deleted_at TIMESTAMP,
  
  UNIQUE(manufacturer_id, goergens_model_number),
  CONSTRAINT chk_years CHECK (year_first IS NULL OR year_last IS NULL OR year_first <= year_last),
  CONSTRAINT chk_body_type CHECK (body_type ~ '^[A-Z][a-z]?$')
);

CREATE INDEX idx_models_manufacturer ON camera_models(manufacturer_id);
CREATE INDEX idx_models_type ON camera_models(body_type, viewfinder_type);
CREATE INDEX idx_models_format ON camera_models(format_code);
CREATE INDEX idx_models_years ON camera_models(year_first, year_last);
CREATE INDEX idx_models_active ON camera_models(id) WHERE deleted_at IS NULL;

COMMENT ON TABLE camera_models IS 'Camera models - grouping level in Goergens system';
COMMENT ON COLUMN camera_models.goergens_model_number IS 'Model number from Goergens ID (e.g., 1910 for Bob IV)';

-- ==========================================
-- CAMERA MODEL I18N
-- ==========================================
CREATE TABLE camera_model_i18n (
  camera_model_id INT PRIMARY KEY REFERENCES camera_models(id) ON DELETE CASCADE,
  
  model_name VARCHAR(200),
  description_de TEXT,
  description_en TEXT,
  marketing_name_de VARCHAR(200),
  marketing_name_en VARCHAR(200),
  
  -- Full-text search vectors
  search_vector_de TSVECTOR GENERATED ALWAYS AS (
    to_tsvector('german', COALESCE(model_name, '') || ' ' || COALESCE(description_de, ''))
  ) STORED,
  search_vector_en TSVECTOR GENERATED ALWAYS AS (
    to_tsvector('english', COALESCE(model_name, '') || ' ' || COALESCE(description_en, ''))
  ) STORED
);

CREATE INDEX idx_i18n_fts_de ON camera_model_i18n USING GIN(search_vector_de);
CREATE INDEX idx_i18n_fts_en ON camera_model_i18n USING GIN(search_vector_en);

COMMENT ON TABLE camera_model_i18n IS 'Multilingual content for camera models';

-- ==========================================
-- HOUSING VARIANTS
-- ==========================================
CREATE TABLE housing_variants (
  id SERIAL PRIMARY KEY,
  camera_model_id INT NOT NULL REFERENCES camera_models(id) ON DELETE CASCADE,
  goergens_variant_letter VARCHAR(2) NOT NULL,
  
  color VARCHAR(50),
  body_material VARCHAR(50),
  distinguishing_features TEXT,
  
  -- Audit
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by INT,
  updated_at TIMESTAMP,
  updated_by INT,
  version INT DEFAULT 1,
  deleted_at TIMESTAMP,
  
  UNIQUE(camera_model_id, goergens_variant_letter),
  CONSTRAINT chk_variant_letter CHECK (goergens_variant_letter ~ '^[a-z]{1,2}$')
);

CREATE INDEX idx_variants_model ON housing_variants(camera_model_id);
CREATE INDEX idx_variants_active ON housing_variants(id) WHERE deleted_at IS NULL;

COMMENT ON TABLE housing_variants IS 'Housing variants - color, material variations';
COMMENT ON COLUMN housing_variants.goergens_variant_letter IS 'Variant letter from Goergens ID (a, b, c, etc.)';

-- ==========================================
-- LENS/SHUTTER COMBINATIONS
-- ==========================================
CREATE TABLE lens_shutter_combos (
  id SERIAL PRIMARY KEY,
  housing_variant_id INT NOT NULL REFERENCES housing_variants(id) ON DELETE CASCADE,
  goergens_combo_number VARCHAR(3) NOT NULL,
  
  lens_id INT,
  shutter_id INT,
  
  -- DENORMALIZED FIELDS FOR PERFORMANCE
  manufacturer_id INT,
  camera_model_id INT,
  goergens_full_id VARCHAR(50),
  display_name TEXT,
  
  -- STRUCTURED TECHNICAL SPECS
  weight_g INT,
  width_mm DECIMAL(6,2),
  height_mm DECIMAL(6,2),
  depth_mm DECIMAL(6,2),
  
  -- FLEXIBLE SPECS (JSONB)
  specs JSONB,
  
  -- Full-text search
  search_text TEXT,
  
  -- Audit
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by INT,
  updated_at TIMESTAMP,
  updated_by INT,
  version INT DEFAULT 1,
  deleted_at TIMESTAMP,
  
  UNIQUE(housing_variant_id, goergens_combo_number),
  CONSTRAINT chk_weight CHECK (weight_g IS NULL OR (weight_g > 0 AND weight_g < 50000)),
  CONSTRAINT chk_dimensions CHECK (
    width_mm IS NULL OR height_mm IS NULL OR depth_mm IS NULL OR
    (width_mm > 0 AND height_mm > 0 AND depth_mm > 0)
  )
);

-- Critical indexes
CREATE INDEX idx_combos_variant ON lens_shutter_combos(housing_variant_id);
CREATE INDEX idx_combos_lens ON lens_shutter_combos(lens_id);
CREATE INDEX idx_combos_shutter ON lens_shutter_combos(shutter_id);

-- Denormalized indexes (performance boost)
CREATE INDEX idx_combos_manufacturer ON lens_shutter_combos(manufacturer_id);
CREATE INDEX idx_combos_model ON lens_shutter_combos(camera_model_id);
CREATE INDEX idx_combos_goergens_id ON lens_shutter_combos(goergens_full_id);

-- JSONB and full-text search
CREATE INDEX idx_combos_specs ON lens_shutter_combos USING GIN(specs);
CREATE INDEX idx_combos_fts ON lens_shutter_combos USING GIN(to_tsvector('german', COALESCE(search_text, '')));
CREATE INDEX idx_combos_active ON lens_shutter_combos(id) WHERE deleted_at IS NULL;

COMMENT ON TABLE lens_shutter_combos IS 'Lens/shutter combinations - lowest level of hierarchy';
COMMENT ON COLUMN lens_shutter_combos.goergens_full_id IS 'Complete Goergens ID (e.g., ERNM K 1910 a01)';
COMMENT ON COLUMN lens_shutter_combos.specs IS 'Flexible technical specs as JSON (body_material, bellows_color, etc.)';

-- ==========================================
-- CAMERA RELATIONSHIPS
-- ==========================================
CREATE TABLE camera_relations (
  id SERIAL PRIMARY KEY,
  source_combo_id INT NOT NULL REFERENCES lens_shutter_combos(id) ON DELETE CASCADE,
  related_combo_id INT NOT NULL REFERENCES lens_shutter_combos(id) ON DELETE CASCADE,
  relationship_type VARCHAR(50) NOT NULL,
  notes TEXT,
  
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  created_by INT,
  
  UNIQUE(source_combo_id, related_combo_id, relationship_type),
  CONSTRAINT chk_not_self CHECK (source_combo_id != related_combo_id),
  CONSTRAINT chk_relationship_type CHECK (relationship_type IN ('successor', 'predecessor', 'similar', 'variant_of', 'replaced_by'))
);

CREATE INDEX idx_relations_source ON camera_relations(source_combo_id);
CREATE INDEX idx_relations_target ON camera_relations(related_combo_id);
CREATE INDEX idx_relations_type ON camera_relations(relationship_type);

COMMENT ON TABLE camera_relations IS 'Relationships between cameras (evolution, variants, etc.)';