secu/db/migrations/004_objects.sql

-- Migration: 004_objects.sql
-- Objektverwaltung / Wachobjekte

-- Wachobjekte/Standorte
CREATE TABLE IF NOT EXISTS objects (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- Basisdaten
    name VARCHAR(200) NOT NULL,
    short_name VARCHAR(50),
    object_number VARCHAR(50), -- Interne Objektnummer
    
    -- Typ
    object_type VARCHAR(50) DEFAULT 'other', -- building, event, construction, retail, industrial, residential, other
    
    -- Adresse
    street VARCHAR(200),
    house_number VARCHAR(20),
    postal_code VARCHAR(10),
    city VARCHAR(100),
    country VARCHAR(50) DEFAULT 'Deutschland',
    
    -- Koordinaten (für Karte/GPS)
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- Kontaktdaten Objekt
    phone VARCHAR(50),
    email VARCHAR(255),
    
    -- Details
    description TEXT,
    size_sqm INTEGER, -- Größe in m²
    floors INTEGER, -- Anzahl Etagen
    
    -- Zugang
    access_info TEXT, -- Zugangsinfos (Schlüssel, Codes, etc.)
    parking_info TEXT, -- Parkhinweise
    
    -- Kundenreferenz
    customer_id INTEGER, -- Später für CRM-Modul
    customer_name VARCHAR(200),
    
    -- Status
    status VARCHAR(20) DEFAULT 'active', -- active, inactive, archived
    
    -- Bilder
    image_url TEXT,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_objects_org ON objects(org_id);
CREATE INDEX IF NOT EXISTS idx_objects_status ON objects(status);
CREATE INDEX IF NOT EXISTS idx_objects_type ON objects(object_type);

-- Ansprechpartner pro Objekt
CREATE TABLE IF NOT EXISTS object_contacts (
    id SERIAL PRIMARY KEY,
    object_id INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    
    -- Kontaktdaten
    name VARCHAR(200) NOT NULL,
    role VARCHAR(100), -- z.B. "Hausmeister", "Objektleiter", "Notfall"
    company VARCHAR(200),
    phone VARCHAR(50),
    mobile VARCHAR(50),
    email VARCHAR(255),
    
    -- Verfügbarkeit
    availability TEXT, -- z.B. "Mo-Fr 8-17 Uhr"
    
    -- Priorität
    is_primary BOOLEAN DEFAULT false,
    is_emergency BOOLEAN DEFAULT false, -- Notfallkontakt
    
    notes TEXT,
    
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_object_contacts_object ON object_contacts(object_id);

-- Dienstanweisungen pro Objekt
CREATE TABLE IF NOT EXISTS object_instructions (
    id SERIAL PRIMARY KEY,
    object_id INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    
    title VARCHAR(200) NOT NULL,
    category VARCHAR(50) DEFAULT 'general', -- general, patrol, emergency, access, reporting
    content TEXT NOT NULL,
    
    -- Sortierung/Priorität
    sort_order INTEGER DEFAULT 0,
    is_critical BOOLEAN DEFAULT false, -- Wichtig hervorheben
    
    -- Version
    version INTEGER DEFAULT 1,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_object_instructions_object ON object_instructions(object_id);

-- Dokumente pro Objekt (Lagepläne, Schlüssellisten, etc.)
CREATE TABLE IF NOT EXISTS object_documents (
    id SERIAL PRIMARY KEY,
    object_id INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    
    name VARCHAR(200) NOT NULL,
    document_type VARCHAR(50), -- floorplan, keylist, contract, photo, other
    file_url TEXT NOT NULL,
    file_size INTEGER,
    mime_type VARCHAR(100),
    
    description TEXT,
    
    uploaded_at TIMESTAMP DEFAULT NOW(),
    uploaded_by INTEGER REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_object_documents_object ON object_documents(object_id);

-- Checkpoints/Kontrollpunkte pro Objekt (für Wächterkontrolle)
CREATE TABLE IF NOT EXISTS object_checkpoints (
    id SERIAL PRIMARY KEY,
    object_id INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    
    name VARCHAR(100) NOT NULL,
    location_description TEXT, -- Wo genau ist der Punkt
    
    -- QR/NFC Code
    code VARCHAR(100) UNIQUE, -- Der zu scannende Code
    code_type VARCHAR(20) DEFAULT 'qr', -- qr, nfc, manual
    
    -- Position in Rundgang
    sort_order INTEGER DEFAULT 0,
    
    -- GPS (optional)
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- Status
    active BOOLEAN DEFAULT true,
    
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_object_checkpoints_object ON object_checkpoints(object_id);
CREATE INDEX IF NOT EXISTS idx_object_checkpoints_code ON object_checkpoints(code);

-- Anforderungen pro Objekt (welche Qualifikationen werden benötigt)
CREATE TABLE IF NOT EXISTS object_requirements (
    id SERIAL PRIMARY KEY,
    object_id INTEGER NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    qualification_type_id INTEGER REFERENCES qualification_types(id),
    org_qualification_type_id INTEGER REFERENCES org_qualification_types(id),
    is_mandatory BOOLEAN DEFAULT true,
    notes TEXT,
    
    CONSTRAINT chk_object_req_type CHECK (
        (qualification_type_id IS NOT NULL AND org_qualification_type_id IS NULL) OR
        (qualification_type_id IS NULL AND org_qualification_type_id IS NOT NULL)
    )
);

CREATE INDEX IF NOT EXISTS idx_object_requirements_object ON object_requirements(object_id);

-- Objekt-Typen Referenz
COMMENT ON TABLE objects IS 'Wachobjekte/Standorte - object_type: building, event, construction, retail, industrial, residential, other';
COMMENT ON TABLE object_contacts IS 'Ansprechpartner pro Objekt';
COMMENT ON TABLE object_instructions IS 'Dienstanweisungen - category: general, patrol, emergency, access, reporting';
COMMENT ON TABLE object_documents IS 'Dokumente - document_type: floorplan, keylist, contract, photo, other';
COMMENT ON TABLE object_checkpoints IS 'Kontrollpunkte für Wächterkontrolle';