<?php
ini_set('display_errors',1);
error_reporting(E_ALL);

/* ===================== DB CONFIG ===================== */
$DB_HOST = "localhost";
$DB_NAME = "jgsuite_erp";
$DB_USER = "jgsuite_erp";
$DB_PASS = "Moiz3036342@2025?";

try{
    $pdo = new PDO(
        "mysql:host=$DB_HOST;dbname=$DB_NAME;charset=utf8mb4",
        $DB_USER, $DB_PASS,
        [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]
    );
}catch(Exception $e){
    die("DB connection failed: ".$e->getMessage());
}

$msg = "";
$table = $_GET['table'] ?? null;

/* ===================== Run SQL (structure only) ===================== */
if(isset($_POST['run_sql'])){
    $sql = trim($_POST['sql']);

    if(preg_match('/\b(select|insert|update|delete|replace)\b/i',$sql)){
        $msg = "Data queries are not allowed.";
    } elseif(!preg_match('/^(alter|create|drop|truncate|rename)/i',$sql)){
        $msg = "Only structure queries allowed (ALTER, CREATE, DROP, TRUNCATE, RENAME).";
    } else {
        try{
            $pdo->exec($sql);
            $msg = "Query executed successfully.";
        }catch(Exception $e){
            $msg = $e->getMessage();
        }
    }
}

/* ===================== Drop table ===================== */
if(isset($_POST['drop_table'])){
    $t = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['table']);
    $pdo->exec("DROP TABLE `$t`");
    $msg="Table dropped.";
}

/* ===================== Add column ===================== */
if(isset($_POST['add_col'])){
    $t = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['table']);
    $name = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['col']);
    $type = trim($_POST['type']);
    $pdo->exec("ALTER TABLE `$t` ADD `$name` $type");
    $msg="Column added.";
}

/* ===================== Drop column ===================== */
if(isset($_POST['drop_col'])){
    $t = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['table']);
    $c = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['col']);
    $pdo->exec("ALTER TABLE `$t` DROP `$c`");
    $msg="Column dropped.";
}

/* ===================== Modify column ===================== */
if(isset($_POST['modify_col'])){
    $t = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['table']);
    $old = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['old']);
    $new = preg_replace('/[^a-zA-Z0-9_]/','',$_POST['new']);
    $type = trim($_POST['type']);
    $pdo->exec("ALTER TABLE `$t` CHANGE `$old` `$new` $type");
    $msg="Column updated.";
}

/* ===================== Create table ===================== */
if(isset($_POST['create_table'])){
    $sql = trim($_POST['table_name']); // actually the full CREATE TABLE query

    // Make sure only structure queries are allowed
    if(!preg_match('/^(CREATE|ALTER|DROP|TRUNCATE|RENAME)\b/i', $sql)){
        $msg = "Only structure queries allowed (CREATE, ALTER, DROP, TRUNCATE, RENAME).";
    } else {
        try {
            $pdo->exec($sql);
            $msg = "Query executed successfully.";
        } catch (Exception $e) {
            $msg = "Error: " . $e->getMessage();
        }
    }
}


