<?php
// Define our absolute target path variables explicitly
$db_directory = "F:\\ratings_data";
$db_file = $db_directory . "\\stories.db";
$message = "";
// STEP 1: Handle file deletion independently if requested via GET
if (isset($_GET['action']) && $_GET['action'] === 'wipe_database') {
if (file_exists($db_file)) {
try {
unlink($db_file);
$message = "<div class='alert success'>Current stories.db dropped completely. Re-instantiating schema pipeline...</div>";
} catch (Exception $e) {
$err = htmlspecialchars($e->getMessage());
$message = "<div class='alert error'>Failed to delete database file: $err</div>";
}
}
}
// Ensure target directory exists on the F: drive before proceeding
if (!file_exists($db_directory)) {
mkdir($db_directory, 0777, true);
}
try {
// STEP 2: Open a clean, fresh connection pipeline to build or mount the file
$db = new PDO("sqlite:" . $db_file);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec("PRAGMA foreign_keys = ON;");
// 1. CORE RELATION TABLES
$db->exec("CREATE TABLE IF NOT EXISTS creators (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
)");
$db->exec("CREATE TABLE IF NOT EXISTS stories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
creator_id INTEGER NOT NULL,
title TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (creator_id) REFERENCES creators(id) ON DELETE CASCADE
)");
// 2. STANDARD DROP-DOWN LOOKUPS
$lookupTables = ['genders', 'sexualities', 'ages', 'heights', 'weights', 'relationship_types'];
foreach ($lookupTables as $table) {
$db->exec("CREATE TABLE IF NOT EXISTS $table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
)");
}
// 3. DYNAMIC LOOKUPS ("Other" tracking infrastructure)
$dynamicTables = ['species', 'hair_colors', 'eye_colors'];
foreach ($dynamicTables as $table) {
$db->exec("CREATE TABLE IF NOT EXISTS $table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
)");
}
// 4. CREATOR PRIVACY JUNCTION FILTERS
foreach ($dynamicTables as $table) {
$singular = ($table === 'species') ? 'species' : rtrim($table, 's');
$db->exec("CREATE TABLE IF NOT EXISTS creator_$table (
creator_id INTEGER NOT NULL,
{$singular}_id INTEGER NOT NULL,
PRIMARY KEY (creator_id, {$singular}_id),
FOREIGN KEY (creator_id) REFERENCES creators(id) ON DELETE CASCADE,
FOREIGN KEY ({$singular}_id) REFERENCES $table(id) ON DELETE CASCADE
)");
}
// NEW ATTACHMENT: Creator-Level Reusable Primary Locations Table
$db->exec("CREATE TABLE IF NOT EXISTS locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
creator_id INTEGER NOT NULL,
name TEXT NOT NULL,
UNIQUE(creator_id, name),
FOREIGN KEY (creator_id) REFERENCES creators(id) ON DELETE CASCADE
)");
// 5. THE CHARACTERS REPOSITORY MATRIX
$db->exec("CREATE TABLE IF NOT EXISTS characters (
id INTEGER PRIMARY KEY AUTOINCREMENT,
story_id INTEGER NOT NULL,
first_name TEXT DEFAULT '',
last_name TEXT DEFAULT '',
nickname TEXT DEFAULT '',
gender_id INTEGER NOT NULL,
sexuality_id INTEGER NOT NULL,
species_id INTEGER NOT NULL,
age_id INTEGER NOT NULL,
hair_color_id INTEGER NOT NULL,
eye_color_id INTEGER NOT NULL,
height_id INTEGER NOT NULL,
weight_id INTEGER NOT NULL,
FOREIGN KEY (story_id) REFERENCES stories(id) ON DELETE CASCADE,
FOREIGN KEY (gender_id) REFERENCES genders(id),
FOREIGN KEY (sexuality_id) REFERENCES sexualities(id),
FOREIGN KEY (species_id) REFERENCES species(id),
FOREIGN KEY (age_id) REFERENCES ages(id),
FOREIGN KEY (hair_color_id) REFERENCES hair_colors(id),
FOREIGN KEY (eye_color_id) REFERENCES eye_colors(id),
FOREIGN KEY (height_id) REFERENCES heights(id),
FOREIGN KEY (weight_id) REFERENCES weights(id)
)");
// 6. CHARACTER RELATIONSHIPS (Bidirectional Grid links)
$db->exec("CREATE TABLE IF NOT EXISTS character_relationships (
story_id INTEGER NOT NULL,
character_id_1 INTEGER NOT NULL,
character_id_2 INTEGER NOT NULL,
relationship_type_id INTEGER NOT NULL,
PRIMARY KEY (character_id_1, character_id_2),
FOREIGN KEY (story_id) REFERENCES stories(id) ON DELETE CASCADE,
FOREIGN KEY (character_id_1) REFERENCES characters(id) ON DELETE CASCADE,
FOREIGN KEY (character_id_2) REFERENCES characters(id) ON DELETE CASCADE,
FOREIGN KEY (relationship_type_id) REFERENCES relationship_types(id)
)");
// 7. SMART RECIPROCAL RULES DICTIONARY
$db->exec("CREATE TABLE IF NOT EXISTS relationship_reciprocals (
from_relationship_id INTEGER NOT NULL,
to_relationship_id INTEGER NOT NULL,
PRIMARY KEY (from_relationship_id, to_relationship_id),
FOREIGN KEY (from_relationship_id) REFERENCES relationship_types(id) ON DELETE CASCADE,
FOREIGN KEY (to_relationship_id) REFERENCES relationship_types(id) ON DELETE CASCADE
)");
// NEW ATTACHMENT: Scenes Chronological Metadata Entry Log Table
$db->exec("CREATE TABLE IF NOT EXISTS scenes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
story_id INTEGER NOT NULL,
scene_number INTEGER NOT NULL,
title TEXT DEFAULT '',
location_id INTEGER NOT NULL,
sub_location TEXT DEFAULT '',
description TEXT DEFAULT '',
FOREIGN KEY (story_id) REFERENCES stories(id) ON DELETE CASCADE,
FOREIGN KEY (location_id) REFERENCES locations(id)
)");
// NEW ATTACHMENT: Scene Involved Cast Junction Board (Tracks 2.C Statuses)
$db->exec("CREATE TABLE IF NOT EXISTS scene_characters (
scene_id INTEGER NOT NULL,
character_id INTEGER NOT NULL,
character_status TEXT DEFAULT '',
PRIMARY KEY (scene_id, character_id),
FOREIGN KEY (scene_id) REFERENCES scenes(id) ON DELETE CASCADE,
FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE
)");
// NEW ATTACHMENT: Timeline Relationship Shift Log Table (Tracks 2.D Events)
$db->exec("CREATE TABLE IF NOT EXISTS scene_relationship_shifts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
scene_id INTEGER NOT NULL,
character_id_1 INTEGER NOT NULL,
character_id_2 INTEGER NOT NULL,
relationship_type_id INTEGER NOT NULL,
FOREIGN KEY (scene_id) REFERENCES scenes(id) ON DELETE CASCADE,
FOREIGN KEY (character_id_1) REFERENCES characters(id) ON DELETE CASCADE,
FOREIGN KEY (character_id_2) REFERENCES characters(id) ON DELETE CASCADE,
FOREIGN KEY (relationship_type_id) REFERENCES relationship_types(id)
)");
// --- AUTOMATED BASE SEEDING DATA LOADING ---
$seed = function($db, $table, $items) {
$stmt = $db->prepare("INSERT OR IGNORE INTO $table (name) VALUES (?)");
foreach ($items as $item) { $stmt->execute([$item]); }
};
$seed($db, 'genders', ['None', 'Male', 'Female', 'Hermaphrodite']);
$seed($db, 'sexualities', ['None', 'Straight', 'Gay', 'Lesbian', 'Pansexual', 'Asexual']);
$seed($db, 'hair_colors', ['None', 'Random', 'Other', 'Brown', 'Black', 'Blonde', 'Red', 'Gray']);
$seed($db, 'eye_colors', ['None', 'Random', 'Other', 'Blue', 'Green', 'Hazel', 'Brown', 'Ice Blue']);
$seed($db, 'species', ['None', 'Other', 'Human', 'Elf', 'Ogre', 'Dog', 'Wolf', 'Horse', 'Sheep', 'Goat', 'Pig', 'Zebra', 'Lion', 'Unicorn', 'Minotaur', 'Cerberus', 'Chimera', 'Dragon', 'Sphinx']);
// Build property list numeric ranges
$ages = ['None']; for($i=0; $i<=250; $i++) { $ages[] = (string)$i; }
$seed($db, 'ages', $ages);
$heights = ['None']; for($f=1; $f<=8; $f++) { for($i=0; $i<12; $i++) { if($f==8 && $i>0) break; $heights[] = "{$f}'{$i}\""; } }
$seed($db, 'heights', $heights);
$weights = ['None']; for($w=1; $w<=400; $w++) { $weights[] = "$w lbs"; }
$seed($db, 'weights', $weights);
$relTypes = ['None', 'Spouse', 'Parent', 'Offspring', 'Sibling', 'Grandparent', 'Niece', 'Nephew', 'Cousin', 'In-law', 'Fiancé / Fiancée', 'Friend', 'Neighbor', 'Roommate', 'Landlord', 'Tenant', 'Employer', 'Employee', 'Coworker', 'Ex-Spouse', 'Step-Sibling', 'Step-Parent', 'Step-Offspring'];
$seed($db, 'relationship_types', $relTypes);
$getRelId = function($db, $name) {
$stmt = $db->prepare("SELECT id FROM relationship_types WHERE name = ?");
$stmt->execute([$name]);
return $stmt->fetchColumn();
};
$rules = [
['None', 'None'], ['Spouse', 'Spouse'], ['Sibling', 'Sibling'], ['Cousin', 'Cousin'],
['Friend', 'Friend'], ['Neighbor', 'Neighbor'], ['Roommate', 'Roommate'], ['Coworker', 'Coworker'],
['In-law', 'In-law'], ['Fiancé / Fiancée', 'Fiancé / Fiancée'],
['Parent', 'Offspring'], ['Offspring', 'Parent'],
['Employer', 'Employee'], ['Employee', 'Employer'],
['Landlord', 'Tenant'], ['Tenant', 'Landlord'],
['Ex-Spouse', 'Ex-Spouse'], ['Step-Sibling', 'Step-Sibling'],
['Step-Parent', 'Step-Offspring'], ['Step-Offspring', 'Step-Parent']
];
$stmtRule = $db->prepare("INSERT OR IGNORE INTO relationship_reciprocals (from_relationship_id, to_relationship_id) VALUES (?, ?)");
foreach ($rules as $rule) {
$id1 = $getRelId($db, $rule[0]); $id2 = $getRelId($db, $rule[1]);
if ($id1 && $id2) {
$stmtRule->execute([$id1, $id2]);
if ($id1 !== $id2) { $stmtRule->execute([$id2, $id1]); }
}
}
// Seed standard Macro Locations using creator_id 0 as the global default link
$defaultLocations = [
'None', 'Kingdom', 'Village', 'Town', 'Outpost', 'Settlement', 'City', 'Citadel',
'Castle', 'Fortress', 'Tower', 'Palace', 'House', 'Tavern / Inn', 'Temple', 'Shop', 'Ruins',
'Forest', 'Mountain', 'Valley', 'River', 'Lake', 'Cave', 'Desert', 'Ocean', 'Island', 'Swamp',
'Cruise Ship', 'Carriage', 'Wagon', 'Airship'
];
$stmtLoc = $db->prepare("INSERT OR IGNORE INTO locations (creator_id, name) VALUES (0, ?)");
foreach ($defaultLocations as $loc) { $stmtLoc->execute([$loc]); }
$message .= "<div class='alert success'>Database engine deployed successfully and baseline rows initialized!</div>";
} catch (PDOException $e) {
$message .= "<div class='alert error'>Database setup execution failure: " . htmlspecialchars($e->getMessage()) . "</div>";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Database System Core Initializer</title>
<style>
body { font-family: sans-serif; background: #f0f2f5; padding: 40px; margin: 0; }
.card { max-width: 650px; background: white; margin: 0 auto; padding: 30px; border-radius: 12px; box-shadow: 0 4px 12px rgba(0,0,0,0.08); }
h1 { color: #1e293b; font-size: 24px; margin-top: 0; margin-bottom: 10px; }
p { color: #64748b; font-size: 14px; margin-bottom: 25px; line-height: 1.5; }
.alert { padding: 15px; border-radius: 6px; font-size: 14px; font-weight: bold; margin-bottom: 20px; }
.success { background: #f0fdf4; color: #166534; border: 1px solid #bbf7d0; }
.error { background: #fef2f2; color: #991b1b; border: 1px solid #fecaca; }
.btn-danger { display: inline-block; text-decoration: none; background: #dc2626; color: white; padding: 12px 20px; font-size: 14px; font-weight: 600; border-radius: 6px; cursor: pointer; width: 100%; text-align: center; box-sizing: border-box; }
.btn-danger:hover { background: #b91c1c; }
.meta-box { background: #f8fafc; border: 1px solid #e2e8f0; padding: 15px; border-radius: 6px; margin-top: 25px; font-size: 13px; color: #475569; font-family: monospace; }
</style>
</head>
<body>
<div class="card">
<h1>Database System Framework Setup</h1>
<p>This panel initializes the relational tables, index mappings, and reciprocal dictionary matrices needed to drive your application.</p>
<a href="setup_db.php?action=wipe_database" class="btn-danger" onclick="return confirm('WARNING: Erase all current story and character entries permanently. Proceed?');">
Wipe Database & Recreate Schema
</a>
<div class="meta-box">
<div><strong>Active Configuration Parameters:</strong></div>
<div>Path: <?php echo htmlspecialchars($db_file); ?></div>
<div>Engine Mode: SQLite3 (PDO)</div>
</div>
<br><?php echo $message; ?>
</div>
</body>
</html>
setup_db