schema.sql

-- ==============================================================================
-- 0. TIMESCALEDB E RESET
-- ==============================================================================
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- ==============================================================================
-- 1. UTENTI E PERMESSI BASE
-- ==============================================================================

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    global_role VARCHAR(50) NOT NULL DEFAULT 'COLLABORATOR'
); 

CREATE TABLE permissions (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    category VARCHAR(100) NOT NULL,
    description VARCHAR(255)
);
-- ==============================================================================
-- 2. DIZIONARIO PARAMETRI (Deve esistere prima dei dispositivi)
-- ==============================================================================

CREATE TABLE parameter_dictionary (
    id BIGSERIAL PRIMARY KEY,
    parent_id BIGINT REFERENCES parameter_dictionary(id) ON DELETE CASCADE,
    sensor_group VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    json_key VARCHAR(50) NOT NULL,
    data_type VARCHAR(50) NOT NULL,
    description TEXT,
    requires_frequency BOOLEAN DEFAULT FALSE,
    requires_epoch BOOLEAN DEFAULT FALSE,
    UNIQUE (sensor_group, json_key)
);

CREATE TABLE parameter_dependencies (
    parameter_id BIGINT REFERENCES parameter_dictionary(id) ON DELETE CASCADE,
    requires_parameter_id BIGINT REFERENCES parameter_dictionary(id) ON DELETE CASCADE,
    PRIMARY KEY (parameter_id, requires_parameter_id)
);

-- ==============================================================================
-- 3. CATALOGO HARDWARE E CAPABILITY
-- ==============================================================================

