<?php
include_once 'config.php'; enforce_admin();
ini_set('display_errors', 1);
error_reporting(E_ALL);
$db_file = "F:\\ratings_data\\stories.db";
$db = new PDO("sqlite:" . $db_file);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec("PRAGMA foreign_keys = ON;");
$message = "";
// Capture configuration change events via GET stream to comply with IIS modules
if (isset($_GET['action'])) {
$action = $_GET['action'];
// Core Action 1: Add a new universal item to a standard dropdown table
if ($action === 'add_item') {
$table = preg_replace('/[^a-z0-9_]/', '', $_GET['table'] ?? '');
$name = trim($_GET['name'] ?? '');
if (!empty($table) && !empty($name)) {
try {
$stmt = $db->prepare("INSERT INTO {$table} (name) VALUES (?)");
$stmt->execute([$name]);
$message = "<div class='alert success'>Successfully added '{$name}' globally.</div>";
} catch (Exception $e) {
$message = "<div class='alert error'>Error appending item: " . htmlspecialchars($e->getMessage()) . "</div>";
}
}
}
// Core Action 2: Incorporate custom items into the universal default list
if ($action === 'make_universal') {
$table = preg_replace('/[^a-z0-9_]/', '', $_GET['table'] ?? ''); // species, hair_colors, eye_colors
$item_id = intval($_GET['item_id'] ?? 0);
$singular = ($table === 'species') ? 'species' : rtrim($table, 's');
if (!empty($table) && $item_id > 0) {
try {
// Clear any restricted privacy links inside the junction access tables
$stmt = $db->prepare("DELETE FROM creator_{$table} WHERE {$singular}_id = ?");
$stmt->execute([$item_id]);
$message = "<div class='alert success'>Item successfully promoted to the universal schema layout list.</div>";
} catch (Exception $e) {
$message = "<div class='alert error'>Promotion engine failure: " . htmlspecialchars($e->getMessage()) . "</div>";
}
}
}
// Core Action 3: Handle dynamic custom pairing bindings for inverse rules
if ($action === 'add_reciprocal') {
$from_id = intval($_GET['from_id'] ?? 0);
$to_id = intval($_GET['to_id'] ?? 0);
if ($from_id > 0 && $to_id > 0) {
try {
$stmt = $db->prepare("INSERT OR REPLACE INTO relationship_reciprocals (from_relationship_id, to_relationship_id) VALUES (?, ?)");
$stmt->execute([$from_id, $to_id]);
// Mirror it to ensure complete bidirectional symmetry matching
if ($from_id !== $to_id) {
$stmt = $db->prepare("INSERT OR REPLACE INTO relationship_reciprocals (from_relationship_id, to_relationship_id) VALUES (?, ?)");
$stmt->execute([$to_id, $from_id]);
}
$message = "<div class='alert success'>Reciprocal pairing logged successfully.</div>";
} catch (Exception $e) {
$message = "<div class='alert error'>Rule logging failure: " . htmlspecialchars($e->getMessage()) . "</div>";
}
}
}
}
// Helper wrapper to fetch lookup options and track if they are custom or universal
function fetchManagedLookup($db, $table) {
$singular = ($table === 'species') ? 'species' : rtrim($table, 's');
// If a junction link exists, it's a creator's custom entry. If not, it's global.
$sql = "SELECT DISTINCT s.id, s.name,
CASE WHEN c.creator_id IS NOT NULL THEN 1 ELSE 0 END as is_custom
FROM {$table} s
LEFT JOIN creator_{$table} c ON s.id = c.{$singular}_id
ORDER BY is_custom ASC, s.name ASC";
return $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}
$standardTables = [
'genders' => $db->query("SELECT id, name, 0 as is_custom FROM genders ORDER BY id ASC")->fetchAll(PDO::FETCH_ASSOC),
'sexualities' => $db->query("SELECT id, name, 0 as is_custom FROM sexualities ORDER BY id ASC")->fetchAll(PDO::FETCH_ASSOC),
'relationship_types' => $db->query("SELECT id, name, 0 as is_custom FROM relationship_types ORDER BY id ASC")->fetchAll(PDO::FETCH_ASSOC)
];
$dynamicTables = ['species', 'hair_colors', 'eye_colors'];
$managedLookups = [];
foreach ($dynamicTables as $t) {
$managedLookups[$t] = fetchManagedLookup($db, $t);
}
$recipLog = $db->query("SELECT r.from_relationship_id, t1.name as from_name, r.to_relationship_id, t2.name as to_name
FROM relationship_reciprocals r
JOIN relationship_types t1 ON r.from_relationship_id = t1.id
JOIN relationship_types t2 ON r.to_relationship_id = t2.id
ORDER BY t1.name ASC")->fetchAll(PDO::FETCH_ASSOC);
?>
<body>
<div class="container">
<h1>Database Dropdowns & Relationship Rules Manager</h1>
<p style="color: #64748b; margin-top: -10px; margin-bottom: 25px;">Modify existing tables, expand options, and manage dynamic reciprocal behaviors.</p>
<?php echo $message; ?>
<div class="grid">
<?php
// Render standard lookup cards sequentially
foreach (array_merge($standardTables, $managedLookups) as $tableName => $rows):
?>
<div class="card">
<h2>Manage Table: <?php echo htmlspecialchars($tableName); ?></h2>
<div class="item-list">
<?php foreach ($rows as $r): ?>
<div class="item-row">
<span><strong>#<?php echo $r['id']; ?></strong> – <?php echo htmlspecialchars($r['name']); ?></span>
<?php if (($r['is_custom'] ?? 0) == 1): ?>
<div>
<span class="tag-custom">Custom</span>
<a href="modify_db.php?action=make_universal&table=<?php echo $tableName; ?>&item_id=<?php echo $r['id']; ?>" class="btn-action" style="background:#166534;">Promote</a>
</div>
<?php endif; ?>
</div>
<?php endforeach; ?>
</div>
<div class="add-box">
<input type="text" id="input_<?php echo $tableName; ?>" placeholder="New Item Name...">
<button type="button" class="btn-action" style="padding:6px 12px; font-size:13px;" onclick="submitNewItem('<?php echo $tableName; ?>')">Add</button>
</div>
</div>
<?php endforeach; ?>
</div>
<div class="card" style="margin-bottom: 40px; margin-top: 20px;">
<h2>Smart Reciprocal Symmetry Matrix Rules</h2>
<!-- Replaced conflicting class with a clean dedicated styling wrapper -->
<div style="display: flex; flex-wrap: wrap; gap: 30px; margin-top: 15px;">
<div style="flex: 1; min-width: 300px;">
<strong style="font-size:13px; display:block; margin-bottom:8px; color:#475569;">Active Rule Configuration Maps:</strong>
<div class="item-list" style="max-height: 250px; height: auto;">
<?php foreach ($recipLog as $rl): ?>
<div class="item-row" style="padding: 8px 5px;">
<span>If choice is: <strong><?php echo htmlspecialchars($rl['from_name']); ?></strong></span>
<span style="color:#64748b;">→ Opposite card flips to: <strong><?php echo htmlspecialchars($rl['to_name']); ?></strong></span>
</div>
<?php endforeach; ?>
</div>
</div>
<div style="flex: 1; min-width: 300px; background: #f8fafc; padding: 15px; border-radius: 6px; border: 1px solid #e2e8f0;">
<strong style="font-size:13px; display:block; margin-bottom:12px; color:#475569;">Map New Reciprocal Constraints Pair:</strong>
<div style="margin-bottom: 15px;">
<label style="display:block; font-size:12px; font-weight:bold; margin-bottom:6px; color:#334155;">When Dropdown A Selection is Set To:</label>
<select id="recip_from" style="width: 100%; padding: 8px; border: 1px solid #cbd5e1; border-radius: 4px;">
<?php foreach ($standardTables['relationship_types'] as $rt) { echo "<option value='{$rt['id']}'>{$rt['name']}</option>"; } ?>
</select>
</div>
<div style="margin-bottom: 20px;">
<label style="display:block; font-size:12px; font-weight:bold; margin-bottom:6px; color:#334155;">Force Dropdown B Selection To Automatically Flip To:</label>
<select id="recip_to" style="width: 100%; padding: 8px; border: 1px solid #cbd5e1; border-radius: 4px;">
<?php foreach ($standardTables['relationship_types'] as $rt) { echo "<option value='{$rt['id']}'>{$rt['name']}</option>"; } ?>
</select>
</div>
<button type="button" class="btn-action" style="width:100%; padding:10px; font-size:13px; cursor:pointer; text-align:center;" onclick="submitNewReciprocal()">Establish Bidirectional Symmetry Link</button>
</div>
</div>
</div>
</div>
<script>
function submitNewItem(tableName) {
const txtEl = document.getElementById('input_' + tableName);
if (!txtEl) return;
const value = encodeURIComponent(txtEl.value.trim());
if (!value) { alert('Please enter a valid option label.'); return; }
window.location.href = `modify_db.php?action=add_item&table=${tableName}&name=${value}`;
}
function submitNewReciprocal() {
const fromId = document.getElementById('recip_from').value;
const toId = document.getElementById('recip_to').value;
window.location.href = `modify_db.php?action=add_reciprocal&from_id=${fromId}&to_id=${toId}`;
}
</script>
</body>
</html>
modify_db