-- ============================================================
-- EIGIS Theme 1: Surface Geological Features
-- Soil profiles, rock descriptions, lithological observations
-- Supports multiple horizons per observation, multiple samples
-- ============================================================

-- -----------------------------------------------------------
-- 1. SOIL PROFILES - Linked to observations
-- -----------------------------------------------------------
CREATE TABLE soil_profiles (
    soil_profile_id  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    profile_code     VARCHAR(50),                     -- e.g. SP-001
    total_depth_m    DECIMAL(8,2),
    groundwater_depth_m DECIMAL(8,2),
    surface_condition VARCHAR(100),
    vegetation_cover VARCHAR(100),
    erosion_evidence VARCHAR(100),
    drainage_class   VARCHAR(50) CHECK (drainage_class IN ('very_poor','poor','moderately_poor','moderate','moderately_well','well','excessively_well')),
    dilatancy        VARCHAR(20) CHECK (dilatancy IN ('none','slow','rapid')),
    sorting          VARCHAR(30) CHECK (sorting IN ('well_sorted','moderately_sorted','poorly_sorted')),
    profile_geom     GEOMETRY(LineString, 4326),     -- Vertical profile trace
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(observation_id, profile_code)
);

CREATE INDEX idx_soil_profiles_obs ON soil_profiles(observation_id);

-- -----------------------------------------------------------
-- 2. SOIL HORIZONS - Multiple horizons per profile
-- -----------------------------------------------------------
CREATE TABLE soil_horizons (
    horizon_id       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    soil_profile_id  UUID NOT NULL REFERENCES soil_profiles(soil_profile_id) ON DELETE CASCADE,
    horizon_label    VARCHAR(10) NOT NULL,             -- A, B, C, etc.
    depth_from_m     DECIMAL(6,2) NOT NULL,
    depth_to_m       DECIMAL(6,2) NOT NULL,
    color            VARCHAR(50),                     -- Munsell or descriptive
    consistency      VARCHAR(30) CHECK (consistency IN ('soft','firm','stiff','very_stiff','hard','loose','very_loose','dense','very_dense')),
    moisture         VARCHAR(30) CHECK (moisture IN ('dry','slightly_moist','moist','wet','very_wet')),
    grain_size       VARCHAR(30) CHECK (grain_size IN ('clay','silt','fine_sand','medium_sand','coarse_sand','gravel','cobble','boulder')),
    plasticity       VARCHAR(30) CHECK (plasticity IN ('non_plastic','low_CL','medium_CI','high_CH')),
    uscs_class       VARCHAR(10) CHECK (uscs_class IN ('CL','CI','CH','SM','SC','SP','SW','GC','GM','GP','GW','ML','MH','PT','OL','OH')),
    organic_content  VARCHAR(20),
    root_content     VARCHAR(50),
    boundary_type    VARCHAR(30) CHECK (boundary_type IN ('abrupt','clear','gradual','diffuse')),
    structure_type   VARCHAR(50),                     -- Granular, blocky, prismatic, etc.
    notes            TEXT,
    sort_order       INTEGER DEFAULT 0,
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_soil_horizons_profile ON soil_horizons(soil_profile_id);
CREATE INDEX idx_soil_horizons_depth ON soil_horizons(depth_from_m, depth_to_m);

-- -----------------------------------------------------------
-- 3. ROCK DESCRIPTIONS - Linked to observations
-- -----------------------------------------------------------
CREATE TABLE rock_descriptions (
    rock_desc_id     UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    rock_name        VARCHAR(100) NOT NULL,            -- Basalt, Granite, etc.
    rock_type        VARCHAR(30) CHECK (rock_type IN ('igneous_volcanic','igneous_plutonic','sedimentary','metamorphic','pyroclastic','hydrothermal')),
    color            VARCHAR(50),
    grain_size       VARCHAR(30) CHECK (grain_size IN ('fine_grained','medium_grained','coarse_grained','aphanitic','porphyritic','phaneritic','pegmatitic')),
    texture          VARCHAR(50),                      -- Massive, foliated, vesicular, etc.
    weathering_grade INTEGER CHECK (weathering_grade BETWEEN 1 AND 6),
    weathering_desc  VARCHAR(50),                      -- Unweathered to Residual Soil
    intact_strength  INTEGER CHECK (intact_strength BETWEEN 1 AND 7),
    strength_desc    VARCHAR(50),                      -- Very Weak to Extremely Strong
    structure        VARCHAR(50) CHECK (structure IN ('massive','foliated','vesicular','amygdaloidal','laminated','bedded','schistose','gneissose')),
    mineralogy      TEXT,
    alteration      VARCHAR(100),
    rock_description TEXT,                             -- Free-form detailed description
    geom             GEOMETRY(Point, 4326),            -- Rock sample location
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_rock_desc_obs ON rock_descriptions(observation_id);
CREATE INDEX idx_rock_desc_geom ON rock_descriptions USING GIST(geom);
CREATE INDEX idx_rock_desc_name ON rock_descriptions(rock_name);
CREATE INDEX idx_rock_desc_type ON rock_descriptions(rock_type);

-- -----------------------------------------------------------
-- 4. LITHOLOGICAL UNITS - Geological map integration
-- -----------------------------------------------------------
CREATE TABLE lithological_units (
    litho_unit_id    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    unit_code        VARCHAR(30) UNIQUE,               -- e.g. Qa, Tvb, Pzs
    unit_name        VARCHAR(255) NOT NULL,
    rock_type        VARCHAR(50),
    age_era          VARCHAR(50),                      -- Cenozoic, Mesozoic, etc.
    age_period       VARCHAR(50),                      -- Quaternary, Tertiary, etc.
    age_epoch        VARCHAR(50),                      -- Pleistocene, Holocene, etc.
    description      TEXT,
    geom             GEOMETRY(MultiPolygon, 4326),     -- Outcrop polygon
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_litho_units_geom ON lithological_units USING GIST(geom);
CREATE INDEX idx_litho_units_code ON lithological_units(unit_code);