-- Schema version tracking table
CREATE TABLE schema_versions (
    version_id VARCHAR(50) PRIMARY KEY,
    applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    description VARCHAR(200) NOT NULL,
    script_name VARCHAR(100) NOT NULL,
    applied_by VARCHAR(50) NOT NULL,
    execution_time_ms INT,
    success BOOLEAN NOT NULL DEFAULT TRUE,
    rollback_script VARCHAR(200)
);

-- Example version entries
INSERT INTO schema_versions VALUES 
('v1.0.1', '2024-01-15 10:30:00', 'Create users table', 'V1.0.1__Create_users_table.sql', 'admin', 1250, TRUE, NULL),
('v1.0.2', '2024-01-16 14:15:00', 'Add user status column', 'V1.0.2__Add_user_status.sql', 'admin', 45000, TRUE, 'R1.0.2__Remove_user_status.sql');

-- Version-based naming convention
V1.0.1__Create_users_table.sql
V1.0.2__Add_user_status_column.sql  
V1.0.3__Create_orders_table.sql
V1.1.0__Add_payment_methods.sql
V1.1.1__Fix_order_status_index.sql

-- Rollback scripts (optional but recommended)
R1.0.2__Remove_user_status_column.sql
R1.1.0__Remove_payment_methods.sql

-- V1.0.2__Add_user_status.sql
-- Description: Add status column to users table for account management
-- Author: Development Team
-- Date: 2024-01-16

-- Check if migration already applied
SELECT COUNT(*) FROM schema_versions WHERE version_id = 'v1.0.2';

-- Begin transaction
START TRANSACTION;

-- Add the column if it doesn't exist
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.columns 
    WHERE table_schema = DATABASE() 
    AND table_name = 'users' 
    AND column_name = 'status'
);

-- Only add column if it doesn't exist (idempotent)
SET @sql = IF(@column_exists = 0, 
    'ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT ''active''',
    'SELECT "Column already exists" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Create index if it doesn't exist
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);

-- Record successful migration
INSERT INTO schema_versions (version_id, description, script_name, applied_by, execution_time_ms)
VALUES ('v1.0.2', 'Add user status column', 'V1.0.2__Add_user_status.sql', USER(), 
        TIMESTAMPDIFF(MICROSECOND, @start_time, NOW()) / 1000);

-- Commit transaction
COMMIT;

-- Adding a new column (safe for most databases)
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP NULL;

-- Adding a new table (always safe)
CREATE TABLE user_preferences (
    user_id BIGINT PRIMARY KEY,
    theme VARCHAR(20) NOT NULL DEFAULT 'default',
    notifications_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    language VARCHAR(10) NOT NULL DEFAULT 'en',
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Adding an index (can be resource intensive but non-blocking in many databases)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);  -- PostgreSQL
CREATE INDEX idx_orders_status ON orders(status) ALGORITHM=INPLACE; -- MySQL

-- Expanding a column's size (generally safe, but test first)
ALTER TABLE products ALTER COLUMN description TYPE VARCHAR(1000);  -- PostgreSQL
ALTER TABLE products MODIFY COLUMN description VARCHAR(1000);      -- MySQL

-- Relaxing constraints (safe)
ALTER TABLE orders ALTER COLUMN notes DROP NOT NULL;

-- Adding to enum values (database-specific considerations)
ALTER TABLE orders MODIFY COLUMN status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'returned');

-- Renaming a column (potentially disruptive)
-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN user_name VARCHAR(50);

-- Phase 2: Populate new column
UPDATE users SET user_name = username WHERE user_name IS NULL;

-- Phase 3: Update application to use new column
-- (Deploy application changes)

-- Phase 4: Remove old column (after verification)
ALTER TABLE users DROP COLUMN username;

-- PHASE 1: EXPAND - Add new structures without removing old ones
-- Example: Splitting a name column into first_name and last_name

-- Step 1: Add new columns
ALTER TABLE users 
ADD COLUMN first_name VARCHAR(50),
ADD COLUMN last_name VARCHAR(50);

-- Step 2: Create trigger or procedure to keep data in sync
DELIMITER //
CREATE TRIGGER sync_name_columns 
BEFORE INSERT ON users 
FOR EACH ROW 
BEGIN
    IF NEW.first_name IS NULL AND NEW.last_name IS NULL AND NEW.name IS NOT NULL THEN
        SET NEW.first_name = SUBSTRING_INDEX(NEW.name, ' ', 1);
        SET NEW.last_name = SUBSTRING(NEW.name, LENGTH(SUBSTRING_INDEX(NEW.name, ' ', 1)) + 2);
    END IF;
    
    IF NEW.name IS NULL AND (NEW.first_name IS NOT NULL OR NEW.last_name IS NOT NULL) THEN
        SET NEW.name = CONCAT(COALESCE(NEW.first_name, ''), ' ', COALESCE(NEW.last_name, ''));
    END IF;
END//
DELIMITER ;

