-- chat_threads table
CREATE TABLE IF NOT EXISTS chat_threads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER, -- Foreign key to users table for authenticated users
    guest_id TEXT UNIQUE, -- Unique ID for guest users
    last_message_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'open', -- e.g., 'open', 'closed', 'pending'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- chat_messages table
CREATE TABLE IF NOT EXISTS chat_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_id INTEGER NOT NULL,
    sender_id INTEGER, -- Foreign key to users table for authenticated senders
    guest_id TEXT, -- ID for guest senders
    is_admin BOOLEAN DEFAULT FALSE, -- TRUE if sender is an admin
    body TEXT NOT NULL, -- Encrypted message body
    attachment_path TEXT, -- Path to uploaded attachment
    status TEXT DEFAULT 'sent', -- e.g., 'sent', 'delivered', 'read'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    read_at DATETIME,
    FOREIGN KEY (thread_id) REFERENCES chat_threads(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL
);

-- chat_typing table
CREATE TABLE IF NOT EXISTS chat_typing (
    user_id INTEGER UNIQUE, -- User currently typing
    guest_id TEXT UNIQUE, -- Guest currently typing
    is_typing BOOLEAN DEFAULT FALSE,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Index for faster lookup of messages by thread
CREATE INDEX IF NOT EXISTS idx_chat_messages_thread_id ON chat_messages (thread_id);
-- Index for faster lookup of threads by user or guest
CREATE INDEX IF NOT EXISTS idx_chat_threads_user_id ON chat_threads (user_id);
CREATE INDEX IF NOT EXISTS idx_chat_threads_guest_id ON chat_threads (guest_id);
