-- ============================================================
-- EIGIS Theme 3: Geothermal Features
-- Hot springs, fumaroles, geothermal gradients, wells
-- Supports future expansion: geochemistry, geophysics, drilling
-- ============================================================

-- -----------------------------------------------------------
-- 1. GEOTHERMAL MANIFESTATIONS
-- -----------------------------------------------------------
CREATE TABLE geothermal_manifestations (
    manifestation_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    manifestation_type VARCHAR(50) NOT NULL CHECK (manifestation_type IN (
        'hot_spring','warm_spring','fumarole','steaming_ground',
        'mud_pot','geyser','sinter_deposit','travertine',
        'altered_ground','hydrothermal_breccia','volcanic_vent',
        'mineral_spring','other'
    )),
    surface_temp_c   DECIMAL(6,2),                     -- Surface temperature °C
    discharge_rate_lps DECIMAL(8,3),                   -- Liters per second
    ph_value         DECIMAL(4,2),
    electrical_conductivity_us DECIMAL(8,2),           -- µS/cm
    total_dissolved_solids DECIMAL(8,2),               -- mg/L
    fluid_color      VARCHAR(50),
    odor             VARCHAR(50),
    deposit_type     VARCHAR(100),                      -- Sinter, travertine, etc.
    alteration_zone  VARCHAR(100),
    alteration_intensity VARCHAR(30) CHECK (alteration_intensity IN ('none','slight','moderate','intense','pervasive')),
    alteration_minerals TEXT[],
    structural_control VARCHAR(100),                   -- Fault-controlled, fracture, etc.
    host_rock        VARCHAR(100),
    geom             GEOMETRY(Point, 4326) NOT NULL,
    elevation_m      DECIMAL(10,2),
    catchment_area   VARCHAR(100),
    usage_current    VARCHAR(100),                     -- Bathing, irrigation, none, etc.
    usage_potential  VARCHAR(100),
    temperature_measurement_depth_m DECIMAL(6,2),
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_geotherm_obs ON geothermal_manifestations(observation_id);
CREATE INDEX idx_geotherm_geom ON geothermal_manifestations USING GIST(geom);
CREATE INDEX idx_geotherm_type ON geothermal_manifestations(manifestation_type);
CREATE INDEX idx_geotherm_temp ON geothermal_manifestations(surface_temp_c);

-- -----------------------------------------------------------
-- 2. GEOTHERMAL GRADIENT STATIONS
-- -----------------------------------------------------------
CREATE TABLE geothermal_gradients (
    gradient_id      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID REFERENCES observations(observation_id) ON DELETE CASCADE,
    station_code     VARCHAR(50) NOT NULL,
    depth_top_m     DECIMAL(8,2) NOT NULL,
    depth_bottom_m  DECIMAL(8,2) NOT NULL,
    temp_top_c      DECIMAL(6,2),
    temp_bottom_c   DECIMAL(6,2),
    gradient_c_per_km DECIMAL(8,2),                    -- °C/km
    conductivity_wmk DECIMAL(8,4),                    -- W/m·K
    heat_flow_mwm2  DECIMAL(8,4),                     -- mW/m²
    measurement_method VARCHAR(50),
    geom             GEOMETRY(Point, 4326),
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_gradient_obs ON geothermal_gradients(observation_id);
CREATE INDEX idx_gradient_geom ON geothermal_gradients USING GIST(geom);
CREATE INDEX idx_gradient_value ON geothermal_gradients(gradient_c_per_km);

-- -----------------------------------------------------------
-- 3. SAMPLING TABLE - Unified across all themes
-- Multiple samples per observation, linked to any sub-entity
-- -----------------------------------------------------------
CREATE TABLE samples (
    sample_id        UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    sample_code      VARCHAR(50) NOT NULL,              -- S-01, GTH-01, etc.
    sample_type      VARCHAR(30) NOT NULL CHECK (sample_type IN (
        'disturbed_soil','undisturbed_soil','bulk_soil',
        'rock_core','rock_chip','water','gas',
        'geothermal_fluid','geothermal_gas','alteration_mineral',
        'surface_sediment','vegetation','other'
    )),
    depth_m          DECIMAL(6,2),
    horizon_ref      VARCHAR(20),                       -- Reference to soil horizon
    weight_kg        DECIMAL(6,2),
    volume_cm3       DECIMAL(8,2),
    sample_condition VARCHAR(30) CHECK (sample_condition IN ('good','fair','poor','compromised')),
    storage_method   VARCHAR(50),                       -- Bag, core box, bottle, etc.
    tests_requested TEXT[],                              -- Array of requested tests
    lab_id          VARCHAR(50),                        -- Laboratory reference
    lab_status      VARCHAR(20) DEFAULT 'pending' CHECK (lab_status IN ('pending','in_progress','completed','failed')),
    lab_results     JSONB,                               -- Flexible results storage
    geom            GEOMETRY(Point, 4326),
    collected_at    TIMESTAMPTZ DEFAULT NOW(),
    collected_by    UUID REFERENCES personnel(personnel_id),
    notes           TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(observation_id, sample_code)
);

CREATE INDEX idx_samples_obs ON samples(observation_id);
CREATE INDEX idx_samples_geom ON samples USING GIST(geom);
CREATE INDEX idx_samples_type ON samples(sample_type);
CREATE INDEX idx_samples_lab_status ON samples(lab_status);
CREATE INDEX idx_samples_code ON samples(sample_code);

-- -----------------------------------------------------------
-- 4. GEOCHEMISTRY RESULTS (Future expansion table)
-- -----------------------------------------------------------
CREATE TABLE geochemistry_results (
    geochem_id       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sample_id        UUID NOT NULL REFERENCES samples(sample_id) ON DELETE CASCADE,
    analysis_type    VARCHAR(50) NOT NULL CHECK (analysis_type IN (
        'xrf','xrd','icp_ms','icp_aes','aas','ic','gc_ms',
        'isotope_ratio','major_oxides','trace_elements','rare_earth',
        'stable_isotope','radiometric','fluid_inclusion','other'
    )),
    laboratory       VARCHAR(100),
    method_code      VARCHAR(30),
    detection_limit  DECIMAL(12,6),
    results          JSONB NOT NULL,                    -- Key-value pairs of elements/compounds
    unit             VARCHAR(20) DEFAULT 'ppm',
    quality_flag     VARCHAR(20) CHECK (quality_flag IN ('valid','suspect','invalid','below_detection')),
    analyzed_at      DATE,
    reported_at      DATE,
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_geochem_sample ON geochemistry_results(sample_id);
CREATE INDEX idx_geochem_type ON geochemistry_results(analysis_type);
CREATE INDEX idx_geochem_results ON geochemistry_results USING GIN(results);

-- -----------------------------------------------------------
-- 5. GEOPHYSICAL SURVEYS (Future expansion table)
-- -----------------------------------------------------------
CREATE TABLE geophysical_surveys (
    survey_id        UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID REFERENCES observations(observation_id) ON DELETE SET NULL,
    project_id       UUID NOT NULL REFERENCES projects(project_id),
    survey_type      VARCHAR(50) NOT NULL CHECK (survey_type IN (
        'magnetic','gravity','seismic_refraction','seismic_reflection',
        'electrical_resistivity','induced_polarization','spm',
        'magnetotelluric','ground_penetrating_radar','self_potential',
        'thermal_infrared','aeromagnetic','other'
    )),
    line_id          VARCHAR(50),
    station_spacing_m DECIMAL(8,2),
    data_points      INTEGER,
    depth_investigation_m DECIMAL(8,2),
    instrument       VARCHAR(100),
    parameters       JSONB,
    results_summary  TEXT,
    data_file_path   VARCHAR(500),                      -- S3/MinIO path
    geom             GEOMETRY(MultiPoint, 4326),        -- Survey stations
    line_geom        GEOMETRY(LineString, 4326),        -- Survey line
    date_conducted   DATE,
    conducted_by     UUID REFERENCES personnel(personnel_id),
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_geophys_obs ON geophysical_surveys(observation_id);
CREATE INDEX idx_geophys_project ON geophysical_surveys(project_id);
CREATE INDEX idx_geophys_geom ON geophysical_surveys USING GIST(geom);
CREATE INDEX idx_geophys_line ON geophysical_surveys USING GIST(line_geom);

-- -----------------------------------------------------------
-- 6. DRILLING DATA (Future expansion table)
-- -----------------------------------------------------------
CREATE TABLE drilling_data (
    drill_id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id       UUID NOT NULL REFERENCES projects(project_id),
    observation_id   UUID REFERENCES observations(observation_id) ON DELETE SET NULL,
    borehole_code    VARCHAR(50) NOT NULL,
    bh_type          VARCHAR(30) CHECK (bh_type IN ('exploratory','production','injection','monitoring','temperature_gradient','core_hole')),
    collar_easting   DECIMAL(12,2),
    collar_northing  DECIMAL(12,2),
    collar_elevation DECIMAL(10,2),
    total_depth_m    DECIMAL(8,2),
    diameter_mm      DECIMAL(6,1),
    inclination_deg  DECIMAL(5,1),
    azimuth_deg      DECIMAL(5,1),
    water_table_m    DECIMAL(6,2),
    circulation_loss VARCHAR(50),
    lithology_log    JSONB,                             -- Array of {from, to, description}
    geom             GEOMETRY(Point, 4326) NOT NULL,
    trajectory_geom  GEOMETRY(LineString, 4326),       -- 3D borehole path
    start_date       DATE,
    end_date         DATE,
    contractor       VARCHAR(100),
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(project_id, borehole_code)
);

CREATE INDEX idx_drill_project ON drilling_data(project_id);
CREATE INDEX idx_drill_geom ON drilling_data USING GIST(geom);
CREATE INDEX idx_drill_traj ON drilling_data USING GIST(trajectory_geom);

-- -----------------------------------------------------------
-- 7. PETROGRAPHY (Future expansion table)
-- -----------------------------------------------------------
CREATE TABLE petrography (
    petro_id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sample_id        UUID NOT NULL REFERENCES samples(sample_id) ON DELETE CASCADE,
    thin_section_id  VARCHAR(50),
    rock_name        VARCHAR(100),
    modal_mineralogy JSONB,                             -- {quartz: 35, feldspar: 45, ...}
    texture_type     VARCHAR(50),
    grain_size_mm    DECIMAL(6,3),
    fabric           VARCHAR(50),
    alteration       TEXT,
    micro_structures TEXT,
    photo_path       VARCHAR(500),
    analyzed_by      UUID REFERENCES personnel(personnel_id),
    analyzed_at      DATE,
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_petro_sample ON petrography(sample_id);
