import { sql } from 'drizzle-orm';
import { PostgresJsDatabase } from 'drizzle-orm/postgres-js';

export async function createTestSchema(db: PostgresJsDatabase) {
  // Create extension
  await db.execute(sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);

  // Create tables in dependency order using raw SQL with proper column names
  const tableCreationQueries = [
    // Users table (no dependencies)
    sql`CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      handle TEXT,
      linked_at TIMESTAMP WITH TIME ZONE NOT NULL,
      last_login_at TIMESTAMP WITH TIME ZONE NOT NULL
    )`,

    // Published records table (no dependencies)
    sql`CREATE TABLE IF NOT EXISTS published_records (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      uri TEXT NOT NULL,
      cid TEXT NOT NULL,
      recorded_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      UNIQUE(uri, cid)
    )`,

    // Cards table (references published_records and self-references)
    sql`CREATE TABLE IF NOT EXISTS cards (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      author_id TEXT NOT NULL,
      type TEXT NOT NULL,
      content_data JSONB NOT NULL,
      url TEXT,
      url_type TEXT,
      parent_card_id UUID REFERENCES cards(id),
      via_card_id UUID REFERENCES cards(id),
      published_record_id UUID REFERENCES published_records(id),
      library_count INTEGER NOT NULL DEFAULT 0,
      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
    )`,

    // Library memberships table (references cards and published_records)
    sql`CREATE TABLE IF NOT EXISTS library_memberships (
      card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
      user_id TEXT NOT NULL,
      added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      published_record_id UUID REFERENCES published_records(id),
      PRIMARY KEY (card_id, user_id)
    )`,

    // Collections table (references published_records)
    sql`CREATE TABLE IF NOT EXISTS collections (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      author_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      access_type TEXT NOT NULL,
      card_count INTEGER NOT NULL DEFAULT 0,
      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      published_record_id UUID REFERENCES published_records(id)
    )`,

    // Collection collaborators table (references collections)
    sql`CREATE TABLE IF NOT EXISTS collection_collaborators (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
      collaborator_id TEXT NOT NULL,
      UNIQUE(collection_id, collaborator_id)
    )`,

    // Collection cards table (references collections, cards, and published_records)
    sql`CREATE TABLE IF NOT EXISTS collection_cards (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
      card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
      added_by TEXT NOT NULL,
      added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      via_card_id UUID REFERENCES cards(id),
      published_record_id UUID REFERENCES published_records(id),
      UNIQUE(collection_id, card_id)
    )`,
    sql`
    CREATE TABLE IF NOT EXISTS feed_activities (
      id UUID PRIMARY KEY,
      actor_id TEXT NOT NULL,
      card_id TEXT,
      connection_id TEXT,
      type TEXT NOT NULL,
      metadata JSONB NOT NULL,
      url_type TEXT,
      source TEXT,
      created_at TIMESTAMP NOT NULL DEFAULT NOW()
    )`,

    // Notifications table (no dependencies)
    sql`CREATE TABLE IF NOT EXISTS notifications (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      recipient_user_id TEXT NOT NULL,
      actor_user_id TEXT NOT NULL,
      type TEXT NOT NULL,
      metadata JSONB NOT NULL,
      read BOOLEAN NOT NULL DEFAULT false,
      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
    )`,

    // Sync statuses table (no dependencies)
    sql`CREATE TABLE IF NOT EXISTS sync_statuses (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      curator_id TEXT NOT NULL UNIQUE,
      sync_state TEXT NOT NULL,
      last_synced_at TIMESTAMP WITH TIME ZONE,
      last_sync_attempt_at TIMESTAMP WITH TIME ZONE,
      sync_error_message TEXT,
      records_processed INTEGER,
      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
    )`,

    // Follows table (references published_records)
    sql`CREATE TABLE IF NOT EXISTS follows (
      follower_id TEXT NOT NULL,
      target_id TEXT NOT NULL,
      target_type TEXT NOT NULL,
      published_record_id UUID REFERENCES published_records(id),
      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      PRIMARY KEY (follower_id, target_id, target_type)
    )`,

    // Connections table (references published_records)
    sql`CREATE TABLE IF NOT EXISTS connections (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      curator_id TEXT NOT NULL,
      source_type TEXT NOT NULL,
      source_value TEXT NOT NULL,
      source_url_metadata JSONB,
      target_type TEXT NOT NULL,
      target_value TEXT NOT NULL,
      target_url_metadata JSONB,
      connection_type TEXT,
      note TEXT,
      published_record_id UUID REFERENCES published_records(id),
      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
    )`,

    // Following feed items table (references feed_activities)
    sql`CREATE TABLE IF NOT EXISTS following_feed_items (
      user_id TEXT NOT NULL,
      activity_id UUID NOT NULL REFERENCES feed_activities(id) ON DELETE CASCADE,
      created_at TIMESTAMP NOT NULL,
      PRIMARY KEY (user_id, activity_id)
    )`,
  ];

  // Execute table creation queries in order
  for (const query of tableCreationQueries) {
    await db.execute(query);
  }

  // Create indexes
  await db.execute(
    sql`CREATE INDEX IF NOT EXISTS idx_user_cards ON library_memberships(user_id)`,
  );
  await db.execute(
    sql`CREATE INDEX IF NOT EXISTS idx_card_users ON library_memberships(card_id)`,
  );

  // Performance indexes
  // Covering index for getUrlCardsOfUser - avoids table lookups by including cardId
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_library_memberships_user_type_covering 
    ON library_memberships(user_id, added_at DESC) 
    INCLUDE (card_id)
  `);

  // Optimizes sorting cards by type and update time in query results
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_cards_type_updated_at 
    ON cards(type, updated_at DESC)
  `);

  // Index for getLibrariesForUrl and getCollectionsWithUrl - fast URL+type lookups with card ID included
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_cards_url_type 
    ON cards(url, type) INCLUDE (id)
  `);

  // Index for filtering by URL type
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_cards_url_type_filter 
    ON cards(url_type)
  `);

  // Covering index for getCardsInCollection - sorted by add time with cardId included
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_collection_cards_collection_added 
    ON collection_cards(collection_id, added_at DESC) 
    INCLUDE (card_id)
  `);

  // Partial index for finding NOTE cards by parent - only indexes NOTE type cards
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_cards_parent_type 
    ON cards(parent_card_id, type) WHERE type = 'NOTE'
  `);

  // Covering index for finding collections containing a card - avoids table lookups
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_collection_cards_card_collection 
    ON collection_cards(card_id) INCLUDE (collection_id)
  `);
  // Feed activities indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_type_idx ON feed_activities(type);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_url_type_idx ON feed_activities(url_type);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_created_at_idx ON feed_activities(created_at DESC);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_type_created_at_idx ON feed_activities(type, created_at DESC);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_url_type_created_at_idx ON feed_activities(url_type, created_at DESC);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_type_url_type_created_at_idx ON feed_activities(type, url_type, created_at DESC);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_dedup_idx ON feed_activities(actor_id, card_id, created_at DESC);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_card_id_idx ON feed_activities(card_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_connection_id_idx ON feed_activities(connection_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_feed_activities_actor_id ON feed_activities(actor_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS feed_activities_source_idx ON feed_activities(source);
  `);

  // Index for efficient AT URI look ups
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS published_records_uri_idx ON published_records(uri);
  `);

  // Notifications table indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS notifications_recipient_idx ON notifications(recipient_user_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS notifications_recipient_created_at_idx ON notifications(recipient_user_id, created_at DESC);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS notifications_recipient_read_idx ON notifications(recipient_user_id, read);
  `);

  // Cards table indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS cards_author_url_idx ON cards(author_id, url);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS cards_author_id_idx ON cards(author_id);
  `);
  // New indexes for stats query optimization
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_cards_created_at ON cards(created_at);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_cards_author_created_at ON cards(author_id, created_at);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_cards_created_at_url_type ON cards(created_at, url_type);
  `);

  // Collections table indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS collections_author_id_idx ON collections(author_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS collections_author_updated_at_idx ON collections(author_id, updated_at);
  `);
  // New indexes for stats query optimization
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_collections_created_at ON collections(created_at);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_collections_author_created_at ON collections(author_id, created_at);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_collections_created_at_access_type ON collections(created_at, access_type);
  `);

  // Collection cards table indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS collection_cards_card_id_idx ON collection_cards(card_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS collection_cards_collection_id_idx ON collection_cards(collection_id);
  `);

  // Follows table indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_follows_follower ON follows(follower_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_follows_target ON follows(target_id, target_type);
  `);
  // New indexes for stats query optimization
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_follows_created_at ON follows(created_at);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_follows_follower_created_at ON follows(follower_id, created_at);
  `);

  // Connections table indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS connections_curator_id_idx ON connections(curator_id);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS connections_source_idx ON connections(source_type, source_value);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS connections_target_idx ON connections(target_type, target_value);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS connections_created_at_idx ON connections(created_at DESC);
  `);
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS connections_curator_created_at_idx ON connections(curator_id, created_at DESC);
  `);
  // New indexes for stats query optimization
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_connections_created_at_connection_type ON connections(created_at, connection_type);
  `);

  // Following feed items indexes
  await db.execute(sql`
    CREATE INDEX IF NOT EXISTS idx_following_feed_user_time ON following_feed_items(user_id, created_at DESC);
  `);
}
