<?php
/**
 * JUSTSALE POS - Database Migration Script
 * Migrates local database from v2.0.4 to v2.1.4
 * Adds: Credit Note System, SMTP settings, & enhanced Branding
 */

// 1. Load Database Connection
// Use the relative path to db.php. Note: db.php has a licensing check.
// If you are blocked by the license check, update your .license.lock first.
require_once 'api/db.php';

try {
    echo "<h2>JUSTSALE Database Migration Tool (v2.1.4)</h2>";
    echo "<p>Connected to database: <strong>$dbname</strong></p>";
    echo "<hr>";

    // 2. Create Credit Notes Table
    echo "Creating 'credit_notes' table... ";
    $sql_cn = "CREATE TABLE IF NOT EXISTS `credit_notes` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `sale_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `shift_id` int(11) DEFAULT NULL,
      `customer_id` int(11) DEFAULT NULL,
      `total_refunded` decimal(12,2) NOT NULL DEFAULT 0.00,
      `reason` text DEFAULT NULL,
      `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
      `approval_user_id` int(11) DEFAULT NULL,
      `approved_at` timestamp NULL DEFAULT NULL,
      `created_at` timestamp DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    $pdo->exec($sql_cn);
    echo "<span style='color:green'>Success</span><br>";

    // 3. Create Credit Note Items Table
    echo "Creating 'credit_note_items' table... ";
    $sql_cni = "CREATE TABLE IF NOT EXISTS `credit_note_items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `credit_note_id` int(11) NOT NULL,
      `sale_item_id` int(11) NOT NULL,
      `product_id` int(11) NOT NULL,
      `quantity_refunded` int(11) NOT NULL,
      `refund_amount` decimal(12,2) NOT NULL,
      PRIMARY KEY (`id`),
      FOREIGN KEY (`credit_note_id`) REFERENCES `credit_notes` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    $pdo->exec($sql_cni);
    echo "<span style='color:green'>Success</span><br>";

    // 4. Add Missing System Settings
    echo "Synchronizing system settings... ";
    $new_settings = [
        ['company_city', ''],
        ['company_country', ''],
        ['company_website', ''],
        ['smtp_host', ''],
        ['smtp_port', '587'],
        ['smtp_user', ''],
        ['smtp_pass', ''],
        ['smtp_encryption', 'tls'],
        ['smtp_from_email', ''],
        ['smtp_from_name', 'JUSTSALE POS'],
        ['receipt_header', ''],
        ['receipt_footer', 'Thank you for shopping with us!'],
        ['receipt_customer_pos', 'top'],
        ['receipt_show_tin', 'yes'],
        ['pos_allow_price_edit', '0']
    ];

    $stmt = $pdo->prepare("INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES (?, ?)");
    foreach ($new_settings as $s) {
        $stmt->execute($s);
    }
    echo "<span style='color:green'>Success</span><br>";

    // 5. Update System Version in Settings (if applicable)
    $pdo->prepare("INSERT INTO settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)")
        ->execute(['system_version', '2.1.4']);

    echo "<hr>";
    echo "<h3 style='color:blue'>Migration Complete!</h3>";
    echo "<p>Your database is now fully compatible with JUSTSALE v2.1.4.</p>";
    echo "<p><a href='index.html'>Click here to return to the Dashboard</a></p>";
    echo "<p><small>Note: You can safely delete this file (update_database_v2_0_5.php) from your server now.</small></p>";

} catch (Exception $e) {
    echo "<hr>";
    echo "<h3 style='color:red'>Migration Failed!</h3>";
    echo "<p>Error: " . $e->getMessage() . "</p>";
    echo "<p>Please ensure you have configured your database connection in <strong>api/db.php</strong>.</p>";
}



