<?php
// Script to convert SQLite database to SQL dump specifically for MySQL/MariaDB import

$sqlite_path = 'database/database.sqlite';
$output_file = 'database_dump.sql';

if (!file_exists($sqlite_path)) {
    die("SQLite database file not found: $sqlite_path\n");
}

try {
    $db = new PDO("sqlite:$sqlite_path");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage() . "\n");
}

$tables = [];
$result = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'");
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $tables[] = $row['name'];
}

$sql_dump = "-- Laundry Box Database Dump\n";
$sql_dump .= "-- Generated on " . date('Y-m-d H:i:s') . "\n\n";
$sql_dump .= "SET FOREIGN_KEY_CHECKS=0;\n";
$sql_dump .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n\n";

foreach ($tables as $table) {
    // Get table structure
    $stmt = $db->prepare("PRAGMA table_info($table)");
    $stmt->execute();
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sql_dump .= "-- Structure for table `$table`\n";
    $sql_dump .= "DROP TABLE IF EXISTS `$table`;\n";
    $sql_dump .= "CREATE TABLE `$table` (\n";

    $primary_keys = [];
    $col_defs = [];

    foreach ($columns as $col) {
        $name = $col['name'];
        $type = strtoupper($col['type']);
        $notnull = $col['notnull'] ? 'NOT NULL' : 'NULL';
        $default = $col['dflt_value'] !== null ? "DEFAULT " . $col['dflt_value'] : '';
        
        // Map SQLite types to MySQL types
        if ($type == 'INTEGER') $type = 'BIGINT'; // Safer for IDs
        if (strpos($type, 'VARCHAR') !== false) $type = 'VARCHAR(255)';
        if ($type == '') $type = 'TEXT'; // Fallback

        // Auto increment usually implies Primary Key in SQLite
        $extra = '';
        if ($col['pk']) {
            $primary_keys[] = "`$name`";
            if ($type == 'BIGINT') $extra = 'AUTO_INCREMENT';
        }

        $col_defs[] = "  `$name` $type $notnull $default $extra";
    }
    
    $sql_dump .= implode(",\n", $col_defs);
    if (!empty($primary_keys)) {
        $sql_dump .= ",\n  PRIMARY KEY (" . implode(',', $primary_keys) . ")";
    }
    $sql_dump .= "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;\n\n";

    // GetData
    $rows = $db->query("SELECT * FROM $table")->fetchAll(PDO::FETCH_ASSOC);
    if (count($rows) > 0) {
        $sql_dump .= "-- Dumping data for table `$table`\n";
        $sql_dump .= "INSERT INTO `$table` VALUES\n";
        
        $values_list = [];
        foreach ($rows as $row) {
            $row_values = [];
            foreach ($row as $val) {
                if ($val === null) {
                    $row_values[] = "NULL";
                } else {
                    $row_values[] = $db->quote($val); // Escape properly
                }
            }
            $values_list[] = "(" . implode(", ", $row_values) . ")";
        }
        $sql_dump .= implode(",\n", $values_list) . ";\n\n";
    }
}

$sql_dump .= "SET FOREIGN_KEY_CHECKS=1;\n";

file_put_contents($output_file, $sql_dump);

echo "Database successfully exported to $output_file\n";
