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);