-- Create messages table
CREATE TABLE IF NOT EXISTS messages (
    id SERIAL PRIMARY KEY,
    match_id VARCHAR(255) NOT NULL,
    sender_id VARCHAR(255) NOT NULL,
    receiver_id VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(50) DEFAULT 'sent', -- sent, delivered, seen
    time BIGINT NOT NULL, -- timestamp as number
    message_type VARCHAR(50) DEFAULT 'text', -- text, image, video, audio
    media_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index on match_id for faster queries
CREATE INDEX IF NOT EXISTS idx_messages_match_id ON messages(match_id);
CREATE INDEX IF NOT EXISTS idx_messages_receiver_id ON messages(receiver_id);
CREATE INDEX IF NOT EXISTS idx_messages_sender_id ON messages(sender_id);

-- Create pending_messages table
CREATE TABLE IF NOT EXISTS pending_messages (
    id SERIAL PRIMARY KEY,
    sender_id VARCHAR(255) NOT NULL,
    receiver_id VARCHAR(255) NOT NULL,
    match_id VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    time BIGINT NOT NULL,
    message_type VARCHAR(50) DEFAULT 'text',
    media_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index on receiver_id for faster queries
CREATE INDEX IF NOT EXISTS idx_pending_messages_receiver_id ON pending_messages(receiver_id);

-- Do NOT create user_status or profile here.
-- Chat-backend uses the main Django table profiles_profile for status only (UPDATE status).
-- Add columns if they don't exist (idempotent for existing tables)
ALTER TABLE messages ADD COLUMN IF NOT EXISTS message_type VARCHAR(50) DEFAULT 'text';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS media_url TEXT;

ALTER TABLE pending_messages ADD COLUMN IF NOT EXISTS media_url TEXT;

-- Reply-to (store as JSON text: { message, time, sender })
ALTER TABLE messages ADD COLUMN IF NOT EXISTS reply_to TEXT;
ALTER TABLE pending_messages ADD COLUMN IF NOT EXISTS reply_to TEXT;

-- Create call_logs table
CREATE TABLE IF NOT EXISTS call_logs (
    id SERIAL PRIMARY KEY,
    caller_id VARCHAR(255) NOT NULL,
    receiver_id VARCHAR(255) NOT NULL,
    match_id VARCHAR(255),
    status VARCHAR(50) DEFAULT 'initiated', -- initiated, accepted, rejected, missed, ended
    call_type VARCHAR(20) DEFAULT 'audio',  -- audio, video
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP,
    duration INTEGER DEFAULT 0, -- in seconds
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_call_logs_caller_id ON call_logs(caller_id);
CREATE INDEX IF NOT EXISTS idx_call_logs_receiver_id ON call_logs(receiver_id);

-- Ensure call_type exists for DBs created before this column was added
ALTER TABLE call_logs ADD COLUMN IF NOT EXISTS call_type VARCHAR(20) DEFAULT 'audio';