Schema Management

/**
 * Defines a database schema version
 * @param versionNumber - Version number (must be sequential, starting from 1)
 * @returns Version instance for defining schema and upgrade functions
 */
version(versionNumber: number): Version;

interface Version {
  /** Define table schemas for this version */
  stores(schema: { [tableName: string]: string | null }): Version;
  /** Define upgrade function for migrating from previous version */
  upgrade(upgradeFunction: (trans: Transaction) => PromiseLike<any> | void): Version;
}

// Basic version definition
db.version(1).stores({
  friends: "++id, name, age",
  messages: "++id, friendId, message, timestamp"
});

// Multiple versions
db.version(1).stores({
  users: "++id, name, email"
});

db.version(2).stores({
  users: "++id, name, email, createdAt", // Add index
  posts: "++id, userId, title, content"    // Add table
});

db.version(3).stores({
  users: "++id, name, email, createdAt",
  posts: "++id, userId, title, content, publishedAt", // Add index
  comments: "++id, postId, userId, content"           // Add table
});

// Schema syntax patterns:
// "++keyName" - Auto-incrementing primary key
// "keyName" - Non-auto-incrementing primary key
// "&keyName" - Unique key (not primary key)

// Auto-incrementing primary key
db.version(1).stores({
  users: "++id, name, email"  // id will auto-increment
});

// Non-auto-incrementing primary key
db.version(1).stores({
  settings: "key, value"      // key is the primary key (string)
});

// Unique constraint (not primary key)
db.version(1).stores({
  users: "id, &email, name"   // email must be unique
});

// Compound primary key
db.version(1).stores({
  userRoles: "[userId+roleId], assignedAt" // Compound primary key
});

// Index syntax patterns:
// "primaryKey, index1, index2" - Simple indexes
// "primaryKey, *multiIndex" - Multi-entry index (for arrays)
// "primaryKey, [compound+index]" - Compound index
// "primaryKey, &uniqueIndex" - Unique index

// Simple indexes
db.version(1).stores({
  products: "++id, name, category, price"
});

// Multi-entry index (for array values)
db.version(1).stores({
  articles: "++id, title, *tags, publishedAt"  // tags is an array
});

// Compound indexes
db.version(1).stores({
  orders: "++id, customerId, [customerId+date], status"
});

// Mixed index types
db.version(1).stores({
  users: "++id, &email, name, *interests, [city+state]"
});

/**
 * Add new tables in later versions
 */

// Version 1: Initial schema
db.version(1).stores({
  users: "++id, name, email"
});

// Version 2: Add new table
db.version(2).stores({
  users: "++id, name, email",
  posts: "++id, userId, title, content, createdAt"  // New table
});

// Version 3: Add another table
db.version(3).stores({
  users: "++id, name, email",
  posts: "++id, userId, title, content, createdAt",
  comments: "++id, postId, userId, content, createdAt"  // Another new table
});

/**
 * Add or remove indexes on existing tables
 */

// Version 1: Basic indexes
db.version(1).stores({
  products: "++id, name, category"
});

// Version 2: Add price index
db.version(2).stores({
  products: "++id, name, category, price"  // Add price index
});

// Version 3: Add compound index
db.version(3).stores({
  products: "++id, name, category, price, [category+price]"  // Add compound index
});

// Version 4: Remove an index
db.version(4).stores({
  products: "++id, name, price, [category+price]"  // Remove category index
});

/**
 * Delete tables by setting schema to null
 */

// Version 1: Multiple tables
db.version(1).stores({
  users: "++id, name, email",
  tempData: "++id, data, timestamp",
  posts: "++id, userId, title"
});

// Version 2: Remove temp table
db.version(2).stores({
  users: "++id, name, email",
  tempData: null,  // Delete this table
  posts: "++id, userId, title"
});

/**
 * Defines an upgrade function for migrating data
 * @param upgradeFunction - Function to execute during upgrade
 * @returns Version instance for method chaining
 */
upgrade(upgradeFunction: (trans: Transaction) => PromiseLike<any> | void): Version;

interface Transaction {
  /** Database reference */
  db: Dexie;
  /** Transaction mode */
  mode: IDBTransactionMode;
  /** Get table bound to this transaction */
  table(tableName: string): Table<any, any>;
}

// Version 1: Initial schema
db.version(1).stores({
  users: "++id, name, email"
});

// Version 2: Add timestamp, migrate existing data
db.version(2)
  .stores({
    users: "++id, name, email, createdAt"
  })
  .upgrade(trans => {
    // Add createdAt to all existing users
    return trans.table("users").toCollection().modify(user => {
      user.createdAt = Date.now();
    });
  });

// Version 3: Restructure data
db.version(3)
  .stores({
    users: "++id, name, email, createdAt, profile",
    profiles: "++id, userId, bio, avatar"
  })
  .upgrade(async trans => {
    // Move profile data to separate table
    const users = await trans.table("users").toArray();
    const profiles = users.map(user => ({
      userId: user.id,
      bio: user.bio || "",
      avatar: user.avatar || null
    }));
    
    // Add profiles
    await trans.table("profiles").bulkAdd(profiles);
    
    // Remove old fields from users
    await trans.table("users").toCollection().modify(user => {
      delete user.bio;
      delete user.avatar;
    });
  });

