-- Enable foreign key constraints
PRAGMA foreign_keys = ON;

CREATE TABLE client (
    uuid TEXT PRIMARY KEY NOT NULL,
    name TEXT 
);

CREATE TABLE camera (
    uuid TEXT PRIMARY KEY NOT NULL,
    desc TEXT,
    name TEXT NOT NULL
);

CREATE TABLE sightings (
    client_uuid TEXT NOT NULL,
    sighted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    camera_id TEXT NOT NULL,
    FOREIGN KEY (client_uuid) REFERENCES client(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (camera_id) REFERENCES camera(uuid) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE (client_uuid, camera_id)
);

-- Create indexes for better performance on foreign key lookups
CREATE INDEX idx_sightings_client_uuid ON sightings(client_uuid);
CREATE INDEX idx_sightings_camera_id ON sightings(camera_id);

CREATE TABLE banned_names (
    name TEXT PRIMARY KEY NOT NULL,
    banned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);