<?php
session_start();
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/access_control.php';
require_admin();

$message = '';
$type = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['fix_db'])) {
    try {
        // Drop old unique key
        try {
            $pdo->exec("ALTER TABLE class_subjects DROP INDEX unique_class_subject");
            $message .= "পুরনো unique_class_subject কী মুছে ফেলা হয়েছে।<br>";
        } catch (Exception $e) {
            if (strpos($e->getMessage(), 'doesn\'t exist') !== false || strpos($e->getMessage(), 'Drop') !== false) {
                $message .= "unique_class_subject কী আগেই নেই (OK)।<br>";
            } else {
                $message .= "কী মুছতে সমস্যা: " . $e->getMessage() . "<br>";
            }
        }

        // Drop any other class_id only index
        try {
            $pdo->exec("ALTER TABLE class_subjects DROP INDEX class_id");
            $message .= "class_id ইন্ডেক্স মুছে ফেলা হয়েছে।<br>";
        } catch (Exception $e) {}

        // Drop subject_id index
        try {
            $pdo->exec("ALTER TABLE class_subjects DROP INDEX subject_id");
            $message .= "subject_id ইন্ডেক্স মুছে ফেলা হয়েছে।<br>";
        } catch (Exception $e) {}

        // Add group_name column if not exists
        try {
            $pdo->exec("ALTER TABLE class_subjects ADD COLUMN group_name VARCHAR(100) DEFAULT NULL AFTER subject_id");
            $message .= "group_name কলাম যোগ করা হয়েছে।<br>";
        } catch (Exception $e) {
            if (strpos($e->getMessage(), 'Duplicate') !== false || strpos($e->getMessage(), 'duplicate') !== false) {
                $message .= "group_name কলাম আগেই আছে (OK)।<br>";
            } else {
                $message .= "কলাম যোগে সমস্যা: " . $e->getMessage() . "<br>";
            }
        }

        // Add composite unique key
        try {
            $pdo->exec("ALTER TABLE class_subjects ADD UNIQUE KEY unique_class_subject_group (class_id, subject_id, group_name)");
            $message .= "নতুন unique_class_subject_group (class_id, subject_id, group_name) কী যোগ করা হয়েছে।<br>";
        } catch (Exception $e) {
            if (strpos($e->getMessage(), 'Duplicate') !== false || strpos($e->getMessage(), 'duplicate') !== false) {
                $message .= "নতুন ইউনিক কী আগেই আছে (OK)।<br>";
            } else {
                $message .= "নতুন কী যোগে সমস্যা: " . $e->getMessage() . "<br>";
            }
        }

        // Add index for performance
        try {
            $pdo->exec("ALTER TABLE class_subjects ADD INDEX idx_class_group (class_id, group_name)");
            $message .= "idx_class_group ইন্ডেক্স যোগ করা হয়েছে।<br>";
        } catch (Exception $e) {
            $message .= "ইন্ডেক্স (OK/skip)।<br>";
        }

        $type = 'success';
    } catch (Exception $e) {
        $message = "সমস্যা: " . $e->getMessage();
        $type = 'danger';
    }
}

// Check current structure
$columns = [];
$indexes = [];
try {
    $columns = $pdo->query("SHOW COLUMNS FROM class_subjects")->fetchAll(PDO::FETCH_ASSOC);
    $indexes = $pdo->query("SHOW INDEX FROM class_subjects")->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {}
?>
<!DOCTYPE html>
<html lang="bn">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0">
<title>ডাটাবেস ফিক্স</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
<style>body{background:#f0f4ff;font-size:.85rem;padding:20px}</style>
</head>
<body>
<div class="container" style="max-width:700px">
    <h5 class="mb-3"><i class="fas fa-database text-primary me-1"></i> class_subjects টেবিল ফিক্স</h5>

    <?php if ($message): ?>
    <div class="alert alert-<?= $type ?> py-2" style="font-size:.82rem"><?= $message ?></div>
    <?php endif; ?>

    <div class="card mb-3">
        <div class="card-header fw-bold" style="font-size:.85rem">বর্তমান স্ট্রাকচার</div>
        <div class="card-body p-2">
            <table class="table table-sm table-bordered mb-0" style="font-size:.78rem">
                <thead><tr><th>কলাম</th><th>টাইপ</th><th>Null</th><th>Key</th></tr></thead>
                <tbody>
                <?php foreach ($columns as $col): ?>
                <tr>
                    <td><strong><?= $col['Field'] ?></strong></td>
                    <td><?= $col['Type'] ?></td>
                    <td><?= $col['Null'] ?></td>
                    <td><?= $col['Key'] ?> <?= $col['Key'] === 'UNI' ? '('.$col['Extra'].')' : '' ?></td>
                </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <div class="card mb-3">
        <div class="card-header fw-bold" style="font-size:.85rem">ইন্ডেক্স</div>
        <div class="card-body p-2">
            <table class="table table-sm table-bordered mb-0" style="font-size:.78rem">
                <thead><tr><th>নাম</th><th>কলাম</th><th>ইউনিক</th></tr></thead>
                <tbody>
                <?php foreach ($indexes as $idx): ?>
                <tr>
                    <td><?= $idx['Key_name'] ?></td>
                    <td><?= $idx['Column_name'] ?></td>
                    <td><?= $idx['Non_unique'] ? 'না' : 'হ্যাঁ' ?></td>
                </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <form method="POST">
        <button type="submit" name="fix_db" class="btn btn-primary" onclick="return confirm('ডাটাবেস ফিক্স করতে চান?')">
            <i class="fas fa-wrench me-1"></i>ডাটাবেস ফিক্স করুন
        </button>
        <a href="teacher_subject_assignment.php" class="btn btn-secondary ms-2">বাকি থেকে ফিরুন</a>
    </form>
</div>
</body>
</html>
