database.sql

/* ============================================================
   BioSentinel AI — Full Database Schema
   Engine:  MySQL 8.0+
   Charset: utf8mb4 (full Unicode + emoji)
   ============================================================ */

/* ---- Create & Select Database ---- */
CREATE DATABASE IF NOT EXISTS BioSentinelAI
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE BioSentinelAI;

/* ============================================================
   USERS
   Stores registered user accounts.
   ============================================================ */

CREATE TABLE IF NOT EXISTS users (
    user_id       INT            AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)    NOT NULL UNIQUE,
    email         VARCHAR(150)   NOT NULL UNIQUE,
    password_hash VARCHAR(255)   NOT NULL,
    full_name     VARCHAR(150)   DEFAULT NULL,
    role          ENUM('admin', 'researcher', 'viewer')
                                 NOT NULL DEFAULT 'researcher',
    is_active     TINYINT(1)     NOT NULL DEFAULT 1,
    last_login    TIMESTAMP      NULL DEFAULT NULL,
    created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
                                 ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_users_email    (email),
    INDEX idx_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   UPLOADS
   Each row represents one image uploaded by a user.
   ============================================================ */

CREATE TABLE IF NOT EXISTS uploads (
    upload_id     INT            AUTO_INCREMENT PRIMARY KEY,
    user_id       INT            NOT NULL,
    filename      VARCHAR(255)   NOT NULL,          -- UUID-based stored name
    original_name VARCHAR(255)   NOT NULL,          -- original user filename
    file_path     VARCHAR(512)   NOT NULL,          -- relative path on disk
    file_size     INT            NOT NULL,          -- bytes
    mime_type     VARCHAR(100)   DEFAULT NULL,
    status        ENUM('pending', 'processing', 'done', 'failed')
                                 NOT NULL DEFAULT 'pending',
    uploaded_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
                                 ON UPDATE CURRENT_TIMESTAMP,

    latitude      DECIMAL(10, 7) DEFAULT NULL,
    longitude     DECIMAL(10, 7) DEFAULT NULL,

    CONSTRAINT fk_uploads_user
        FOREIGN KEY (user_id) REFERENCES users (user_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    INDEX idx_uploads_user   (user_id),
    INDEX idx_uploads_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   ANALYSES
   One row per completed AI analysis of an upload.
   ============================================================ */

CREATE TABLE IF NOT EXISTS analyses (
    analysis_id      INT            AUTO_INCREMENT PRIMARY KEY,
    upload_id        INT            NOT NULL UNIQUE,   -- 1:1 with uploads
    user_id          INT            NOT NULL,
    total_animals    INT            NOT NULL DEFAULT 0,
    scene_summary    TEXT           DEFAULT NULL,
    recommendations  TEXT           DEFAULT NULL,
    raw_gemini_json  LONGTEXT       DEFAULT NULL,       -- full Gemini response
    yolo_detected    INT            NOT NULL DEFAULT 0, -- YOLO animal count
    yolo_json        LONGTEXT       DEFAULT NULL,       -- YOLO detection results
    ai_provider      VARCHAR(50)    NOT NULL DEFAULT 'gemini',
    analyzed_at      TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_analyses_upload
        FOREIGN KEY (upload_id) REFERENCES uploads (upload_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_analyses_user
        FOREIGN KEY (user_id) REFERENCES users (user_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    INDEX idx_analyses_user (user_id),
    INDEX idx_analyses_date (analyzed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   DETECTED_ANIMALS
   Each row is one species detected within an analysis.
   ============================================================ */

CREATE TABLE IF NOT EXISTS detected_animals (
    animal_id           INT            AUTO_INCREMENT PRIMARY KEY,
    analysis_id         INT            NOT NULL,
    common_name         VARCHAR(150)   NOT NULL,
    scientific_name     VARCHAR(200)   DEFAULT NULL,
    count               INT            NOT NULL DEFAULT 1,
    confidence          DECIMAL(5, 4)  DEFAULT NULL,   -- 0.0000 – 1.0000
    conservation_status VARCHAR(100)   DEFAULT NULL,   -- e.g. 'Endangered'
    bounding_boxes      LONGTEXT       DEFAULT NULL,   -- JSON array from YOLO

    CONSTRAINT fk_da_analysis
        FOREIGN KEY (analysis_id) REFERENCES analyses (analysis_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    INDEX idx_da_analysis (analysis_id),
    INDEX idx_da_name     (common_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   REPORTS
   Tracks generated report files (PDF / CSV / Excel).
   ============================================================ */

CREATE TABLE IF NOT EXISTS reports (
    report_id     INT            AUTO_INCREMENT PRIMARY KEY,
    user_id       INT            NOT NULL,
    analysis_id   INT            NOT NULL,
    report_type   ENUM('pdf', 'csv', 'excel', 'conservation')
                                 NOT NULL,
    file_path     VARCHAR(512)   NOT NULL,
    file_size     INT            DEFAULT NULL,          -- bytes
    generated_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_reports_user
        FOREIGN KEY (user_id) REFERENCES users (user_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_reports_analysis
        FOREIGN KEY (analysis_id) REFERENCES analyses (analysis_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    INDEX idx_reports_user     (user_id),
    INDEX idx_reports_analysis (analysis_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


/* ============================================================
   ALERT RECIPIENTS
   Stores email addresses to notify when endangered animals are seen.
   ============================================================ */

CREATE TABLE IF NOT EXISTS alert_recipients (
    id            INT            AUTO_INCREMENT PRIMARY KEY,
    user_id       INT            NOT NULL,
    email         VARCHAR(150)   NOT NULL UNIQUE,
    name          VARCHAR(150)   DEFAULT NULL,
    created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_alert_user
        FOREIGN KEY (user_id) REFERENCES users (user_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;