CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(191) NULL,
email VARCHAR(191) UNIQUE,
password VARCHAR(255) NULL,
token TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


-- profiles (based on your Django model)
CREATE TABLE IF NOT EXISTS profiles (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT UNIQUE,
username VARCHAR(100) UNIQUE,
first_name VARCHAR(100) NULL,
last_name VARCHAR(100) NULL,
phone VARCHAR(20) NULL,
email VARCHAR(191) UNIQUE,
register_num VARCHAR(100) NULL,
vehicle VARCHAR(100) NULL,
wallet DECIMAL(10,2) DEFAULT 0.00,
code VARCHAR(50) NULL,
owner VARCHAR(100) NULL,
owner_username VARCHAR(100) NULL,
free TINYINT(1) DEFAULT 1,
pending TINYINT(1) DEFAULT 0,
unavailable TINYINT(1) DEFAULT 0,
expo_push_token VARCHAR(255) NULL,
token VARCHAR(255) NULL,
verified TINYINT(1) DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);


-- vehicles table
CREATE TABLE IF NOT EXISTS vehicles (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NULL,
model VARCHAR(100) NULL,
plate VARCHAR(50) NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);


-- messages table
CREATE TABLE IF NOT EXISTS messages (
id INT AUTO_INCREMENT PRIMARY KEY,
sender_id INT NULL,
receiver_id INT NULL,
body TEXT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL,
FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE SET NULL
);


