-- =====================================================
-- SIMPUS Database Schema - Odontogram Module
-- Version: 000019
-- Description: Dental odontogram records with FDI tooth numbering system
-- =====================================================

-- =====================================================
-- TYPES
-- =====================================================

-- Occlusion types for bite classification
CREATE TYPE occlusion_type AS ENUM (
    'normal',
    'class_i',
    'class_ii_div_1',
    'class_ii_div_2',
    'class_iii',
    'open_bite',
    'cross_bite',
    'edge_to_edge'
);

-- Torus types
CREATE TYPE torus_type AS ENUM (
    'none',
    'palatinus',
    'mandibularis',
    'both'
);

-- Tooth surface identifiers
CREATE TYPE tooth_surface AS ENUM (
    'O',  -- Occlusal/Incisal
    'M',  -- Mesial
    'D',  -- Distal
    'B',  -- Buccal
    'L',  -- Lingual/Palatal
    'I',  -- Incisal (primary teeth)
    'F'   -- Facial
);

-- Common dental condition codes
CREATE TYPE dental_condition_code AS ENUM (
    'CAR',  -- Caries
    'ABR',  -- Abrasion
    'ABF',  -- Abfraction
    'ATR',  -- Attrition
    'EROS', -- Erosion
    'FRC',  -- Fracture
    'PERI', -- Pericoronitis
    'GING', -- Gingivitis
    'PERD', -- Periodontitis
    'ABS',  -- Abscess
    'IMP',  -- Impacted
    'MOB',  -- Mobile
    'RCT',  -- Root canal treated
    'ENDO', -- Endodontic
    'DISC', -- Discolored
    'HYP',  -- Hypoplasia
    'FLU',  -- Fluorosis
    'CALC', -- Calculus
    'ANOM'  -- Anomaly
);

-- Restoration material types
CREATE TYPE restoration_material AS ENUM (
    'AMF',  -- Amalgam
    'COF',  -- Composite filling
    'GIC',  -- Glass ionomer cement
    'RMG',  -- Resin modified GIC
    'COMP', -- Compomer
    'POR',  -- Porcelain
    'GOLD', -- Gold
    'ZIR',  -- Zirconia
    'SSC',  -- Stainless steel crown
    'PEX',  -- Preformed crown
    'TMP',  -- Temporary
    'SEAL', -- Sealant
    'VENE', -- Veneer
    'INL',  -- Inlay
    'ONL'   -- Onlay
);

-- Restoration types
CREATE TYPE restoration_type AS ENUM (
    'FILL',      -- Filling
    'CROW',      -- Crown
    'BRIDGE',    -- Bridge
    'IMPL',      -- Implant
    'PARTIAL',   -- Partial denture
    'COMPLETE',  -- Complete denture
    'RCT',       -- Root canal therapy
    'POST',      -- Post and core
    'SEAL',      -- Sealant
    'VENEER',    -- Veneer
    'INLAY',     -- Inlay
    'ONLAY',     -- Onlay
    'SSC',       -- Stainless steel crown
    'PEX'        -- Preformed crown
);

-- Restoration status
CREATE TYPE restoration_status AS ENUM (
    'planned',
    'in_progress',
    'completed',
    'failed',
    'replaced'
);

-- =====================================================
-- ODONTOGRAM MAIN RECORD
-- =====================================================

CREATE TABLE odontograms (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    puskesmas_id UUID NOT NULL REFERENCES puskesmas(id) ON DELETE CASCADE,
    patient_id UUID NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
    visit_id UUID NOT NULL REFERENCES visits(id) ON DELETE CASCADE,
    doctor_id UUID REFERENCES users(id) ON DELETE SET NULL,
    
    -- Examination details
    examination_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Oral assessment
    occlusion_type occlusion_type,
    torus_palatinus torus_type DEFAULT 'none',
    torus_mandibularis torus_type DEFAULT 'none',
    palatum TEXT,
    diastema BOOLEAN DEFAULT FALSE,
    anomaly_notes TEXT,
    
    -- Status
    status VARCHAR(20) DEFAULT 'active',
    
    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    
    -- Constraints
    CONSTRAINT unique_odontogram_per_visit UNIQUE(visit_id)
);

CREATE INDEX idx_odontograms_puskesmas ON odontograms(puskesmas_id);
CREATE INDEX idx_odontograms_patient ON odontograms(patient_id);
CREATE INDEX idx_odontograms_visit ON odontograms(visit_id);
CREATE INDEX idx_odontograms_doctor ON odontograms(doctor_id);
CREATE INDEX idx_odontograms_examination_date ON odontograms(examination_date);

CREATE TRIGGER update_odontograms_updated_at
    BEFORE UPDATE ON odontograms
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- TEETH
-- =====================================================

CREATE TABLE teeth (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    odontogram_id UUID NOT NULL REFERENCES odontograms(id) ON DELETE CASCADE,
    
    -- Tooth identification (FDI system)
    tooth_number VARCHAR(3) NOT NULL,  -- 11-48 (permanent), 51-85 (deciduous)
    is_deciduous BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Tooth status
    present BOOLEAN NOT NULL DEFAULT TRUE,
    erupted BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Additional properties
    mobility INTEGER CHECK (mobility >= 0 AND mobility <= 3),
    rotation_degrees INTEGER CHECK (rotation_degrees >= -180 AND rotation_degrees <= 180),
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT unique_tooth_per_odontogram UNIQUE(odontogram_id, tooth_number)
);

