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
