formies/init.sql

-- init.sql for PostgreSQL
-- Attempt to create the database if it doesn't exist.
-- Note: CREATE DATABASE IF NOT EXISTS is not standard SQL for all clients.
-- This might need to be handled outside the script or by connecting to a default db like 'postgres' first.
-- For docker-entrypoint-initdb.d, this script is typically run after the DB specified by POSTGRES_DB is created.

-- Enable pgcrypto extension for gen_random_uuid() if not already enabled
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Users table for authentication and authorization
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  first_name VARCHAR(255) DEFAULT NULL,
  last_name VARCHAR(255) DEFAULT NULL,
  role VARCHAR(50) DEFAULT 'user' CHECK(role IN ('user', 'admin', 'super_admin')),
  is_verified BOOLEAN DEFAULT FALSE,
  is_active BOOLEAN DEFAULT TRUE,
  verification_token TEXT DEFAULT NULL,
  password_reset_token TEXT DEFAULT NULL,
  password_reset_expires TIMESTAMPTZ NULL DEFAULT NULL,
  last_login TIMESTAMPTZ NULL DEFAULT NULL,
  failed_login_attempts INTEGER DEFAULT 0,
  account_locked_until TIMESTAMPTZ NULL DEFAULT NULL,
  must_change_password BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
  -- Removed redundant UNIQUE constraints as they are already on id, uuid, email
);
CREATE INDEX IF NOT EXISTS idx_email ON users (email);
CREATE INDEX IF NOT EXISTS idx_verification_token ON users (verification_token);
CREATE INDEX IF NOT EXISTS idx_password_reset_token ON users (password_reset_token);
CREATE INDEX IF NOT EXISTS idx_uuid_users ON users (uuid);

-- User sessions table for JWT blacklisting and session management
CREATE TABLE IF NOT EXISTS user_sessions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  token_jti TEXT NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  user_agent TEXT DEFAULT NULL,
  ip_address VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_token_jti ON user_sessions (token_jti);
CREATE INDEX IF NOT EXISTS idx_user_id_sessions ON user_sessions (user_id);
CREATE INDEX IF NOT EXISTS idx_expires_at_sessions ON user_sessions (expires_at);

-- Forms table
CREATE TABLE IF NOT EXISTS forms (
  id SERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  user_id INTEGER NOT NULL,
  name VARCHAR(255) DEFAULT 'My Form',
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  thank_you_url TEXT DEFAULT NULL,
  thank_you_message TEXT DEFAULT NULL,
  ntfy_enabled BOOLEAN DEFAULT TRUE,
  is_archived BOOLEAN DEFAULT FALSE,
  allowed_domains TEXT DEFAULT NULL, -- Consider array of VARCHARs or separate table for multi-domain
  email_notifications_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  notification_email_address VARCHAR(255) DEFAULT NULL,
  recaptcha_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_user_id_forms ON forms (user_id);
CREATE INDEX IF NOT EXISTS idx_uuid_forms ON forms (uuid);

-- Submissions table
CREATE TABLE IF NOT EXISTS submissions (
  id SERIAL PRIMARY KEY,
  form_uuid UUID NOT NULL,
  user_id INTEGER NOT NULL, -- Assuming submissions are tied to a user account that owns the form
  data JSONB NOT NULL, -- Storing JSON as JSONB
  ip_address VARCHAR(255) NULL,
  submitted_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (form_uuid) REFERENCES forms(uuid) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE -- Or remove if submissions are anonymous to users table
);
CREATE INDEX IF NOT EXISTS idx_form_uuid_submissions ON submissions (form_uuid);
CREATE INDEX IF NOT EXISTS idx_user_id_submissions ON submissions (user_id);
CREATE INDEX IF NOT EXISTS idx_submitted_at_submissions ON submissions (submitted_at);

-- Rate limiting table
CREATE TABLE IF NOT EXISTS rate_limits (
  id SERIAL PRIMARY KEY,
  identifier TEXT NOT NULL,
  action TEXT NOT NULL,
  count INTEGER DEFAULT 1,
  window_start TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMPTZ NOT NULL,
  UNIQUE (identifier, action)
);
CREATE INDEX IF NOT EXISTS idx_identifier_action_rate_limits ON rate_limits (identifier, action);
CREATE INDEX IF NOT EXISTS idx_expires_at_rate_limits ON rate_limits (expires_at);

-- API Keys table
CREATE TABLE IF NOT EXISTS api_keys (
  id SERIAL PRIMARY KEY,
  uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  user_id INTEGER NOT NULL,
  key_name VARCHAR(255) DEFAULT NULL,
  api_key_identifier TEXT NOT NULL UNIQUE,
  hashed_api_key_secret TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  last_used_at TIMESTAMPTZ NULL DEFAULT NULL,
  expires_at TIMESTAMPTZ NULL DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_user_id_api_keys ON api_keys (user_id);
CREATE INDEX IF NOT EXISTS idx_api_key_identifier_api_keys ON api_keys (api_key_identifier);

-- Function and Trigger to update 'updated_at' timestamp
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for users table
CREATE TRIGGER set_timestamp_users
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

-- Trigger for forms table
CREATE TRIGGER set_timestamp_forms
BEFORE UPDATE ON forms
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

-- Create default super admin user
-- Using ON CONFLICT to prevent error if user already exists.
-- UUID is now generated by default by the database.
INSERT INTO users (email, password_hash, first_name, last_name, role, is_verified, is_active, must_change_password)
VALUES ('admin@formies.local', 'NEEDS_TO_BE_SET_ON_FIRST_LOGIN', 'Admin', 'User', 'super_admin', TRUE, TRUE, TRUE)
ON CONFLICT (email) DO NOTHING;

-- Note: PRAGMA foreign_keys = ON; is not needed in PostgreSQL. FKs are enforced by default if defined.
-- Note: Backticks for table/column names are generally not needed unless using reserved words or special chars.
-- Standard SQL double quotes can be used if necessary, but unquoted is often preferred.