CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    nickname VARCHAR(24) NOT NULL UNIQUE,
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS rooms (
    id BIGSERIAL PRIMARY KEY,
    room_name VARCHAR(50) NOT NULL UNIQUE,
    topic VARCHAR(140) NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS messages (
    id BIGSERIAL PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_messages_room_id_id ON messages(room_id, id);
CREATE INDEX IF NOT EXISTS idx_users_last_seen_at ON users(last_seen_at);

INSERT INTO rooms (room_name, topic)
VALUES
    ('general', 'General discussions from all over Pakistan'),
    ('karachi', 'Karachi community room'),
    ('lahore', 'Lahore community room'),
    ('islamabad', 'Islamabad and twin cities hangout'),
    ('tech', 'Developers and technology talk')
ON CONFLICT (room_name) DO NOTHING;