137 lines
5.9 KiB
SQL
137 lines
5.9 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
full_name TEXT NOT NULL,
|
|
username TEXT UNIQUE,
|
|
role TEXT NOT NULL DEFAULT 'passenger' CHECK (role IN ('passenger', 'driver', 'admin', 'member')),
|
|
mauth_token TEXT,
|
|
phone TEXT,
|
|
citizen_id TEXT,
|
|
is_banned INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS companion_safety_profiles (
|
|
user_email TEXT PRIMARY KEY,
|
|
persona_label TEXT NOT NULL,
|
|
safety_consent INTEGER NOT NULL DEFAULT 0,
|
|
background_check_status TEXT NOT NULL DEFAULT 'not_started' CHECK (background_check_status IN ('not_started', 'pending', 'approved', 'rejected')),
|
|
reviewed_at TEXT,
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS companion_circles (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
cadence TEXT NOT NULL CHECK (cadence IN ('weekly', 'biweekly', 'monthly')),
|
|
status TEXT NOT NULL DEFAULT 'forming' CHECK (status IN ('forming', 'active', 'paused')),
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS companion_circle_members (
|
|
circle_id TEXT NOT NULL,
|
|
user_email TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'invited' CHECK (status IN ('invited', 'accepted', 'declined', 'left')),
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (circle_id, user_email),
|
|
FOREIGN KEY (circle_id) REFERENCES companion_circles(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS companion_activities (
|
|
id TEXT PRIMARY KEY,
|
|
circle_id TEXT NOT NULL,
|
|
created_by_email TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
location_label TEXT NOT NULL,
|
|
starts_at TEXT NOT NULL,
|
|
max_attendees INTEGER,
|
|
status TEXT NOT NULL DEFAULT 'planned' CHECK (status IN ('planned', 'cancelled', 'completed')),
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (circle_id) REFERENCES companion_circles(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by_email) REFERENCES users(email) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS companion_activity_rsvps (
|
|
activity_id TEXT NOT NULL,
|
|
user_email TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'going' CHECK (status IN ('going', 'maybe', 'not_going')),
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (activity_id, user_email),
|
|
FOREIGN KEY (activity_id) REFERENCES companion_activities(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS driver_profiles (
|
|
id TEXT PRIMARY KEY,
|
|
user_email TEXT NOT NULL UNIQUE,
|
|
vehicle_make TEXT NOT NULL,
|
|
vehicle_model TEXT NOT NULL,
|
|
license_plate TEXT NOT NULL,
|
|
vehicle_color TEXT NOT NULL,
|
|
is_available INTEGER NOT NULL DEFAULT 0,
|
|
current_lat REAL,
|
|
current_lng REAL,
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS rides (
|
|
id TEXT PRIMARY KEY,
|
|
passenger_email TEXT NOT NULL,
|
|
driver_email TEXT,
|
|
pickup_location TEXT NOT NULL,
|
|
pickup_lat REAL NOT NULL,
|
|
pickup_lng REAL NOT NULL,
|
|
destination_location TEXT NOT NULL,
|
|
destination_lat REAL NOT NULL,
|
|
destination_lng REAL NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'requested' CHECK (status IN ('requested', 'accepted', 'picked_up', 'completed', 'cancelled')),
|
|
driver_lat REAL,
|
|
driver_lng REAL,
|
|
rating INTEGER,
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (passenger_email) REFERENCES users(email) ON DELETE CASCADE,
|
|
FOREIGN KEY (driver_email) REFERENCES users(email) ON DELETE SET NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
order_id TEXT PRIMARY KEY,
|
|
user_email TEXT NOT NULL,
|
|
amount REAL NOT NULL,
|
|
currency TEXT NOT NULL DEFAULT 'INR',
|
|
status TEXT NOT NULL DEFAULT 'PENDING',
|
|
payment_session_id TEXT NOT NULL,
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_email) REFERENCES users(email) ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id TEXT PRIMARY KEY,
|
|
recipient_email TEXT NOT NULL,
|
|
type TEXT NOT NULL DEFAULT 'info' CHECK (type IN ('info', 'success', 'warning', 'mention', 'system')),
|
|
title TEXT NOT NULL,
|
|
body TEXT,
|
|
link TEXT,
|
|
actor_email TEXT,
|
|
is_read INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (recipient_email) REFERENCES users(email) ON DELETE CASCADE,
|
|
FOREIGN KEY (actor_email) REFERENCES users(email) ON DELETE SET NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
CREATE INDEX IF NOT EXISTS idx_companion_circle_members_user ON companion_circle_members(user_email);
|
|
CREATE INDEX IF NOT EXISTS idx_companion_activities_circle ON companion_activities(circle_id);
|
|
CREATE INDEX IF NOT EXISTS idx_companion_activity_rsvps_user ON companion_activity_rsvps(user_email);
|
|
CREATE INDEX IF NOT EXISTS idx_driver_profiles_user ON driver_profiles(user_email);
|
|
CREATE INDEX IF NOT EXISTS idx_driver_profiles_coords ON driver_profiles(current_lat, current_lng);
|
|
CREATE INDEX IF NOT EXISTS idx_rides_passenger ON rides(passenger_email);
|
|
CREATE INDEX IF NOT EXISTS idx_rides_driver ON rides(driver_email);
|
|
CREATE INDEX IF NOT EXISTS idx_rides_status ON rides(status);
|
|
CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_email);
|
|
CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON notifications(recipient_email, is_read);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);
|