CREATE TABLE device_models (
    id BIGSERIAL PRIMARY KEY,
    manufacturer VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE device_supported_parameters (
    device_id BIGINT REFERENCES device_models(id) ON DELETE CASCADE,
    parameter_id BIGINT REFERENCES parameter_dictionary(id) ON DELETE CASCADE,
    PRIMARY KEY (device_id, parameter_id)
);

-- ==============================================================================
-- 4. STUDI (MULTI-TENANT) E RUOLI
-- ==============================================================================

CREATE TABLE studies (
    id BIGSERIAL PRIMARY KEY,
    unique_code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    owner_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    device_id BIGINT REFERENCES device_models(id) ON DELETE RESTRICT,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    description TEXT,
    app_show_data BOOLEAN DEFAULT FALSE,
    app_show_goals BOOLEAN DEFAULT FALSE,
    app_show_tasks BOOLEAN DEFAULT FALSE
);

CREATE TABLE roles (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    study_id BIGINT REFERENCES studies(id) ON DELETE CASCADE, -- Se NULL = Ruolo Globale
    is_custom BOOLEAN DEFAULT FALSE,
    is_system BOOLEAN DEFAULT FALSE,
    UNIQUE (study_id, name)
);

CREATE TABLE role_permissions (
    role_id BIGINT REFERENCES roles(id) ON DELETE CASCADE,
    permission_id BIGINT REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE study_members (
    study_id BIGINT REFERENCES studies(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role_id BIGINT REFERENCES roles(id) ON DELETE CASCADE,
    is_principal_investigator BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (study_id, user_id)
);

CREATE INDEX ix_study_members_user ON study_members(user_id);

-- ==============================================================================
-- 5. CONFIGURAZIONE DISPOSITIVI PER STUDIO
-- ==============================================================================

CREATE TABLE study_device_configurations (
    id BIGSERIAL PRIMARY KEY,
    study_id BIGINT REFERENCES studies(id) ON DELETE CASCADE,
    parameter_id BIGINT REFERENCES parameter_dictionary(id) ON DELETE CASCADE,
    is_enabled BOOLEAN DEFAULT FALSE,
    frequency_hz DOUBLE PRECISION CHECK (frequency_hz IS NULL OR frequency_hz > 0),
    epoch_seconds INT CHECK (epoch_seconds IS NULL OR epoch_seconds > 0),

    UNIQUE (study_id, parameter_id)
);

-- ==============================================================================
-- 6. SOGGETTI E SESSIONI
-- ==============================================================================

CREATE TABLE subjects (
    id BIGSERIAL PRIMARY KEY,
    study_id BIGINT REFERENCES studies(id) ON DELETE CASCADE,
    subject_study_code VARCHAR(50) NOT NULL,
    birth_year INTEGER,
    biological_sex VARCHAR(10),
    height DOUBLE PRECISION,
    weight DOUBLE PRECISION,
    additional_notes TEXT,
    qr_pairing_code VARCHAR(255),
    start_date DATE,
    end_date DATE,
    device_id VARCHAR(100),
    is_paired BOOLEAN DEFAULT FALSE,
    compliance_profile VARCHAR(20) DEFAULT 'ADHERENT',
    actual_start_date DATE,
    actual_end_date DATE,
    UNIQUE (study_id, subject_study_code)  
);

CREATE INDEX ix_subjects_study ON subjects(study_id);

CREATE TABLE subject_sessions (
    id BIGSERIAL PRIMARY KEY,
    subject_id BIGINT REFERENCES subjects(id) ON DELETE CASCADE,
    api_key_hash VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    is_revoked BOOLEAN DEFAULT FALSE,
    CHECK (expires_at > created_at)
);

CREATE INDEX ix_subject_sessions_subject ON subject_sessions(subject_id);

-- ==============================================================================
-- 7. GRUPPI E TAG (TENANT-SAFE VIA study_id su Entity, non su DB)
-- ==============================================================================

CREATE TABLE subject_groups (
    id BIGSERIAL PRIMARY KEY,
    study_id BIGINT REFERENCES studies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    UNIQUE (study_id, name)
);

CREATE TABLE subject_group_members (
    group_id BIGINT REFERENCES subject_groups(id) ON DELETE CASCADE,
    subject_id BIGINT REFERENCES subjects(id) ON DELETE CASCADE,
    PRIMARY KEY (group_id, subject_id)
);

CREATE TABLE tags (
    id BIGSERIAL PRIMARY KEY,
    study_id BIGINT REFERENCES studies(id) ON DELETE CASCADE,
    color VARCHAR(20),
    name VARCHAR(50) NOT NULL,
    UNIQUE (study_id, name)
);

CREATE TABLE subject_tags (
    subject_id BIGINT REFERENCES subjects(id) ON DELETE CASCADE,
    tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (subject_id, tag_id)
);

-- ==============================================================================
-- 8. TIMESCALEDB – DATI FISIOLOGICI
-- ==============================================================================

CREATE TABLE physiological_data (
    time TIMESTAMPTZ NOT NULL,
    subject_id BIGINT REFERENCES subjects(id) ON DELETE CASCADE,
    metrics JSONB NOT NULL
);

SELECT create_hypertable('physiological_data', 'time', if_not_exists => TRUE);

CREATE INDEX ix_physio_subject_time
ON physiological_data (subject_id, time DESC);

CREATE INDEX ix_physio_metrics_jsonb
ON physiological_data USING GIN (metrics);

ALTER TABLE physiological_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'subject_id'
);
SELECT add_compression_policy('physiological_data', INTERVAL '7 days');

TRUNCATE TABLE permissions, roles CASCADE;

INSERT INTO permissions (name, category, description) VALUES 
('data_acquisition', 'Impostazioni Progetto', 'Gestisci il protocollo di acquisizione dati'),
('members_roles', 'Impostazioni Progetto', 'Gestisci i membri del team e i ruoli'),
('general_settings', 'Impostazioni Progetto', 'Modifica le impostazioni generali del progetto');

INSERT INTO permissions (name, category, description) VALUES 
('manage_subjects', 'Gestione Progetto', 'Registra o rimuovi i soggetti dal progetto'),
('compliance_monitoring', 'Gestione Progetto', 'Monitora l''aderenza dei soggetti'),
('device_disconnections', 'Gestione Progetto', 'Gestisci le disconnessioni dei dispositivi');

INSERT INTO permissions (name, category, description) VALUES 
('data_exports', 'Dati', 'Esporta i dati fisiologici grezzi'),
('standard_analysis', 'Dati', 'Visualizza le dashboard di analisi standard');

INSERT INTO roles (name, study_id, is_custom, is_system) VALUES 
('Project Manager', NULL, FALSE, TRUE),
('Operator', NULL, FALSE, TRUE),
('Data Analyst', NULL, FALSE, TRUE);


INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id 
FROM roles r, permissions p 
WHERE r.name = 'Project Manager';

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id 
FROM roles r, permissions p 
WHERE r.name = 'Operator' 
AND p.name IN ('manage_subjects', 'compliance_monitoring', 'device_disconnections', 'data_exports');

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id 
FROM roles r, permissions p 
WHERE r.name = 'Data Analyst' 
AND p.name IN ('data_exports', 'standard_analysis', 'compliance_monitoring');


-- ==============================================================================
-- 10. POPOLAMENTO PARAMETRI E GERARCHIA (Albero Padre-Figlio)
-- ==============================================================================
INSERT INTO parameter_dictionary (sensor_group, name, json_key, data_type, description, requires_frequency, requires_epoch) VALUES 
('SENSORE OTTICO (PPG)', 'Beat-to-Beat Interval (BBI)', 'bbi', 'RAW', 'Intervallo R-R tra i battiti', FALSE, FALSE), -- Il BBI è ad evento, non ha Hz o epoche
('SENSORE OTTICO (PPG)', 'Heart Rate Variability (HRV)', 'hrv', 'CALCULATED', 'Variabilità della frequenza cardiaca', FALSE, TRUE),
('SENSORE OTTICO (PPG)', 'Stress Level', 'stress_level', 'MODEL', 'Livello di stress fisiologico', FALSE, TRUE),
('SENSORE OTTICO (PPG)', 'Body Battery', 'body_battery', 'MODEL', 'Riserva energetica corporea stimata', FALSE, TRUE),
('SENSORE OTTICO (PPG)', 'Respiration Rate', 'respiration_rate', 'ESTIMATED', 'Atti respiratori al minuto', FALSE, TRUE),
('SENSORE OTTICO (PPG)', 'Heart Rate (BPM)', 'hr_bpm', 'CALCULATED', 'Frequenza cardiaca in battiti al minuto', FALSE, TRUE),
('SENSORE OTTICO (PPG)', 'Resting HR (RHR)', 'rhr', 'CALCULATED', 'Frequenza cardiaca a riposo', FALSE, FALSE), -- Valore singolo giornaliero
('SENSORE OTTICO (PPG)', 'Intensity Minutes', 'intensity_minutes', 'MODEL', 'Minuti di attività fisica intensa', FALSE, FALSE),
('SENSORE OTTICO (PPG)', 'Calories', 'calories', 'ESTIMATED', 'Calorie bruciate stimate', FALSE, FALSE),
('SENSORE OTTICO (PPG)', 'VO2 Max', 'vo2_max', 'ESTIMATED', 'Massimo consumo di ossigeno', FALSE, FALSE),
('ACCELEROMETRO (Motion Sensor)', '3-Axis Acceleration', 'accel_3_axis', 'RAW', 'Dati grezzi di movimento X, Y, Z', FALSE, FALSE), -- L'accelerometro grezzo VUOLE gli Hertz!
('ACCELEROMETRO (Motion Sensor)', 'Steps Count', 'steps_count', 'CALCULATED', 'Conteggio passi giornalieri', FALSE, TRUE), -- Epoca (es. somma ogni 60 sec o 3600 sec)
('ACCELEROMETRO (Motion Sensor)', 'Distance', 'distance', 'ESTIMATED', 'Distanza stimata percorsa', FALSE, TRUE),
('ACCELEROMETRO (Motion Sensor)', 'Cadence', 'cadence', 'CALCULATED', 'Passi al minuto', FALSE, TRUE),
('ACCELEROMETRO (Motion Sensor)', 'Sleep Monitoring', 'sleep_monitoring', 'MODEL', 'Fasi del sonno modellate', FALSE, FALSE), -- Evento asincrono
('PULSOSSIMETRO (Red/IR Sensor)', 'PPG Waveform (Red/IR)', 'ppg_red_ir', 'RAW', 'Onda pletismografica grezza', FALSE, FALSE), -- Il sensore grezzo ottico VUOLE gli Hertz
('PULSOSSIMETRO (Red/IR Sensor)', 'SpO2 %', 'spo2_percent', 'CALCULATED', 'Percentuale di ossigenazione del sangue', FALSE, TRUE),
('PULSOSSIMETRO (Red/IR Sensor)', 'Sleep SpO2 (Avg/Min)', 'sleep_spo2', 'MODEL', 'Andamento SpO2 durante il sonno', FALSE, TRUE);

-- ...e poi costruiamo la gerarchia con UPDATE mirati!
UPDATE parameter_dictionary SET parent_id = (SELECT id FROM parameter_dictionary WHERE json_key = 'bbi') 
WHERE json_key IN ('hrv', 'stress_level', 'body_battery', 'respiration_rate', 'hr_bpm');

UPDATE parameter_dictionary SET parent_id = (SELECT id FROM parameter_dictionary WHERE json_key = 'hr_bpm') 
WHERE json_key IN ('rhr', 'intensity_minutes', 'calories', 'vo2_max');

UPDATE parameter_dictionary SET parent_id = (SELECT id FROM parameter_dictionary WHERE json_key = 'accel_3_axis') 
WHERE json_key IN ('steps_count', 'sleep_monitoring');

UPDATE parameter_dictionary SET parent_id = (SELECT id FROM parameter_dictionary WHERE json_key = 'steps_count') 
WHERE json_key IN ('distance', 'cadence');

UPDATE parameter_dictionary SET parent_id = (SELECT id FROM parameter_dictionary WHERE json_key = 'ppg_red_ir') 
WHERE json_key IN ('spo2_percent', 'sleep_spo2');

-- ==============================================================================
-- 11. CREAZIONE DELLE DIPENDENZE (I "Warning" dell'interfaccia)
-- ==============================================================================
INSERT INTO parameter_dependencies (parameter_id, requires_parameter_id) VALUES 
((SELECT id FROM parameter_dictionary WHERE json_key = 'sleep_monitoring'), (SELECT id FROM parameter_dictionary WHERE json_key = 'hr_bpm')),
((SELECT id FROM parameter_dictionary WHERE json_key = 'sleep_spo2'), (SELECT id FROM parameter_dictionary WHERE json_key = 'accel_3_axis')),
((SELECT id FROM parameter_dictionary WHERE json_key = 'stress_level'), (SELECT id FROM parameter_dictionary WHERE json_key = 'accel_3_axis')),
((SELECT id FROM parameter_dictionary WHERE json_key = 'body_battery'), (SELECT id FROM parameter_dictionary WHERE json_key = 'accel_3_axis')),
((SELECT id FROM parameter_dictionary WHERE json_key = 'body_battery'), (SELECT id FROM parameter_dictionary WHERE json_key = 'spo2_percent')),
((SELECT id FROM parameter_dictionary WHERE json_key = 'rhr'), (SELECT id FROM parameter_dictionary WHERE json_key = 'accel_3_axis')),
((SELECT id FROM parameter_dictionary WHERE json_key = 'intensity_minutes'), (SELECT id FROM parameter_dictionary WHERE json_key = 'hr_bpm')),
((SELECT id FROM parameter_dictionary WHERE json_key = 'calories'), (SELECT id FROM parameter_dictionary WHERE json_key = 'accel_3_axis'));


INSERT INTO device_models (manufacturer, name, description) VALUES 
('Garmin', 'Vivosmart 5', 'Dispositivo di riferimento per la tesi');

INSERT INTO device_supported_parameters (device_id, parameter_id)
SELECT d.id, p.id 
FROM device_models d, parameter_dictionary p 
WHERE d.name = 'Vivosmart 5';

CREATE TABLE export_jobs (
    id BIGSERIAL PRIMARY KEY,
    study_id BIGINT REFERENCES studies(id) ON DELETE CASCADE,
    requested_by BIGINT REFERENCES users(id) ON DELETE SET NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING', 
    progress INT DEFAULT 0,
    current_step VARCHAR(255),
    format VARCHAR(10) NOT NULL, 
    file_url VARCHAR(500), 
    error_message TEXT,
    config_json JSONB NOT NULL, 
    created_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);
CREATE INDEX ix_export_jobs_study ON export_jobs(study_id);

CREATE TABLE notification_protocols (
    id BIGSERIAL PRIMARY KEY,
    study_id BIGINT NOT NULL REFERENCES studies(id) ON DELETE CASCADE,
    creator_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    subject_title VARCHAR(255),
    message TEXT NOT NULL,
    is_push BOOLEAN NOT NULL DEFAULT FALSE,
    is_banner BOOLEAN NOT NULL DEFAULT FALSE,
    schedule_type VARCHAR(50) NOT NULL,
    times_of_day JSONB NOT NULL DEFAULT '[]'::jsonb,
    fixed_dates JSONB NOT NULL DEFAULT '[]'::jsonb,
    relative_offset_days INTEGER,
    relative_duration_days INTEGER,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE protocol_subjects (
    protocol_id BIGINT REFERENCES notification_protocols(id) ON DELETE CASCADE,
    subject_id BIGINT REFERENCES subjects(id) ON DELETE CASCADE,
    
    simulated_sent INT DEFAULT 0,
    simulated_opened INT DEFAULT 0,
    
    PRIMARY KEY (protocol_id, subject_id)
);

CREATE TABLE study_alert_rules (
    id BIGSERIAL PRIMARY KEY,
    study_id BIGINT NOT NULL UNIQUE REFERENCES studies(id) ON DELETE CASCADE,
    
    -- Low Wearing Time
    low_wearing_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    low_wearing_threshold INT NOT NULL DEFAULT 4,
    low_wearing_check_period VARCHAR(50) NOT NULL DEFAULT 'EVERY_DAY',
    low_wearing_check_time TIME NOT NULL DEFAULT '09:00',
    
    -- Silent Device
    silent_device_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    silent_device_gap_hours INT NOT NULL DEFAULT 6,
    
    -- Device Offline
    device_offline_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    device_offline_hours INT NOT NULL DEFAULT 2
);