<?php
/**
 * Migration: Create payment_terms and invoice_installments tables
 * Run once via browser: http://127.0.0.1:8080/scratch/migrate_payment_terms.php
 */
require_once __DIR__ . '/../includes/db.php';
global $pdo;

$results = [];

// ── 1. Create payment_terms table ──────────────────────────────────────
$pdo->exec("CREATE TABLE IF NOT EXISTS payment_terms (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    description VARCHAR(500) DEFAULT NULL,
    requires_installment TINYINT(1) NOT NULL DEFAULT 0,
    sort_order  INT NOT NULL DEFAULT 0,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
$results[] = "✅ Created table: payment_terms";

// ── 2. Seed default payment terms (only if empty) ──────────────────────
$count = $pdo->query("SELECT COUNT(*) FROM payment_terms")->fetchColumn();
if ($count == 0) {
    $terms = [
        ['Advance Payment',    'Buyer pays partial amount before production',           0, 1],
        ['Full Payment',       'Complete payment required before shipment',             0, 2],
        ['Installment Payment','Buyer pays in scheduled installments with custom dates', 1, 3],
        ['COD',                'Cash on Delivery — payment upon receipt of goods',      0, 4],
        ['Net 30',             'Payment due within 30 days of invoice date',            0, 5],
        ['Net 60',             'Payment due within 60 days of invoice date',            0, 6],
        ['Open Credit',        'Trusted buyer pays later with extended credit terms',   0, 7],
    ];
    $stmt = $pdo->prepare("INSERT INTO payment_terms (name, description, requires_installment, sort_order) VALUES (?, ?, ?, ?)");
    foreach ($terms as $t) {
        $stmt->execute($t);
    }
    $results[] = "✅ Seeded 7 default payment terms";
} else {
    $results[] = "ℹ️  payment_terms already has data, skipping seed";
}

// ── 3. Create invoice_installments table ────────────────────────────────
$pdo->exec("CREATE TABLE IF NOT EXISTS invoice_installments (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id  INT NOT NULL,
    installment_no   INT NOT NULL DEFAULT 1,
    due_date    DATE NOT NULL,
    amount      DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    description VARCHAR(255) DEFAULT NULL,
    is_paid     TINYINT(1) NOT NULL DEFAULT 0,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
$results[] = "✅ Created table: invoice_installments";

// ── 4. Add payment_term_id to production_invoices if missing ───────────
$cols = $pdo->query("SHOW COLUMNS FROM production_invoices LIKE 'payment_term_id'")->fetchAll();
if (empty($cols)) {
    $pdo->exec("ALTER TABLE production_invoices ADD COLUMN payment_term_id INT NULL DEFAULT NULL AFTER payment_terms");
    $results[] = "✅ Added column payment_term_id to production_invoices";
} else {
    $results[] = "ℹ️  Column payment_term_id already exists in production_invoices";
}

echo "<pre style='font-family:monospace;padding:2rem;'>";
echo implode("\n", $results);
echo "\n\n✅ Migration complete.";
echo "</pre>";
