Database Migration

# Add a column:
> Add a 'phone' column to the users table.
  VARCHAR(20) NULL, after the 'email' column.
  Write: ALTER TABLE statement, Laravel migration file, rollback migration.
  Also update UserModel.php to include 'phone' in fillable and findAll() results.

# Rename a column (with zero downtime):
> Rename the 'full_name' column to 'name' in the users table.
  This is a live production table with 500,000 rows.
  Write a zero-downtime migration strategy:
  1. Add new column 'name' (nullable)
  2. Copy data from full_name to name in batches of 1000
  3. Add NOT NULL constraint
  4. Update application code to use 'name'
  5. Drop 'full_name' in a later release

# Split one table to two:
> The users table has address fields (address, city, state, pincode, country).
  Split them to a separate user_addresses table.
  Write:
  1. CREATE TABLE user_addresses with FK to users
  2. INSERT INTO user_addresses SELECT from users (migration)
  3. Drop address columns from users
  4. Update UserModel.php to JOIN with user_addresses

# Step 1: Convert the schema
> Convert this MySQL schema to PostgreSQL:
  [paste CREATE TABLE statements]

  MySQL to PostgreSQL differences to handle:
  - INT AUTO_INCREMENT -> SERIAL or BIGSERIAL
  - TINYINT(1) -> BOOLEAN
  - DATETIME -> TIMESTAMP
  - ENGINE=InnoDB -> (remove, not needed in PostgreSQL)
  - ENUM('a','b') -> CREATE TYPE or VARCHAR with CHECK constraint
  - UNSIGNED INT -> INT (PostgreSQL has no unsigned)
  - MySQL-specific functions: NOW(), IFNULL(), GROUP_CONCAT()

# Step 2: Convert queries
> Read all PHP files that have SQL queries.
  Convert MySQL-specific syntax to PostgreSQL:
  - LIMIT x OFFSET y -> stays the same (PostgreSQL uses same syntax)
  - GROUP_CONCAT(name SEPARATOR ',') -> STRING_AGG(name, ',')
  - IFNULL(col, 'default') -> COALESCE(col, 'default')
  - DATE_FORMAT(date, '%Y-%m') -> TO_CHAR(date, 'YYYY-MM')

# Step 3: Update connection code
> Update config/db.php from MySQL PDO to PostgreSQL PDO:
  Change: mysql:host= to pgsql:host=
  Add: sslmode=require for production
  Update error codes: SQLSTATE[23000] for unique violations differs

# Step 1: Install and configure
> Add Laravel's Eloquent ORM to a standalone PHP project (no full Laravel):
  - Add to composer.json: illuminate/database
  - Create config/database.php that boots Eloquent (Capsule\Manager)
  - Update public/index.php to require this config

# Step 2: Create Eloquent models
> Convert this PDO-based UserModel class to an Eloquent model:
  [paste UserModel.php]

  - Create app/Models/User.php extending Illuminate\Database\Eloquent\Model
  - Set $table, $fillable, $hidden (for password)
  - Map each method to Eloquent: findById -> find(), findByEmail -> where()->first()
  - Add relationships: hasMany(Task::class), belongsToMany(Project::class, 'project_members')

# Step 3: Update callers
> Find all files that use the old UserModel class.
  Update them to use the new Eloquent User model.
  Replace: $model->findById($id) with User::find($id)
  Replace: $model->create($data) with User::create($data)

# Split a column:
> Write a PHP migration script that:
  - Reads all rows from users where full_name is not null
  - Splits full_name on the first space
  - Writes first_name = left part, last_name = right part (or '' if only one word)
  - Processes in batches of 500 rows (memory safe)
  - Logs how many rows were updated

# Normalize data:
> The products table has a 'category' column with free text values
  like "electronics", "Electronics", "ELECTRONICS", "electonics" (typo).
  Write a script that:
  1. Shows a COUNT(*) summary of all distinct values (for me to review)
  2. Normalizes all variations to lowercase
  3. Fixes known typos based on a correction map I provide

# Hash existing passwords:
> The users table has passwords stored in plain text (old system).
  Write a PHP script to hash all of them with password_hash(PASSWORD_DEFAULT).
  Process in batches of 100. Log progress. Do not hash already-hashed values
  (check if value starts with '$2y$').

# Analyze a slow query:
> This MySQL query takes 8 seconds on a table with 2 million rows:
  [paste slow SQL query]

  Table structure:
  [paste SHOW CREATE TABLE result]

  Analyze: what indexes are missing? What is the query doing?
  Provide: optimized query + CREATE INDEX statements.

# Find N+1 queries:
> Read ProductController::index(). It loads 20 products and then
  for each product calls getCategory() and getImages() separately.
  This is N+1 queries. Rewrite the main query to use JOINs to load
  all data in one query.

# Add indexes to an existing schema:
> Read all PHP files that have SELECT queries.
  Identify which WHERE clause columns do not have indexes.
  Generate ALTER TABLE ADD INDEX statements for the missing indexes.

# Paginate a large query:
> This query loads all 500,000 orders at once for a report:
  SELECT * FROM orders WHERE year(created_at) = 2024
  It times out. Rewrite it to:
  1. Process in batches of 1000 using cursor-based pagination
  2. Write results to a CSV file in chunks
  3. Email the CSV when complete