<?php
/**
 * Database Restructure Migration Script
 * Separates authentication (users) from profile data (user_details)
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once('includes/config.php');

echo "<h2>Database Restructure Migration</h2>";
echo "<style>
body { font-family: Arial; padding: 20px; max-width: 1200px; margin: 0 auto; }
.success { background: #d4edda; color: #155724; padding: 15px; border-left: 4px solid #28a745; margin: 10px 0; }
.error { background: #f8d7da; color: #721c24; padding: 15px; border-left: 4px solid #dc3545; margin: 10px 0; }
.warning { background: #fff3cd; color: #856404; padding: 15px; border-left: 4px solid #ffc107; margin: 10px 0; }
.info { background: #d1ecf1; color: #0c5460; padding: 15px; border-left: 4px solid #17a2b8; margin: 10px 0; }
table { border-collapse: collapse; width: 100%; margin: 15px 0; }
th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
th { background: #f8f9fa; font-weight: bold; }
.code { background: #f5f5f5; padding: 15px; border-radius: 5px; font-family: monospace; white-space: pre-wrap; }
</style>";

if (!$con) {
    die("<div class='error'>❌ Database connection failed: " . mysqli_connect_error() . "</div>");
}

echo "<div class='success'>✅ Connected to database: <strong>" . DB_NAME . "</strong></div>";

// ============================================
// STEP 1: Backup current data
// ============================================
echo "<h3>Step 1: Backing up current data...</h3>";

$backup_users = mysqli_query($con, "SELECT * FROM users");
$users_count = mysqli_num_rows($backup_users);

echo "<div class='info'>📊 Found <strong>$users_count</strong> users to migrate</div>";

// ============================================
// STEP 2: Create user_details table
// ============================================
echo "<h3>Step 2: Creating user_details table...</h3>";

$create_user_details = "CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `contactno` varchar(15) DEFAULT NULL,
  `shippingAddress` longtext,
  `shippingState` varchar(255) DEFAULT NULL,
  `shippingCity` varchar(255) DEFAULT NULL,
  `shippingPincode` int(11) DEFAULT NULL,
  `billingAddress` longtext,
  `billingState` varchar(255) DEFAULT NULL,
  `billingCity` varchar(255) DEFAULT NULL,
  `billingPincode` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($con, $create_user_details)) {
    echo "<div class='success'>✅ user_details table created successfully</div>";
} else {
    echo "<div class='warning'>⚠️ user_details table may already exist: " . mysqli_error($con) . "</div>";
}

// ============================================
// STEP 3: Add username column to users
// ============================================
echo "<h3>Step 3: Adding username column to users table...</h3>";

$check_username = mysqli_query($con, "SHOW COLUMNS FROM users LIKE 'username'");
if (mysqli_num_rows($check_username) == 0) {
    $add_username = "ALTER TABLE `users` ADD COLUMN `username` varchar(100) DEFAULT NULL AFTER `id`";
    if (mysqli_query($con, $add_username)) {
        echo "<div class='success'>✅ username column added</div>";
    } else {
        echo "<div class='error'>❌ Failed to add username column: " . mysqli_error($con) . "</div>";
    }
} else {
    echo "<div class='info'>ℹ️ username column already exists</div>";
}

// ============================================
// STEP 4: Migrate customer data
// ============================================
echo "<h3>Step 4: Migrating customer data to user_details...</h3>";

$migrate_customers = "INSERT INTO `user_details` (
    user_id, name, contactno, shippingAddress, shippingState, shippingCity, shippingPincode,
    billingAddress, billingState, billingCity, billingPincode
)
SELECT 
    id, name, contactno, shippingAddress, shippingState, shippingCity, shippingPincode,
    billingAddress, billingState, billingCity, billingPincode
FROM `users`
WHERE (role = 'customer' OR role IS NULL OR role = '')
AND id NOT IN (SELECT user_id FROM user_details)";

$result = mysqli_query($con, $migrate_customers);
if ($result) {
    $migrated = mysqli_affected_rows($con);
    echo "<div class='success'>✅ Migrated <strong>$migrated</strong> customer records to user_details</div>";
} else {
    echo "<div class='error'>❌ Migration failed: " . mysqli_error($con) . "</div>";
}

// ============================================
// STEP 5: Check admin table and migrate if exists
// ============================================
echo "<h3>Step 5: Checking admin table...</h3>";

$check_admin_table = mysqli_query($con, "SHOW TABLES LIKE 'admin'");
if (mysqli_num_rows($check_admin_table) > 0) {
    echo "<div class='info'>ℹ️ admin table found, checking for admins to migrate...</div>";

    $admin_count = mysqli_query($con, "SELECT COUNT(*) as count FROM admin");
    $admin_row = mysqli_fetch_assoc($admin_count);

    echo "<div class='info'>📊 Found <strong>{$admin_row['count']}</strong> admin records</div>";

    // Check admin table structure
    $admin_structure = mysqli_query($con, "DESCRIBE admin");
    echo "<table><tr><th>Column</th><th>Type</th></tr>";
    while ($col = mysqli_fetch_assoc($admin_structure)) {
        echo "<tr><td>{$col['Field']}</td><td>{$col['Type']}</td></tr>";
    }
    echo "</table>";

    echo "<div class='warning'>⚠️ Manual Review Required: Check admin table structure and migrate manually if needed</div>";
} else {
    echo "<div class='info'>ℹ️ No admin table found - skipping admin migration</div>";
}

// ============================================
// STEP 6: Verification
// ============================================
echo "<h3>Step 6: Verification...</h3>";

// Count users by role
$role_counts = mysqli_query($con, "
    SELECT role, COUNT(*) as count 
    FROM users 
    GROUP BY role
");

echo "<h4>Users by Role:</h4>";
echo "<table><tr><th>Role</th><th>Count</th></tr>";
while ($row = mysqli_fetch_assoc($role_counts)) {
    $role = $row['role'] ? $row['role'] : '(not set)';
    echo "<tr><td>$role</td><td>{$row['count']}</td></tr>";
}
echo "</table>";

// Count user_details
$details_count = mysqli_query($con, "SELECT COUNT(*) as count FROM user_details");
$details_row = mysqli_fetch_assoc($details_count);

echo "<h4>Data Integrity Check:</h4>";
echo "<table>";
echo "<tr><th>Check</th><th>Result</th></tr>";

$customers = mysqli_query($con, "SELECT COUNT(*) as count FROM users WHERE role = 'customer'");
$customer_row = mysqli_fetch_assoc($customers);

echo "<tr><td>Customers in users table</td><td>{$customer_row['count']}</td></tr>";
echo "<tr><td>Records in user_details</td><td>{$details_row['count']}</td></tr>";

if ($customer_row['count'] == $details_row['count']) {
    echo "<tr><td colspan='2' style='background: #d4edda; color: #155724;'>✅ Counts match - migration successful!</td></tr>";
} else {
    echo "<tr><td colspan='2' style='background: #fff3cd; color: #856404;'>⚠️ Counts don't match - review needed</td></tr>";
}
echo "</table>";

// ============================================
// STEP 7: Sample data view
// ============================================
echo "<h3>Step 7: Sample Data</h3>";

echo "<h4>Users Table (First 5):</h4>";
$sample_users = mysqli_query($con, "SELECT id, username, email, role FROM users LIMIT 5");
echo "<table><tr><th>ID</th><th>Username</th><th>Email</th><th>Role</th></tr>";
while ($row = mysqli_fetch_assoc($sample_users)) {
    echo "<tr>";
    echo "<td>{$row['id']}</td>";
    echo "<td>" . ($row['username'] ?: '-') . "</td>";
    echo "<td>" . ($row['email'] ?: '-') . "</td>";
    echo "<td>" . ($row['role'] ?: '-') . "</td>";
    echo "</tr>";
}
echo "</table>";

echo "<h4>User Details Table (First 5):</h4>";
$sample_details = mysqli_query($con, "SELECT user_id, name, contactno FROM user_details LIMIT 5");
echo "<table><tr><th>User ID</th><th>Name</th><th>Contact</th></tr>";
while ($row = mysqli_fetch_assoc($sample_details)) {
    echo "<tr>";
    echo "<td>{$row['user_id']}</td>";
    echo "<td>{$row['name']}</td>";
    echo "<td>{$row['contactno']}</td>";
    echo "</tr>";
}
echo "</table>";

// ============================================
// Summary
// ============================================
echo "<h3>📋 Migration Summary</h3>";
echo "<div class='success'>";
echo "<h4>✅ Migration Complete!</h4>";
echo "<p><strong>Next Steps:</strong></p>";
echo "<ol>";
echo "<li>Verify the data looks correct above</li>";
echo "<li>Test login with both admin and customer accounts</li>";
echo "<li>Update login.php to use new structure</li>";
echo "<li>Update signup.php to create user_details</li>";
echo "<li>Once verified, remove old columns from users table (see SQL file)</li>";
echo "</ol>";
echo "</div>";

echo "<div class='warning'>";
echo "<h4>⚠️ Important Notes:</h4>";
echo "<ul>";
echo "<li>Old columns still exist in users table (name, contactno, etc.)</li>";
echo "<li>This is intentional for safety - don't delete them yet!</li>";
echo "<li>After thorough testing, run the cleanup SQL in database-restructure.sql</li>";
echo "<li>Keep a backup before running cleanup!</li>";
echo "</ul>";
echo "</div>";

echo "<br>";
echo "<a href='index.php' style='padding: 10px 20px; background: #4caf50; color: white; text-decoration: none; border-radius: 5px;'>Go to Homepage</a> ";
echo "<a href='login.php' style='padding: 10px 20px; background: #2196f3; color: white; text-decoration: none; border-radius: 5px; margin-left: 10px;'>Test Login</a>";

mysqli_close($con);
?>
