src/db/provider.tsx

import React, { Suspense } from 'react';
import { ActivityIndicator, View, StyleSheet } from 'react-native';
import { SQLiteProvider } from 'expo-sqlite';
import { PaperProvider, useTheme } from 'react-native-paper';

// ─── Migration runner ──────────────────────────────────────────────────────────
// This function is called once when the database is first opened (or when
// the schema version changes).  It reads and executes the bundled SQL
// migration file.

async function migrate(db: import('expo-sqlite').SQLiteDatabase) {
  // Apply the initial migration
  await db.execAsync(`
    PRAGMA journal_mode = WAL;
    PRAGMA foreign_keys = ON;

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

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

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

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

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

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

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

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

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

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

    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 for common query patterns

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

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

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

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

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

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

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

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

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

// ─── Loading fallback ───────────────────────────────────────────────────────────

function DatabaseLoadingFallback() {
  const theme = useTheme();

  return (
    <View style={[styles.container, { backgroundColor: theme.colors.background }]}>
      <ActivityIndicator size="large" color={theme.colors.primary} />
    </View>
  );
}

// ─── DatabaseProvider ───────────────────────────────────────────────────────────
// Wrap your app root with this provider so that:
//   1. The SQLite database is initialised on first render.
//   2. A loading spinner is shown while migrations run.
//   3. Child components can safely query the database via Drizzle.

export function DatabaseProvider({ children }: { children: React.ReactNode }) {
  return (
    <SQLiteProvider databaseName="antaram.db" onInit={migrate}>
      <Suspense fallback={<DatabaseLoadingFallback />}>{children}</Suspense>
    </SQLiteProvider>
  );
}

// ─── Styles ─────────────────────────────────────────────────────────────────────

const styles = StyleSheet.create({
  container: {
    flex: 1,
    justifyContent: 'center',
    alignItems: 'center',
  },
});