src/db/migrations/0001_initial.sql

-- Antaram Database Migration 0001: Initial Schema
-- This migration creates all tables, constraints, and indexes
-- required by the Antaram ride-sharing application.

-- ─── PRAGMA Settings ────────────────────────────────────────────────────────────
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- ─── Table: current_user ────────────────────────────────────────────────────────
-- Stores the currently authenticated user's profile. Only one row is expected.
CREATE TABLE IF NOT EXISTS current_user (
  id TEXT PRIMARY KEY NOT NULL,
  phone TEXT NOT NULL,
  full_name TEXT NOT NULL,
  email TEXT,
  avatar_url TEXT,
  gender TEXT CHECK(gender IN ('male', 'female', 'other')),
  home_address TEXT,
  work_address TEXT,
  preferred_language TEXT DEFAULT 'en',
  rating REAL DEFAULT 0,
  total_rides INTEGER DEFAULT 0,
  verified INTEGER DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: saved_locations ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS saved_locations (
  id TEXT PRIMARY KEY NOT NULL,
  user_id TEXT NOT NULL REFERENCES current_user(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  latitude REAL NOT NULL,
  longitude REAL NOT NULL,
  type TEXT DEFAULT 'favorite' CHECK(type IN ('home', 'work', 'favorite', 'recent')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: rides ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS rides (
  id TEXT PRIMARY KEY NOT NULL,
  user_id TEXT NOT NULL REFERENCES current_user(id) ON DELETE CASCADE,
  origin_address TEXT NOT NULL,
  origin_latitude REAL NOT NULL,
  origin_longitude REAL NOT NULL,
  destination_address TEXT NOT NULL,
  destination_latitude REAL NOT NULL,
  destination_longitude REAL NOT NULL,
  route_polyline TEXT,
  scheduled_time TEXT,
  status TEXT NOT NULL DEFAULT 'searching' CHECK(status IN ('searching', 'matched', 'in_progress', 'completed', 'cancelled')),
  seat_price REAL,
  total_distance REAL,
  estimated_duration INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: matches ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS matches (
  id TEXT PRIMARY KEY NOT NULL,
  ride_id TEXT NOT NULL REFERENCES rides(id) ON DELETE CASCADE,
  matched_user_id TEXT NOT NULL,
  matched_user_full_name TEXT NOT NULL,
  matched_user_avatar TEXT,
  matched_user_rating REAL DEFAULT 0,
  pickup_point TEXT,
  pickup_latitude REAL,
  pickup_longitude REAL,
  dropoff_point TEXT,
  dropoff_latitude REAL,
  dropoff_longitude REAL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'accepted', 'rejected', 'completed', 'cancelled')),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: conversations ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS conversations (
  id TEXT PRIMARY KEY NOT NULL,
  participant_1_id TEXT NOT NULL,
  participant_2_id TEXT NOT NULL,
  last_message TEXT,
  last_message_at TEXT,
  unread_count INTEGER DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: messages ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS messages (
  id TEXT PRIMARY KEY NOT NULL,
  conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_id TEXT NOT NULL,
  content TEXT NOT NULL,
  type TEXT NOT NULL DEFAULT 'text' CHECK(type IN ('text', 'image', 'location', 'system')),
  status TEXT NOT NULL DEFAULT 'sending' CHECK(status IN ('sending', 'sent', 'delivered', 'read', 'failed')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: friends ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS friends (
  id TEXT PRIMARY KEY NOT NULL,
  user_id TEXT NOT NULL REFERENCES current_user(id) ON DELETE CASCADE,
  friend_id TEXT NOT NULL,
  friend_full_name TEXT NOT NULL,
  friend_avatar TEXT,
  friend_phone TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'accepted', 'blocked')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: ratings ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ratings (
  id TEXT PRIMARY KEY NOT NULL,
  ride_id TEXT NOT NULL REFERENCES rides(id) ON DELETE CASCADE,
  from_user_id TEXT NOT NULL,
  to_user_id TEXT NOT NULL,
  score INTEGER NOT NULL,
  review TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: notifications ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
  id TEXT PRIMARY KEY NOT NULL,
  user_id TEXT NOT NULL REFERENCES current_user(id) ON DELETE CASCADE,
  type TEXT NOT NULL CHECK(type IN ('ride_matched', 'ride_cancelled', 'ride_completed', 'message', 'friend_request', 'system')),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  data TEXT,
  read INTEGER DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: search_history ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS search_history (
  id TEXT PRIMARY KEY NOT NULL,
  user_id TEXT NOT NULL REFERENCES current_user(id) ON DELETE CASCADE,
  query TEXT NOT NULL,
  address TEXT NOT NULL,
  latitude REAL NOT NULL,
  longitude REAL NOT NULL,
  searched_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Table: app_state ──────────────────────────────────────────────────────────
-- Key-value store for application-level preferences and feature flags.
CREATE TABLE IF NOT EXISTS app_state (
  key TEXT PRIMARY KEY NOT NULL,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─── Indexes ───────────────────────────────────────────────────────────────────

-- saved_locations
CREATE INDEX IF NOT EXISTS idx_saved_locations_user_id ON saved_locations(user_id);
CREATE INDEX IF NOT EXISTS idx_saved_locations_type ON saved_locations(type);

-- rides
CREATE INDEX IF NOT EXISTS idx_rides_user_id ON rides(user_id);
CREATE INDEX IF NOT EXISTS idx_rides_status ON rides(status);
CREATE INDEX IF NOT EXISTS idx_rides_created_at ON rides(created_at);

-- matches
CREATE INDEX IF NOT EXISTS idx_matches_ride_id ON matches(ride_id);
CREATE INDEX IF NOT EXISTS idx_matches_matched_user_id ON matches(matched_user_id);
CREATE INDEX IF NOT EXISTS idx_matches_status ON matches(status);

-- conversations
CREATE INDEX IF NOT EXISTS idx_conversations_participant_1 ON conversations(participant_1_id);
CREATE INDEX IF NOT EXISTS idx_conversations_participant_2 ON conversations(participant_2_id);
CREATE INDEX IF NOT EXISTS idx_conversations_last_message_at ON conversations(last_message_at);

-- messages
CREATE INDEX IF NOT EXISTS idx_messages_conversation_id ON messages(conversation_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);

-- friends
CREATE INDEX IF NOT EXISTS idx_friends_user_id ON friends(user_id);
CREATE INDEX IF NOT EXISTS idx_friends_friend_id ON friends(friend_id);
CREATE INDEX IF NOT EXISTS idx_friends_status ON friends(status);

-- ratings
CREATE INDEX IF NOT EXISTS idx_ratings_ride_id ON ratings(ride_id);
CREATE INDEX IF NOT EXISTS idx_ratings_to_user_id ON ratings(to_user_id);

-- notifications
CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_read ON notifications(read);
CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at);

-- search_history
CREATE INDEX IF NOT EXISTS idx_search_history_user_id ON search_history(user_id);
CREATE INDEX IF NOT EXISTS idx_search_history_searched_at ON search_history(searched_at);