-- ============================================================
-- EIGIS Core Tables: Projects, Field Trips, Personnel
-- Normalized schema supporting multiple observations per trip,
-- multiple samples per observation, GIS integration,
-- mobile data collection, and future expansion.
-- ============================================================

-- -----------------------------------------------------------
-- 1. PROJECTS - Top-level organizational unit
-- -----------------------------------------------------------
CREATE TABLE projects (
    project_id       UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_code     VARCHAR(50) NOT NULL UNIQUE,  -- e.g. EIGIS-GIE-2026-001
    project_name     VARCHAR(255) NOT NULL,
    description      TEXT,
    client_name      VARCHAR(255),
    client_email     VARCHAR(255),
    receptionist_email VARCHAR(255),
    region           VARCHAR(100),
    start_date       DATE NOT NULL,
    end_date         DATE,
    status           VARCHAR(20) DEFAULT 'active' CHECK (status IN ('planning','active','paused','completed','cancelled')),
    boundary_geom    GEOMETRY(Polygon, 4326),      -- Project AOI boundary
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_projects_boundary ON projects USING GIST(boundary_geom);
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_code ON projects(project_code);

-- -----------------------------------------------------------
-- 2. PERSONNEL - Team members and data loggers
-- -----------------------------------------------------------
CREATE TABLE personnel (
    personnel_id     UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    personnel_code   VARCHAR(20) UNIQUE,           -- e.g. TH-001
    full_name        VARCHAR(255) NOT NULL,
    email            VARCHAR(255),
    phone            VARCHAR(50),
    role             VARCHAR(50) CHECK (role IN ('geologist','engineer','technician','manager','surveyor','student')),
    organization     VARCHAR(255) DEFAULT 'GIE',
    certification    VARCHAR(100),
    is_active        BOOLEAN DEFAULT TRUE,
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

-- -----------------------------------------------------------
-- 3. FIELD TRIPS - Organized data collection campaigns
-- -----------------------------------------------------------
CREATE TABLE field_trips (
    field_trip_id    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id       UUID NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
    trip_code        VARCHAR(50) NOT NULL,          -- e.g. FT-2026-001
    trip_date        DATE NOT NULL,
    leader_id        UUID REFERENCES personnel(personnel_id),
    team_members     UUID[],                         -- Array of personnel IDs
    weather_condition VARCHAR(50),
    vehicle_info     VARCHAR(100),
    area_visited     VARCHAR(255),
    route_geom       GEOMETRY(LineString, 4326),     -- GPS track of the day
    notes            TEXT,
    sync_status      VARCHAR(20) DEFAULT 'pending' CHECK (sync_status IN ('pending','synced','conflict','error')),
    device_id        VARCHAR(100),                    -- Mobile device identifier
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(project_id, trip_code)
);

CREATE INDEX idx_field_trips_project ON field_trips(project_id);
CREATE INDEX idx_field_trips_date ON field_trips(trip_date);
CREATE INDEX idx_field_trips_route ON field_trips USING GIST(route_geom);
CREATE INDEX idx_field_trips_sync ON field_trips(sync_status);

-- -----------------------------------------------------------
-- 4. OBSERVATIONS - Core spatial data unit
-- Each field trip has multiple observation points
-- -----------------------------------------------------------
CREATE TABLE observations (
    observation_id   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    field_trip_id    UUID NOT NULL REFERENCES field_trips(field_trip_id) ON DELETE CASCADE,
    site_id          VARCHAR(50) NOT NULL,            -- e.g. TP-001-A, EXP-047
    observation_type VARCHAR(50) NOT NULL CHECK (observation_type IN (
        'test_pit','gully','slope_cut','river_valley','landslide',
        'quarry_face','road_cut','natural_exposure','geothermal_spring',
        'fumarole','hot_spring','geyser','mineral_deposit','other'
    )),
    exposure_type    VARCHAR(50),
    geom             GEOMETRY(Point, 4326) NOT NULL,  -- Primary spatial location
    elevation_m      DECIMAL(10,2),
    easting          DECIMAL(12,2),
    northing         DECIMAL(12,2),
    utm_zone         VARCHAR(5) DEFAULT '37N',
    epsg_code        INTEGER DEFAULT 4326,
    exposure_length_m DECIMAL(8,2),
    exposure_height_m DECIMAL(8,2),
    groundwater_level_m DECIMAL(8,2),
    weather_condition VARCHAR(50),
    excavation_method VARCHAR(50),
    accessibility    VARCHAR(20) CHECK (accessibility IN ('good','fair','poor')),
    admin_unit       VARCHAR(255),                    -- Auto-populated from GIS
    watershed        VARCHAR(255),                    -- Auto-populated from DEM
    geological_formation VARCHAR(255),               -- Auto-populated from Geo Map
    landslide_inventory_ref VARCHAR(100),             -- Auto from Geohazard DB
    remarks          TEXT,
    observation_date TIMESTAMPTZ DEFAULT NOW(),
    logger_id        UUID REFERENCES personnel(personnel_id),
    status           VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft','saved','submitted','reviewed','approved')),
    created_at       TIMESTAMPTZ DEFAULT NOW(),
    updated_at       TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(field_trip_id, site_id)
);

-- Critical spatial indexes for low-latency rendering
CREATE INDEX idx_observations_geom ON observations USING GIST(geom);
CREATE INDEX idx_observations_geom_3d ON observations USING GIST(geom)
    WHERE elevation_m IS NOT NULL;
CREATE INDEX idx_observations_type ON observations(observation_type);
CREATE INDEX idx_observations_trip ON observations(field_trip_id);
CREATE INDEX idx_observations_status ON observations(status);
CREATE INDEX idx_observations_site ON observations(site_id);
CREATE INDEX idx_observations_composite ON observations USING GIST(geom, observation_type);

-- -----------------------------------------------------------
-- 5. PHOTO DOCUMENTATION - Linked to any observation
-- -----------------------------------------------------------
CREATE TABLE photos (
    photo_id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    observation_id   UUID NOT NULL REFERENCES observations(observation_id) ON DELETE CASCADE,
    photo_type       VARCHAR(50) CHECK (photo_type IN (
        'exposure_overview','slope_photo','discontinuity_set',
        'sample_photo','structural_feature','geothermal_feature',
        'general','panoramic'
    )),
    file_path        VARCHAR(500) NOT NULL,           -- MinIO/S3 path
    file_size_kb     INTEGER,
    mime_type        VARCHAR(50),
    geom             GEOMETRY(Point, 4326),           -- Geotagged location
    azimuth_deg      DECIMAL(5,2),                    -- Camera direction
    caption          VARCHAR(500),
    taken_at         TIMESTAMPTZ DEFAULT NOW(),
    synced           BOOLEAN DEFAULT FALSE,
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_photos_observation ON photos(observation_id);
CREATE INDEX idx_photos_geom ON photos USING GIST(geom);
CREATE INDEX idx_photos_type ON photos(photo_type);

-- -----------------------------------------------------------
-- 6. AUDIT LOG - Track all changes for data integrity
-- -----------------------------------------------------------
CREATE TABLE audit_log (
    audit_id         BIGSERIAL PRIMARY KEY,
    table_name       VARCHAR(100) NOT NULL,
    record_id        UUID NOT NULL,
    action           VARCHAR(10) NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
    old_data         JSONB,
    new_data         JSONB,
    changed_by       VARCHAR(255),
    changed_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_table ON audit_log(table_name);
CREATE INDEX idx_audit_record ON audit_log(record_id);
CREATE INDEX idx_audit_time ON audit_log(changed_at);