-- Step 3: Migrate existing data
UPDATE users 
SET first_name = SUBSTRING_INDEX(name, ' ', 1),
    last_name = CASE 
        WHEN name LIKE '% %' THEN SUBSTRING(name, LENGTH(SUBSTRING_INDEX(name, ' ', 1)) + 2)
        ELSE ''
    END
WHERE first_name IS NULL OR last_name IS NULL;

-- PHASE 2: TRANSITION - Update application to use new columns
-- Deploy application changes to read from first_name/last_name
-- Continue writing to both old and new columns

-- PHASE 3: CONTRACT - Remove old structures after verification
-- Step 1: Stop writing to old column (deploy app changes)
-- Step 2: Drop trigger
DROP TRIGGER sync_name_columns;

-- Step 3: Remove old column
ALTER TABLE users DROP COLUMN name;

-- Deploy schema changes first
ALTER TABLE products ADD COLUMN new_pricing_model JSON;

-- Application code with feature flag
function getProductPrice(product) {
    if (featureFlag.isEnabled('new_pricing_model')) {
        return calculateNewPrice(product.new_pricing_model);
    } else {
        return product.legacy_price;
    }
}

-- Gradual rollout process:
-- 1. Deploy schema changes
-- 2. Deploy application with feature flag disabled
-- 3. Enable feature flag for 5% of users
-- 4. Monitor and gradually increase percentage
-- 5. Remove legacy code and columns when fully migrated

-- Process for major schema changes:

-- 1. Set up parallel database (Green) with new schema
CREATE DATABASE app_new_schema;

-- 2. Create new schema structure
-- (Run all migration scripts on new database)

-- 3. Set up real-time synchronization
-- Use triggers, change data capture, or application dual-writes

-- 4. Migrate historical data in batches
INSERT INTO app_new_schema.users_new 
SELECT user_id, email, created_at, status 
FROM app_old_schema.users 
WHERE user_id BETWEEN 1 AND 10000;

-- 5. Validate data consistency
SELECT COUNT(*) FROM app_old_schema.users;
SELECT COUNT(*) FROM app_new_schema.users_new;

-- 6. Switch application traffic to new database
-- Update database connection strings

-- 7. Monitor and keep old database as fallback
-- Can quickly switch back if issues arise

-- Process used by online schema change tools:

-- 1. Create shadow table with desired structure
CREATE TABLE users_new LIKE users;
ALTER TABLE users_new ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- 2. Create triggers to capture changes
DELIMITER //
CREATE TRIGGER users_insert_trigger 
AFTER INSERT ON users 
FOR EACH ROW 
BEGIN
    INSERT INTO users_new (user_id, username, email, created_at, status)
    VALUES (NEW.user_id, NEW.username, NEW.email, NEW.created_at, 'active');
END//

CREATE TRIGGER users_update_trigger 
AFTER UPDATE ON users 
FOR EACH ROW 
BEGIN
    UPDATE users_new 
    SET username = NEW.username, email = NEW.email 
    WHERE user_id = NEW.user_id;
END//

CREATE TRIGGER users_delete_trigger 
AFTER DELETE ON users 
FOR EACH ROW 
BEGIN
    DELETE FROM users_new WHERE user_id = OLD.user_id;
END//
DELIMITER ;

-- 3. Copy existing data in chunks
-- (Automated by the tool to avoid long-running transactions)
INSERT INTO users_new (user_id, username, email, created_at, status)
SELECT user_id, username, email, created_at, 'active'
FROM users 
WHERE user_id BETWEEN 1 AND 1000;

-- 4. Atomic rename when copy is complete
RENAME TABLE users TO users_old, users_new TO users;

-- 5. Clean up
DROP TRIGGER users_insert_trigger;
DROP TRIGGER users_update_trigger;
DROP TRIGGER users_delete_trigger;
DROP TABLE users_old;

-- Instead of one large operation that locks the table:
-- ALTER TABLE large_table ADD COLUMN new_column INT DEFAULT 0;

-- Use batched approach:
-- 1. Add column allowing NULL
ALTER TABLE large_table ADD COLUMN new_column INT;

-- 2. Update in batches to avoid long locks
SET @batch_size = 10000;
SET @last_id = 0;

update_loop: LOOP
    UPDATE large_table 
    SET new_column = 0 
    WHERE id > @last_id 
    AND id <= @last_id + @batch_size
    AND new_column IS NULL;
    
    SET @rows_affected = ROW_COUNT();
    SET @last_id = @last_id + @batch_size;
    
    -- Sleep to avoid overwhelming the database
    SELECT SLEEP(0.1);
    
    -- Exit when no more rows to process
    IF @rows_affected = 0 THEN
        LEAVE update_loop;
    END IF;
END LOOP;

-- 3. Add NOT NULL constraint after all data is migrated
ALTER TABLE large_table MODIFY COLUMN new_column INT NOT NULL DEFAULT 0;

-- Service A: User Management
-- Database: user_service_db
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP
);

-- Service B: Order Management  
-- Database: order_service_db
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,  -- Reference to user service (no FK constraint)
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP
);

