-- chat_schema.sql
CREATE DATABASE IF NOT EXISTS chat_db;
USE chat_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20) UNIQUE,
    is_typing TINYINT(1) DEFAULT 0,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE chats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT,
    receiver_id INT,
    message TEXT,
    file_path VARCHAR(255),
    file_type VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Demo data
INSERT INTO users (name, phone) VALUES
('jonh1', '9999999999'),
('Alice', '8888888888');

INSERT INTO chats (sender_id, receiver_id, message, created_at) VALUES
(1, 2, 'Hello Alice! How are you?', NOW() - INTERVAL 10 MINUTE),
(2, 1, 'Hey jonh1! I am good. How about you?', NOW() - INTERVAL 9 MINUTE);
