<?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$database = "shopnest_db";

// Create connection
$conn = mysqli_connect($host, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "<h2>Updating Database for ID Verification</h2>";
echo "<div style='background: #f8f9fa; padding: 20px; border-radius: 10px;'>";

// 1. Add columns to users table
$sql = "ALTER TABLE users 
        ADD COLUMN IF NOT EXISTS id_verified BOOLEAN DEFAULT FALSE,
        ADD COLUMN IF NOT EXISTS verification_level ENUM('none', 'pending', 'basic', 'full') DEFAULT 'none',
        ADD COLUMN IF NOT EXISTS verification_date TIMESTAMP NULL";

if (mysqli_query($conn, $sql)) {
    echo "✓ Added verification columns to users table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

// 2. Create user_id_proofs table
$sql = "CREATE TABLE IF NOT EXISTS user_id_proofs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    proof_type ENUM('aadhar', 'driving_license', 'passport', 'voter_id', 'pan_card') NOT NULL,
    front_image VARCHAR(255) NOT NULL,
    back_image VARCHAR(255),
    proof_number VARCHAR(100) NOT NULL,
    full_name VARCHAR(200),
    date_of_birth DATE,
    issue_date DATE,
    expiry_date DATE,
    address TEXT,
    verification_status ENUM('pending', 'verified', 'rejected', 'expired') DEFAULT 'pending',
    rejection_reason TEXT,
    verified_by INT,
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Created user_id_proofs table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

// 3. Add columns to orders table
$sql = "ALTER TABLE orders 
        ADD COLUMN IF NOT EXISTS id_verified BOOLEAN DEFAULT FALSE,
        ADD COLUMN IF NOT EXISTS id_proof_id INT DEFAULT NULL";

if (mysqli_query($conn, $sql)) {
    echo "✓ Added verification columns to orders table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

// 4. Create admin_notifications table (for admin notifications)
$sql = "CREATE TABLE IF NOT EXISTS admin_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Created admin_notifications table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

echo "</div>";
echo "<br><h3 style='color: green;'>✅ Database update completed!</h3>";
echo "<p>Now you can use the ID verification system.</p>";
echo "<div style='margin-top: 20px;'>";
echo "<a href='../index.php' class='btn btn-primary'>Go to Homepage</a> ";
echo "<a href='user/id-verification.php' class='btn btn-success'>Test ID Verification</a>";
echo "</div>";

mysqli_close($conn);
?>

<style>
    body {
        font-family: Arial, sans-serif;
        padding: 20px;
        background: #f0f2f5;
    }
    h2 {
        color: #2c3e50;
        margin-bottom: 20px;
    }
    .btn {
        display: inline-block;
        padding: 10px 20px;
        color: white;
        text-decoration: none;
        border-radius: 5px;
        margin-right: 10px;
        border: none;
        cursor: pointer;
    }
    .btn-primary { background: #3498db; }
    .btn-success { background: #2ecc71; }
    .btn:hover { opacity: 0.9; }
    ✓ { color: #27ae60; font-weight: bold; }
    ✗ { color: #e74c3c; font-weight: bold; }
</style><?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$database = "shopnest_db";

// Create connection
$conn = mysqli_connect($host, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

echo "<h2>Updating Database for Complete ID Verification System</h2>";
echo "<div style='background: #f8f9fa; padding: 20px; border-radius: 10px;'>";

// 1. Add columns to users table
$sql = "ALTER TABLE users 
        ADD COLUMN IF NOT EXISTS id_verified BOOLEAN DEFAULT FALSE,
        ADD COLUMN IF NOT EXISTS verification_level ENUM('none', 'pending', 'verified', 'rejected') DEFAULT 'none',
        ADD COLUMN IF NOT EXISTS verification_date TIMESTAMP NULL,
        ADD COLUMN IF NOT EXISTS verification_notes TEXT";

if (mysqli_query($conn, $sql)) {
    echo "✓ Added verification columns to users table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

// 2. Create user_id_proofs table (updated)
$sql = "CREATE TABLE IF NOT EXISTS user_id_proofs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    proof_type ENUM('aadhar', 'driving_license', 'passport', 'voter_id', 'pan_card') NOT NULL,
    front_image VARCHAR(255) NOT NULL,
    back_image VARCHAR(255),
    proof_number VARCHAR(100) NOT NULL,
    full_name VARCHAR(200),
    date_of_birth DATE,
    issue_date DATE,
    expiry_date DATE,
    address TEXT,
    verification_status ENUM('pending', 'verified', 'rejected', 'expired') DEFAULT 'pending',
    rejection_reason TEXT,
    verified_by INT,
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Created user_id_proofs table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

// 3. Create admin_verification_logs table
$sql = "CREATE TABLE IF NOT EXISTS admin_verification_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    proof_id INT NOT NULL,
    admin_id INT NOT NULL,
    action ENUM('verified', 'rejected', 'pending') NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (proof_id) REFERENCES user_id_proofs(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES admin(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Created admin_verification_logs table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

// 4. Create admin_notifications table
$sql = "CREATE TABLE IF NOT EXISTS admin_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('verification', 'order', 'system') DEFAULT 'system',
    reference_id INT,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_read (type, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

if (mysqli_query($conn, $sql)) {
    echo "✓ Created admin_notifications table<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

// 5. Insert default notification for ID verification
$sql = "INSERT INTO admin_notifications (title, message, type, created_at) 
        VALUES ('ID Verification System Ready', 'ID verification system has been successfully installed.', 'system', NOW())";

if (mysqli_query($conn, $sql)) {
    echo "✓ Added system notification<br>";
} else {
    echo "✗ Error: " . mysqli_error($conn) . "<br>";
}

echo "</div>";
echo "<br><h3 style='color: green;'>✅ Database update completed!</h3>";
echo "<p>ID verification system is now ready to use.</p>";
echo "<div style='margin-top: 20px;'>";
echo "<a href='../index.php' class='btn btn-primary'>Go to Homepage</a> ";
echo "<a href='../admin/verify-users.php' class='btn btn-success'>Go to Admin Verification Panel</a>";
echo "<a href='../user/id-verification.php' class='btn btn-warning'>Test User ID Verification</a>";
echo "</div>";

mysqli_close($conn);
?>

<style>
    body { font-family: Arial, sans-serif; padding: 20px; background: #f0f2f5; }
    h2 { color: #2c3e50; margin-bottom: 20px; }
    .btn { display: inline-block; padding: 10px 20px; color: white; text-decoration: none; border-radius: 5px; margin: 5px; }
    .btn-primary { background: #3498db; }
    .btn-success { background: #2ecc71; }
    .btn-warning { background: #f39c12; }
    .btn:hover { opacity: 0.9; transform: translateY(-2px); }
    ✓ { color: #27ae60; font-weight: bold; }
    ✗ { color: #e74c3c; font-weight: bold; }
</style>