-- ============================================================
-- EIGIS Theme 2: Structural Features
-- Discontinuities, slope stability, rock mass classification
-- Multiple structural measurements per observation
-- ============================================================

-- -----------------------------------------------------------
-- 1. DISCONTINUITY MEASUREMENTS
-- Multiple sets per observation
-- -----------------------------------------------------------
CREATE TABLE discontinuity_measurements (
    disc_id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    set_label        VARCHAR(20) NOT NULL,              -- J1, J2, F1, etc.
    disc_type        VARCHAR(30) NOT NULL CHECK (disc_type IN (
        'joint','fault','bedding','foliation','shear_zone',
        'tension_crack','cleavage','vein','dyke','contact'
    )),
    dip_direction_deg DECIMAL(5,1) CHECK (dip_direction_deg BETWEEN 0 AND 360),
    dip_deg          DECIMAL(4,1) CHECK (dip_deg BETWEEN 0 AND 90),
    strike_deg       DECIMAL(5,1) GENERATED ALWAYS AS (
        CASE WHEN dip_direction_deg IS NOT NULL
             THEN (dip_direction_deg + 90)::DECIMAL(5,1) % 360
             ELSE NULL
        END
    ) STORED,
    spacing_m        DECIMAL(6,3),
    aperture_mm      DECIMAL(6,2),
    persistence_m    DECIMAL(6,2),
    roughness        VARCHAR(30) CHECK (roughness IN ('very_rough','rough','slightly_rough','smooth','slickensided','polished')),
    infill_material  VARCHAR(50) CHECK (infill_material IN ('none','clay','calcite','quartz','chlorite','kaolin','hematite','gouge','breccia')),
    infill_thickness_mm DECIMAL(5,2),
    water_condition   VARCHAR(30) CHECK (water_condition IN ('dry','damp','wet','flowing','seeping')),
    wall_weathering   INTEGER CHECK (wall_weathering BETWEEN 1 AND 5),
    geom             GEOMETRY(Point, 4326),            -- Measurement point
    line_geom        GEOMETRY(LineString, 4326),       -- Discontinuity trace line
    sort_order       INTEGER DEFAULT 0,
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_disc_obs ON discontinuity_measurements(observation_id);
CREATE INDEX idx_disc_geom ON discontinuity_measurements USING GIST(geom);
CREATE INDEX idx_disc_type ON discontinuity_measurements(disc_type);
CREATE INDEX idx_disc_dip ON discontinuity_measurements(dip_direction_deg, dip_deg);
CREATE INDEX idx_disc_line ON discontinuity_measurements USING GIST(line_geom);

-- -----------------------------------------------------------
-- 2. SLOPE STABILITY ASSESSMENTS
-- -----------------------------------------------------------
CREATE TABLE slope_stability (
    slope_id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    has_existing_slope BOOLEAN DEFAULT TRUE,
    slope_height_m   DECIMAL(8,2),
    slope_angle_deg  DECIMAL(5,1),
    aspect_deg       DECIMAL(5,1),
    slope_form       VARCHAR(30) CHECK (slope_form IN ('concave','convex','planar','stepped','composite')),
    stability_condition VARCHAR(30) CHECK (stability_condition IN ('stable','small_problems','large_problems','failed','critical')),
    hazard_level     VARCHAR(20) CHECK (hazard_level IN ('low','moderate','high','very_high','extreme')),
    failure_mode     VARCHAR(50) CHECK (failure_mode IN ('circular','planar','wedge','toppling','fall','flow','spreading','complex')),
    factor_of_safety DECIMAL(6,3),
    trigger_mechanism VARCHAR(100),
    volume_estimate_m3 DECIMAL(12,2),
    runout_distance_m DECIMAL(8,2),
    recommended_actions TEXT,
    mitigation_measures TEXT,
    monitoring_required BOOLEAN DEFAULT FALSE,
    slope_geom       GEOMETRY(LineString, 4326),       -- Slope profile line
    failure_geom     GEOMETRY(Polygon, 4326),          -- Failure area polygon
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_slope_obs ON slope_stability(observation_id);
CREATE INDEX idx_slope_hazard ON slope_stability(hazard_level);
CREATE INDEX idx_slope_geom ON slope_stability USING GIST(slope_geom);
CREATE INDEX idx_slope_failure ON slope_stability USING GIST(failure_geom);

-- -----------------------------------------------------------
-- 3. ROCK MASS CLASSIFICATION (RMR / Q / GSI)
-- -----------------------------------------------------------
CREATE TABLE rock_mass_classifications (
    classification_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id    UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    -- RMR89 Parameters
    rmr_strength      INTEGER CHECK (rmr_strength BETWEEN 0 AND 15),
    rmr_drill_quality INTEGER CHECK (rmr_drill_quality BETWEEN 3 AND 20),
    rmr_spacing       INTEGER CHECK (rmr_spacing BETWEEN 5 AND 20),
    rmr_condition     INTEGER CHECK (rmr_condition BETWEEN 0 AND 30),
    rmr_groundwater   INTEGER CHECK (rmr_groundwater BETWEEN 0 AND 15),
    rmr_adjustment    INTEGER CHECK (rmr_adjustment BETWEEN -15 AND 0),
    rmr_total         INTEGER CHECK (rmr_total BETWEEN 0 AND 100),
    rmr_class         VARCHAR(30),                     -- I through V
    -- Q-System Parameters
    q_rqd            DECIMAL(5,1) CHECK (q_rqd BETWEEN 0 AND 100),
    q_jn             DECIMAL(5,1) CHECK (q_jn BETWEEN 0.5 AND 20),
    q_jr             DECIMAL(5,1) CHECK (q_jr BETWEEN 0.1 AND 4),
    q_ja             DECIMAL(5,1) CHECK (q_ja BETWEEN 0.1 AND 20),
    q_jw             DECIMAL(5,1) CHECK (q_jw BETWEEN 0.05 AND 1),
    q_srf            DECIMAL(5,1) CHECK (q_srf BETWEEN 0.5 AND 400),
    q_value          DECIMAL(10,4),
    q_class          VARCHAR(30),
    -- GSI
    gsi_value        INTEGER CHECK (gsi_value BETWEEN 5 AND 100),
    gsi_structure    VARCHAR(50),
    gsi_surface      VARCHAR(50),
    -- Computed
    emod_gpa         DECIMAL(8,2),                    -- Deformation modulus
    cohesion_mpa     DECIMAL(6,3),
    friction_angle_deg DECIMAL(5,1),
    notes            TEXT,
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_rmc_obs ON rock_mass_classifications(observation_id);
CREATE INDEX idx_rmc_rmr ON rock_mass_classifications(rmr_total);
CREATE INDEX idx_rmc_q ON rock_mass_classifications(q_value);

-- -----------------------------------------------------------
-- 4. LANDSLIDE INVENTORY
-- -----------------------------------------------------------
CREATE TABLE landslide_inventory (
    landslide_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),
    landslide_code   VARCHAR(50) UNIQUE,
    landslide_type   VARCHAR(50) NOT NULL CHECK (landslide_type IN (
        'rotational_slide','translational_slide','block_slide',
        'rockfall','debris_flow','earthflow','mudflow',
        'lateral_spreading','complex','toppling','creep'
    )),
    activity_state   VARCHAR(30) CHECK (activity_state IN ('active','suspended','reactivated','dormant','stabilized','relic')),
    movement_rate    VARCHAR(30),                      -- mm/year, m/year, etc.
    depth_estimated_m DECIMAL(6,2),
    area_m2          DECIMAL(12,2),
    volume_m3       DECIMAL(12,2),
    casualties       INTEGER DEFAULT 0,
    damage_desc      TEXT,
    causative_factors TEXT[],
    geom             GEOMETRY(Polygon, 4326) NOT NULL, -- Landslide boundary
    source_geom      GEOMETRY(Point, 4326),            -- Crown/source point
    deposit_geom     GEOMETRY(Point, 4326),            -- Toe/deposit point
    threat_level     VARCHAR(20) CHECK (threat_level IN ('low','moderate','high','very_high','extreme')),
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_landslide_geom ON landslide_inventory USING GIST(geom);
CREATE INDEX idx_landslide_source ON landslide_inventory USING GIST(source_geom);
CREATE INDEX idx_landslide_project ON landslide_inventory(project_id);
CREATE INDEX idx_landslide_type ON landslide_inventory(landslide_type);
CREATE INDEX idx_landslide_activity ON landslide_inventory(activity_state);
