init.sql

-- Create sequences
-- Create users table
CREATE TABLE IF NOT EXISTS public.users
(
    id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    full_name character varying(100) NOT NULL,
    email character varying(255) NOT NULL,
    password_hash text NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT users_email_key UNIQUE (email)
);

-- Create chat_sessions table
CREATE TABLE IF NOT EXISTS public.chat_sessions
(
    session_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    user_id integer,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    title text,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chat_sessions_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.users (id)
        ON DELETE CASCADE
);

-- Create chat_history table
CREATE TABLE IF NOT EXISTS public.chat_history
(
    message_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    chat_session_id integer NOT NULL,
    user_id integer,
    message text NOT NULL,
    sender character varying(10) NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chat_history_chat_session_id_fkey FOREIGN KEY (chat_session_id)
        REFERENCES public.chat_sessions (session_id)
        ON DELETE CASCADE,
    CONSTRAINT chat_history_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.users (id)
        ON DELETE CASCADE,
    CONSTRAINT chat_history_sender_check CHECK (sender::text = ANY (ARRAY['user'::character varying, 'bot'::character varying]::text[]))
);

-- Function to automatically update the 'updated_at' timestamp on row modification
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger to update 'updated_at' in chat_sessions table before any update
CREATE TRIGGER trg_chat_sessions_updated_at
BEFORE UPDATE ON public.chat_sessions
FOR EACH ROW
EXECUTE PROCEDURE public.update_updated_at_column();