/* ===================== List tables ===================== */
$tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>DB Structure Manager - phpMyAdmin Style</title>
<style>
body{
    margin:0; font-family:sans-serif; background:#1c1f26; color:#fff;
}
.container{width:90%;max-width:1200px;margin:20px auto;}
h2{margin-bottom:10px; color:#6dd6ff;}
.box{background:#252830;padding:20px;border-radius:8px;margin-bottom:20px;}
table{width:100%;border-collapse:collapse;}
th,td{padding:10px;border-bottom:1px solid #333;}
th{background:#333;}
a{color:#6dd6ff;text-decoration:none;}
button{
    padding:6px 12px;
    border:none;
    border-radius:5px;
    cursor:pointer;
    color:#fff;
}
button.add{background:#2ecc71;}
button.danger{background:#e74c3c;}
button.update{background:#f39c12;}
input,textarea{padding:6px;border-radius:5px;border:none;background:#1c1f26;color:#fff;width:100%;}
textarea{resize:none;}
.msg{padding:10px;background:#27ae60;color:#fff;border-radius:5px;margin-bottom:15px;}
.tab{display:flex;gap:10px;margin-bottom:15px;}
.tab button{background:#444;}
.tab button.active{background:#6dd6ff;color:#000;}
</style>
</head>
<body>
<div class="container">
<h2>Database Structure Manager</h2>
<small>Data queries disabled. Structure management only.</small>

<?php if($msg): ?>
<div class="msg"><?=$msg?></div>
<?php endif; ?>

<div class="box">
<h3>Tables</h3>

<table>
<tr><th>Table</th><th>Actions</th></tr>
<?php foreach($tables as $t): ?>
<tr>
<td><a href="?table=<?=$t?>"><?=$t?></a></td>
<td style="display:flex;gap:5px;">
<form method="post" onsubmit="return confirm('Drop table <?=$t?>?');">
<input type="hidden" name="table" value="<?=$t?>">
<button type="submit" name="drop_table" class="danger">Drop</button>
</form>
</td>
</tr>
<?php endforeach; ?>
</table>

<hr>
<h4>Create New Table</h4>
<form method="post" style="display:flex;gap:5px;flex-wrap:wrap;">
<input type="text" name="table_name" placeholder="Table name" required>
<button type="submit" name="create_table" class="add">Create</button>
</form>
</div>

<?php if($table): 
$tableSafe = preg_replace('/[^a-zA-Z0-9_]/','',$table);
$cols = $pdo->query("SHOW COLUMNS FROM `$tableSafe`")->fetchAll(PDO::FETCH_ASSOC);
?>
<div class="box">
<h3>Table: <?=$tableSafe?></h3>

<div class="tab">
<button class="active" onclick="showTab('columns')">Columns</button>
<button onclick="showTab('sql')">Run SQL</button>
</div>

<div id="columns" class="tab-content">
<h4>Columns</h4>
<table>
<tr><th>Column</th><th>Type</th><th>Actions</th></tr>
<?php foreach($cols as $c): ?>
<tr>
<td><?=$c['Field']?></td>
<td><?=$c['Type']?></td>
<td style="display:flex;gap:5px;">
<form method="post" onsubmit="return confirm('Drop column <?=$c['Field']?>?');">
<input type="hidden" name="table" value="<?=$tableSafe?>">
<input type="hidden" name="col" value="<?=$c['Field']?>">
<button name="drop_col" class="danger">Drop</button>
</form>
</td>
</tr>
<?php endforeach; ?>
</table>

<h4>Add Column</h4>
<form method="post" style="display:flex;gap:5px;flex-wrap:wrap;">
<input type="hidden" name="table" value="<?=$tableSafe?>">
<input type="text" name="col" placeholder="Column name" required>
<input type="text" name="type" placeholder="VARCHAR(255)" required>
<button name="add_col" class="add">Add</button>
</form>

<h4>Modify Column</h4>
<form method="post" style="display:flex;gap:5px;flex-wrap:wrap;">
<input type="hidden" name="table" value="<?=$tableSafe?>">
<input type="text" name="old" placeholder="Old name" required>
<input type="text" name="new" placeholder="New name" required>
<input type="text" name="type" placeholder="VARCHAR(255)" required>
<button name="modify_col" class="update">Update</button>
</form>
</div>

<div id="sql" class="tab-content" style="display:none;">
<h4>Run SQL (structure only)</h4>
<form method="post">
<textarea name="sql" style="width:100%;height:120px"
placeholder="Example: ALTER TABLE <?=$tableSafe?> ADD status TINYINT(1) DEFAULT 1;"></textarea>
<br><br>
<button name="run_sql" class="add">Run Query</button>
<p style="color:#ffb86b;font-size:13px">Allowed only: ALTER, CREATE, DROP, TRUNCATE, RENAME</p>
</form>
</div>

</div>
<?php endif; ?>
</div>

<script>
function showTab(tab){
    document.querySelectorAll('.tab-content').forEach(tc => tc.style.display='none');
    document.getElementById(tab).style.display='block';
    document.querySelectorAll('.tab button').forEach(b => b.classList.remove('active'));
    event.currentTarget.classList.add('active');
}
</script>

</body>
</html>
