<?php
require_once __DIR__ . '/../config/init.php';

// Backup current settings and users
$db = getDB();

// Get current settings
$settings = $db->query('SELECT * FROM settings LIMIT 1')->fetchArray(SQLITE3_ASSOC);

// Get current users
$users_result = $db->query('SELECT * FROM users');
$users = [];
while ($row = $users_result->fetchArray(SQLITE3_ASSOC)) {
    $users[] = $row;
}

// Get current consumption tiers
$tiers_result = $db->query('SELECT * FROM consumption_tiers ORDER BY start_range');
$tiers = [];
while ($row = $tiers_result->fetchArray(SQLITE3_ASSOC)) {
    $tiers[] = $row;
}

// Close current database connection
$db->close();

// Delete current database file
if (file_exists(__DIR__ . '/waterit.db')) {
    unlink(__DIR__ . '/waterit.db');
}

// Create new database
$db = new SQLite3(__DIR__ . '/waterit.db');

// Create tables
$schema = file_get_contents(__DIR__ . '/schema.sql');
$db->exec($schema);

// Restore settings
if ($settings) {
    $stmt = $db->prepare('INSERT INTO settings (association_name, logo_path, fixed_fees, created_at) VALUES (:name, :logo, :fees, :created)');
    $stmt->bindValue(':name', $settings['association_name']);
    $stmt->bindValue(':logo', $settings['logo_path']);
    $stmt->bindValue(':fees', $settings['fixed_fees']);
    $stmt->bindValue(':created', $settings['created_at']);
    $stmt->execute();
}

// Restore users
foreach ($users as $user) {
    $stmt = $db->prepare('INSERT INTO users (username, password, created_at) VALUES (:username, :password, :created)');
    $stmt->bindValue(':username', $user['username']);
    $stmt->bindValue(':password', $user['password']);
    $stmt->bindValue(':created', $user['created_at']);
    $stmt->execute();
}

// Restore consumption tiers
foreach ($tiers as $tier) {
    $stmt = $db->prepare('INSERT INTO consumption_tiers (start_range, end_range, price_per_unit, created_at) VALUES (:start, :end, :price, :created)');
    $stmt->bindValue(':start', $tier['start_range']);
    $stmt->bindValue(':end', $tier['end_range']);
    $stmt->bindValue(':price', $tier['price_per_unit']);
    $stmt->bindValue(':created', $tier['created_at']);
    $stmt->execute();
}

echo "Database reset successfully!\n";
echo "- Settings restored\n";
echo "- " . count($users) . " users restored\n";
echo "- " . count($tiers) . " consumption tiers restored\n";
echo "All consumption records and invoices have been cleared.\n";
?>
