init.sql

-- PostgreSQL initialization script for Arrest Data application
-- This script runs when the PostgreSQL container starts for the first time

-- Create the database schema
CREATE SCHEMA IF NOT EXISTS arrest_data;

-- Set the search path
SET search_path TO arrest_data, public;

-- Create clients table
CREATE TABLE IF NOT EXISTS clients (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    nickname VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    registration_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create sessions table
CREATE TABLE IF NOT EXISTS sessions (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    address VARCHAR(45) NOT NULL, -- IPv4/IPv6 address
    start_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE
);

-- Create queries table
CREATE TABLE IF NOT EXISTS queries (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    session_id INTEGER NOT NULL,
    query_type VARCHAR(100) NOT NULL,
    parameters TEXT, -- JSON string
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE
);

-- Create messages table
CREATE TABLE IF NOT EXISTS messages (
    id SERIAL PRIMARY KEY,
    sender_type VARCHAR(50) NOT NULL,
    sender_id INTEGER NOT NULL,
    recipient_type VARCHAR(50) NOT NULL,
    recipient_id INTEGER NOT NULL,
    message TEXT NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    read BOOLEAN DEFAULT FALSE
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_clients_email ON clients (email);
CREATE INDEX IF NOT EXISTS idx_clients_nickname ON clients (nickname);
CREATE INDEX IF NOT EXISTS idx_sessions_client_id ON sessions (client_id);
CREATE INDEX IF NOT EXISTS idx_sessions_start_time ON sessions (start_time);
CREATE INDEX IF NOT EXISTS idx_queries_client_id ON queries (client_id);
CREATE INDEX IF NOT EXISTS idx_queries_session_id ON queries (session_id);
CREATE INDEX IF NOT EXISTS idx_queries_timestamp ON queries (timestamp);
CREATE INDEX IF NOT EXISTS idx_queries_query_type ON queries (query_type);
CREATE INDEX IF NOT EXISTS idx_messages_recipient ON messages (recipient_type, recipient_id);
CREATE INDEX IF NOT EXISTS idx_messages_read ON messages (read);
CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages (timestamp);

-- Grant permissions to the application user
GRANT ALL PRIVILEGES ON SCHEMA arrest_data TO arrest_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA arrest_data TO arrest_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA arrest_data TO arrest_user;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA arrest_data GRANT ALL ON TABLES TO arrest_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA arrest_data GRANT ALL ON SEQUENCES TO arrest_user;