// Version 4: Data transformation
db.version(4)
  .stores({
    users: "++id, name, email, createdAt, profile",
    profiles: "++id, userId, bio, avatar"
  })
  .upgrade(trans => {
    // Transform email addresses to lowercase
    return trans.table("users").toCollection().modify(user => {
      user.email = user.email.toLowerCase();
    });
  });

// Version 1: Combined user data
db.version(1).stores({
  users: "++id, name, email, address, phone, preferences"
});

// Version 2: Split into users and user_details
db.version(2)
  .stores({
    users: "++id, name, email",
    userDetails: "++id, userId, address, phone, preferences"
  })
  .upgrade(async trans => {
    const users = await trans.table("users").toArray();
    
    // Create detail records
    const details = users.map(user => ({
      userId: user.id,
      address: user.address,
      phone: user.phone,
      preferences: user.preferences
    }));
    
    // Add details
    await trans.table("userDetails").bulkAdd(details);
    
    // Clean up users table
    await trans.table("users").toCollection().modify(user => {
      delete user.address;
      delete user.phone;
      delete user.preferences;
    });
  });

// Version 1: Separate tables
db.version(1).stores({
  basicInfo: "++id, name, email",
  extendedInfo: "++id, userId, phone, address"
});

// Version 2: Merge into single table
db.version(2)
  .stores({
    basicInfo: null,      // Delete old table
    extendedInfo: null,   // Delete old table
    users: "++id, name, email, phone, address"
  })
  .upgrade(async trans => {
    const basicInfo = await trans.table("basicInfo").toArray();
    const extendedInfo = await trans.table("extendedInfo").toArray();
    
    // Create lookup map
    const extendedMap = new Map();
    extendedInfo.forEach(ext => {
      extendedMap.set(ext.userId, ext);
    });
    
    // Merge data
    const mergedUsers = basicInfo.map(basic => {
      const extended = extendedMap.get(basic.id) || {};
      return {
        id: basic.id,
        name: basic.name,
        email: basic.email,
        phone: extended.phone || null,
        address: extended.address || null
      };
    });
    
    // Add merged data
    await trans.table("users").bulkAdd(mergedUsers);
  });

// Version 1: String-based status
db.version(1).stores({
  tasks: "++id, title, status, priority"
});

// Version 2: Convert to numeric status codes
db.version(2)
  .stores({
    tasks: "++id, title, status, priority"  // Same schema, different data
  })
  .upgrade(trans => {
    const statusMap = {
      "pending": 0,
      "in_progress": 1,
      "completed": 2,
      "cancelled": 3
    };
    
    return trans.table("tasks").toCollection().modify(task => {
      task.status = statusMap[task.status] || 0;
    });
  });

// Version 3: Add new fields with defaults
db.version(3)
  .stores({
    tasks: "++id, title, status, priority, createdAt, updatedAt"
  })
  .upgrade(trans => {
    const now = Date.now();
    return trans.table("tasks").toCollection().modify(task => {
      task.createdAt = task.createdAt || now;
      task.updatedAt = now;
    });
  });

// Always use sequential version numbers
db.version(1).stores({ /* initial schema */ });
db.version(2).stores({ /* updated schema */ }).upgrade(/* migration */);
db.version(3).stores({ /* further updates */ }).upgrade(/* migration */);
// Never skip version numbers or go backwards

// Version 1: Original schema
db.version(1).stores({
  items: "++id, name, description"
});

// Version 2: Add optional field with default
db.version(2)
  .stores({
    items: "++id, name, description, category"
  })
  .upgrade(trans => {
    // Provide default values for new fields
    return trans.table("items").toCollection().modify(item => {
      item.category = item.category || "uncategorized";
    });
  });

db.version(2)
  .stores({
    users: "++id, name, email, normalizedEmail"
  })
  .upgrade(async trans => {
    try {
      await trans.table("users").toCollection().modify(user => {
        if (user.email) {
          user.normalizedEmail = user.email.toLowerCase().trim();
        }
      });
    } catch (error) {
      console.error("Migration failed:", error);
      throw error; // Re-throw to abort the upgrade
    }
  });

interface Dexie {
  /** Current database version number */
  readonly verno: number;
  /** All tables in current version */
  readonly tables: Table[];
}

interface Table {
  /** Table schema information */
  readonly schema: TableSchema;
}

interface TableSchema {
  /** Table name */
  name: string;
  /** Primary key specification */
  primKey: IndexSpec;
  /** All indexes including primary key */
  indexes: IndexSpec[];
}

// Check current version
console.log("Current database version:", db.verno);

// List all tables
console.log("Available tables:", db.tables.map(t => t.name));

// Inspect table schema
const friendsSchema = db.friends.schema;
console.log("Primary key:", friendsSchema.primKey.name);
console.log("Indexes:", friendsSchema.indexes.map(idx => idx.name));

// Check if table has specific index
const hasAgeIndex = db.friends.schema.indexes.some(idx => idx.name === "age");