-- This script holds the messages table schema and initial data.
CREATE TABLE IF NOT EXISTS program_message (
    id SERIAL PRIMARY KEY,
    program_id INT NOT NULL,
    seeker_id INT NOT NULL,
    sender_id INT NOT NULL,
    receiver_id INT,
    receiver_role_flag BOOLEAN DEFAULT FALSE,
    cc_role_id INT,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (program_id) REFERENCES program_v1(id),
    FOREIGN KEY (seeker_id) REFERENCES users(id),
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (receiver_id) REFERENCES users(id),
    FOREIGN KEY (cc_role_id) REFERENCES user_role(id)
);

-- Add user_type column to differentiate users
CREATE TYPE user_type_enum AS ENUM ('Seeker', 'Org', 'AI', 'System');
ALTER TABLE IF EXISTS users
    ADD COLUMN IF NOT EXISTS user_type user_type_enum DEFAULT 'Seeker';