CREATE INDEX idx_teeth_odontogram ON teeth(odontogram_id);
CREATE INDEX idx_teeth_number ON teeth(tooth_number);

CREATE TRIGGER update_teeth_updated_at
    BEFORE UPDATE ON teeth
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- TOOTH CONDITIONS
-- =====================================================

CREATE TABLE tooth_conditions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tooth_id UUID NOT NULL REFERENCES teeth(id) ON DELETE CASCADE,
    
    -- Condition identification
    surface tooth_surface NOT NULL,
    condition_code dental_condition_code NOT NULL,
    severity INTEGER CHECK (severity >= 1 AND severity <= 5),
    
    -- Medical coding integration
    diagnosis_code VARCHAR(20),  -- ICD-10 code if applicable
    treatment_code VARCHAR(20),   -- Procedure code if applicable
    
    -- Additional information
    notes TEXT,
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    
    CONSTRAINT unique_condition UNIQUE(tooth_id, surface, condition_code)
);

CREATE INDEX idx_tooth_conditions_tooth ON tooth_conditions(tooth_id);
CREATE INDEX idx_tooth_conditions_code ON tooth_conditions(condition_code);

CREATE TRIGGER update_tooth_conditions_updated_at
    BEFORE UPDATE ON tooth_conditions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- TOOTH RESTORATIONS
-- =====================================================

CREATE TABLE tooth_restorations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tooth_id UUID NOT NULL REFERENCES teeth(id) ON DELETE CASCADE,
    
    -- Restoration details
    material_type restoration_material NOT NULL,
    restoration_type restoration_type NOT NULL,
    surfaces tooth_surface[],  -- Multiple surfaces can be restored
    surface_area DECIMAL(5,2) CHECK (surface_area >= 0 AND surface_area <= 100),  -- Percentage
    
    -- Restoration coding
    billing_code VARCHAR(50),
    unit_cost DECIMAL(10,2),
    
    -- Status tracking
    status restoration_status DEFAULT 'planned',
    installation_date DATE,
    completion_date DATE,
    warranty_expiry_date DATE,
    
    -- Quality assessment
    quality_score INTEGER CHECK (quality_score >= 1 AND quality_score <= 10),
    failure_reason TEXT,
    
    -- Notes
    notes TEXT,
    
    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

CREATE INDEX idx_tooth_restorations_tooth ON tooth_restorations(tooth_id);
CREATE INDEX idx_tooth_restorations_status ON tooth_restorations(status);

CREATE TRIGGER update_tooth_restorations_updated_at
    BEFORE UPDATE ON tooth_restorations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- DENTAL PROCEDURE SEQUENCES
-- =====================================================

-- For generating dental procedure numbers
CREATE TABLE dental_procedure_sequences (
    puskesmas_id UUID NOT NULL REFERENCES puskesmas(id) ON DELETE CASCADE,
    procedure_date DATE NOT NULL,
    procedure_type VARCHAR(20) NOT NULL,
    last_number INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (puskesmas_id, procedure_date, procedure_type)
);

-- Function to generate next dental procedure number
CREATE OR REPLACE FUNCTION get_next_dental_procedure_number(
    p_puskesmas_id UUID, 
    p_procedure_date DATE, 
    p_procedure_type VARCHAR(20)
)
RETURNS VARCHAR(30) AS $$
DECLARE
    v_next INT;
    v_procedure_number VARCHAR(30);
BEGIN
    INSERT INTO dental_procedure_sequences (puskesmas_id, procedure_date, procedure_type, last_number)
    VALUES (p_puskesmas_id, p_procedure_date, p_procedure_type, 1)
    ON CONFLICT (puskesmas_id, procedure_date, procedure_type) DO UPDATE
    SET last_number = dental_procedure_sequences.last_number + 1,
    updated_at = CURRENT_TIMESTAMP
    RETURNING last_number INTO v_next;
    
    v_procedure_number := 'DEN-' || UPPER(p_procedure_type) || '-' || TO_CHAR(p_procedure_date, 'YYYYMMDD') || '-' || LPAD(v_next::TEXT, 4, '0');
    RETURN v_procedure_number;
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- VIEW: COMPLETE ODONTOGRAM
-- =====================================================

CREATE VIEW odontogram_complete AS
SELECT 
    o.*,
    p.full_name as patient_name,
    p.medical_record_number,
    v.visit_date,
    u.full_name as doctor_name,
    -- Count of affected teeth
    (SELECT COUNT(*) FROM teeth t WHERE t.odontogram_id = o.id AND t.present = FALSE) as missing_teeth,
    (SELECT COUNT(*) FROM teeth t 
     INNER JOIN tooth_conditions tc ON t.id = tc.tooth_id 
     WHERE t.odontogram_id = o.id AND tc.is_active = TRUE) as conditions_count,
    (SELECT COUNT(*) FROM teeth t 
     INNER JOIN tooth_restorations tr ON t.id = tr.tooth_id 
     WHERE t.odontogram_id = o.id AND tr.status = 'completed') as restorations_count
FROM odontograms o
INNER JOIN patients p ON o.patient_id = p.id
INNER JOIN visits v ON o.visit_id = v.id
LEFT JOIN users u ON o.doctor_id = u.id;