-- schema.sql

-- Create users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
);

-- Create events table
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  host_user_id INTEGER NOT NULL REFERENCES users(id),
  start_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_timestamp TIMESTAMP NOT NULL,
  status VARCHAR(10) CHECK (status IN ('active', 'inactive')) NOT NULL DEFAULT 'active'
);

-- Create sessions table
CREATE TABLE sessions (
  id SERIAL PRIMARY KEY,
  event_id INTEGER NOT NULL REFERENCES events(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  start_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_timestamp TIMESTAMP NOT NULL,
  session_status VARCHAR(10) CHECK (session_status IN ('active', 'inactive')) NOT NULL DEFAULT 'active'
);

-- Create photos table
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  session_id INTEGER NOT NULL REFERENCES sessions(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  image_data BYTEA NOT NULL
);

-- Create jwt_tokens table with indexes
CREATE INDEX idx_jwt_tokens_username ON jwt_tokens (token_value);
CREATE INDEX idx_jwt_tokens_event_name ON jwt_tokens (token_value);