TABLE: metadata
CREATE TABLE metadata (
                        id INTEGER PRIMARY KEY,
                        mp_version TEXT NOT NULL,
                        key TEXT UNIQUE NOT NULL )
INDEX: sqlite_autoindex_metadata_1
None
TABLE: region
CREATE TABLE region (
                        id INTEGER PRIMARY KEY,
                        name TEXT UNIQUE NOT NULL,
                        timezone TEXT)
INDEX: sqlite_autoindex_region_1
None
TABLE: survey
CREATE TABLE survey (
                        id INTEGER PRIMARY KEY,
                        name TEXT UNIQUE NOT NULL,
                        region_id INTEGER,
                        year_start INTEGER,
                        year_end INTEGER,
                        FOREIGN KEY (region_id) REFERENCES region (id) ON DELETE SET NULL)
INDEX: sqlite_autoindex_survey_1
None
TABLE: station
CREATE TABLE station (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        lat REAL,
                        long REAL,
                        survey_id INTEGER NOT NULL,
                        FOREIGN KEY (survey_id) REFERENCES survey (id) ON DELETE CASCADE)
TABLE: camera
CREATE TABLE camera (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        station_id INTEGER NOT NULL,
                        FOREIGN KEY (station_id) REFERENCES station (id) ON DELETE CASCADE)
TABLE: uploads
CREATE TABLE uploads (
                        id INTEGER PRIMARY KEY,
                        base_dir TEXT UNIQUE NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
INDEX: sqlite_autoindex_uploads_1
None
TABLE: media
CREATE TABLE media (
                        id INTEGER PRIMARY KEY,
                        base_dir_id INTEGER NOT NULL,
                        relative_path TEXT UNIQUE NOT NULL,
                        sha256 TEXT UNIQUE NOT NULL,
                        ext TEXT NOT NULL,
                        timestamp TEXT NOT NULL,
                        station_id INTEGER NOT NULL,
                        camera_id INTEGER,
                        sequence_id INTEGER,
                        external_id INTEGER,
                        comment TEXT,
                        FOREIGN KEY (base_dir_id) REFERENCES uploads (id) ON DELETE CASCADE,
                        FOREIGN KEY (station_id) REFERENCES station (id) ON DELETE CASCADE,
                        FOREIGN KEY (camera_id) REFERENCES camera (id) ON DELETE SET NULL,
                        FOREIGN KEY (sequence_id) REFERENCES sequence (id) ON DELETE SET NULL)
INDEX: sqlite_autoindex_media_1
None
INDEX: sqlite_autoindex_media_2
None
TABLE: roi
CREATE TABLE roi (
                        id INTEGER PRIMARY KEY,
                        media_id INTEGER NOT NULL,
                        frame INTEGER NOT NULL,
                        bbox_x REAL NOT NULL,
                        bbox_y REAL NOT NULL,
                        bbox_w REAL NOT NULL,
                        bbox_h REAL NOT NULL,
                        viewpoint INTEGER,
                        reviewed INTEGER NOT NULL,
                        favorite INTEGER NOT NULL,
                        individual_id INTEGER,
                        emb INTEGER,
                        FOREIGN KEY(media_id) REFERENCES media (id) ON DELETE CASCADE,
                        FOREIGN KEY(individual_id) REFERENCES individual (id) ON DELETE SET NULL)
TABLE: individual
CREATE TABLE individual (
                        id INTEGER PRIMARY KEY,
                        name TEXT NOT NULL,
                        sex TEXT,
                        age TEXT)
TABLE: sequence
CREATE TABLE sequence (
                        id INTEGER PRIMARY KEY)
TABLE: media_thumbnails
CREATE TABLE media_thumbnails (
                        id INTEGER PRIMARY KEY,
                        fid INTEGER UNIQUE NOT NULL,
                        filepath TEXT NOT NULL,
                        FOREIGN KEY(fid) REFERENCES media (id) ON DELETE CASCADE)
INDEX: sqlite_autoindex_media_thumbnails_1
None
TABLE: roi_thumbnails
CREATE TABLE roi_thumbnails (
                        id INTEGER PRIMARY KEY,
                        fid INTEGER UNIQUE NOT NULL,
                        filepath TEXT NOT NULL,
                        FOREIGN KEY(fid) REFERENCES roi (id) ON DELETE CASCADE)
INDEX: sqlite_autoindex_roi_thumbnails_1
None