-- Benefits:
-- - Each service evolves independently
-- - Reduced blast radius of schema changes
-- - Service-specific optimizations possible
-- - Clear ownership boundaries

-- Version 1 API and Schema
CREATE TABLE products_v1 (
    product_id BIGINT PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10,2),
    category VARCHAR(100)
);

-- Version 2 introduces new pricing model
CREATE TABLE products_v2 (
    product_id BIGINT PRIMARY KEY,
    name VARCHAR(200),
    base_price DECIMAL(10,2),
    pricing_tier VARCHAR(50),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- API Gateway routes based on version
-- /v1/products -> reads from products_v1 view
-- /v2/products -> reads from products_v2 table

-- Create view for backward compatibility
CREATE VIEW products_v1 AS
SELECT 
    product_id,
    name,
    base_price AS price,
    (SELECT name FROM categories WHERE id = category_id) AS category
FROM products_v2;

-- PostgreSQL: pg_dump for schema-only backups
pg_dump --schema-only --no-owner --no-privileges dbname > schema.sql

-- MySQL: pt-online-schema-change for large table modifications
pt-online-schema-change --alter "ADD COLUMN status VARCHAR(20) DEFAULT 'active'" \
  --execute h=localhost,D=mydb,t=users

-- MongoDB: Custom migration scripts
// migrations/001_add_user_status.js
db.users.updateMany(
    { status: { $exists: false } },
    { $set: { status: "active" } }
);

-- Simple migration framework structure
CREATE TABLE migration_lock (
    id INT PRIMARY KEY DEFAULT 1,
    locked_at TIMESTAMP,
    locked_by VARCHAR(100),
    CHECK (id = 1)  -- Ensure only one row
);

-- Migration execution procedure
DELIMITER //
CREATE PROCEDURE execute_migration(
    IN migration_id VARCHAR(50),
    IN migration_description TEXT,
    IN migration_sql TEXT
)
BEGIN
    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        -- Release lock and re-throw error
        DELETE FROM migration_lock;
        RESIGNAL;
    END;
    
    -- Acquire lock
    INSERT INTO migration_lock (locked_at, locked_by) 
    VALUES (NOW(), USER());
    
    -- Check if already applied
    IF NOT EXISTS (SELECT 1 FROM schema_versions WHERE version_id = migration_id) THEN
        -- Execute migration
        SET @sql = migration_sql;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- Record success
        INSERT INTO schema_versions (version_id, description, applied_by)
        VALUES (migration_id, migration_description, USER());
    END IF;
    
    -- Release lock
    DELETE FROM migration_lock;
END//
DELIMITER ;

-- 1. Syntax validation
EXPLAIN FORMAT=JSON 
ALTER TABLE large_table ADD COLUMN new_column INT;

-- 2. Performance testing on copy of production data
CREATE TABLE test_large_table AS SELECT * FROM large_table LIMIT 1000000;
-- Time the migration
SET @start_time = NOW(6);
ALTER TABLE test_large_table ADD COLUMN new_column INT DEFAULT 0;
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, NOW(6)) / 1000 AS execution_time_ms;

-- 3. Data integrity validation
-- Before migration
SELECT COUNT(*), SUM(CRC32(CONCAT(col1, col2, col3))) FROM test_table;

-- After migration  
SELECT COUNT(*), SUM(CRC32(CONCAT(col1, col2, col3, new_col))) FROM test_table;

-- 4. Application compatibility testing
-- Run automated tests against new schema
-- Verify backward compatibility with old application versions

-- Always test rollback procedures
-- 1. Apply migration
-- 2. Insert test data
-- 3. Execute rollback
-- 4. Verify data integrity

-- Example rollback script
-- R1.0.2__Remove_user_status.sql
START TRANSACTION;

-- Verify rollback is safe (no data loss)
SELECT COUNT(*) FROM users WHERE status != 'active';

-- If count > 0, abort rollback
-- Manual intervention required

-- Remove column
ALTER TABLE users DROP COLUMN status;

-- Update version table
DELETE FROM schema_versions WHERE version_id = 'v1.0.2';

COMMIT;

-- Monitor long-running migrations
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM information_schema.PROCESSLIST 
WHERE COMMAND = 'Query' 
AND TIME > 300  -- Running for more than 5 minutes
AND INFO LIKE '%ALTER TABLE%';

-- Monitor replication lag during migrations
SHOW SLAVE STATUS\G

-- Check for blocked queries
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- Post-migration validation queries
-- 1. Check table integrity
CHECK TABLE users;

-- 2. Verify row counts match expectations
SELECT 
    table_name,
    table_rows,
    NOW() as checked_at
FROM information_schema.tables 
WHERE table_schema = DATABASE()
ORDER BY table_rows DESC;

-- 3. Validate constraints
SELECT 
    table_name,
    constraint_name,
    constraint_type
FROM information_schema.table_constraints
WHERE table_schema = DATABASE()
AND constraint_type = 'FOREIGN KEY';

-- 4. Check for duplicate data
SELECT user_id, COUNT(*) as duplicate_count
FROM users
GROUP BY user_id
HAVING COUNT(*) > 1;