<?php
require_once 'config/db.php';
$log = [];

// 1. Create sales_agents table
try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS sales_agents (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        name        VARCHAR(150) NOT NULL,
        phone       VARCHAR(30)  DEFAULT NULL,
        email       VARCHAR(150) DEFAULT NULL,
        designation VARCHAR(100) DEFAULT 'Sales Agent',
        is_active   TINYINT(1)   DEFAULT 1,
        created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $log[] = "✅ sales_agents table created (or already exists).";
} catch(PDOException $e) { $log[] = "❌ sales_agents: " . $e->getMessage(); }

// 2. Add assigned_agent_id column to leads
try {
    $pdo->exec("ALTER TABLE leads ADD COLUMN assigned_agent_id INT NULL DEFAULT NULL AFTER status_id");
    $log[] = "✅ leads.assigned_agent_id column added.";
} catch(PDOException $e) {
    $log[] = strpos($e->getMessage(),'Duplicate') !== false
        ? "⚠️  leads.assigned_agent_id already exists — skipped."
        : "❌ " . $e->getMessage();
}

// 3. Add FK (soft — ignore on fail)
try {
    $pdo->exec("ALTER TABLE leads ADD CONSTRAINT fk_lead_agent FOREIGN KEY (assigned_agent_id) REFERENCES sales_agents(id) ON DELETE SET NULL");
    $log[] = "✅ Foreign key added.";
} catch(PDOException $e) { $log[] = "⚠️  FK skipped: " . $e->getMessage(); }

foreach ($log as $l) echo "<p>$l</p>";
echo "<p><strong>Done! <a href='sales_agents.php'>Go to Sales Agents →</a></strong></p>";
?>
