<?php
declare(strict_types=1);

/*
 * consultants.php
 * Robust version:
 * - Does not white-screen/HTTP 500 when the agents/consultants schema differs.
 * - Detects available columns before querying.
 * - Shows friendly on-page errors instead of uncaught PDO exceptions.
 */

ini_set('display_errors', '0');
ini_set('log_errors', '1');
error_reporting(E_ALL);

require_once __DIR__ . '/../../includes/bootstrap.php';

if (function_exists('require_login')) {
    require_login();
}

$pageTitle = 'Consultants';

if (!function_exists('h')) {
    function h($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('db_columns')) {
    function db_columns(PDO $pdo, string $table): array
    {
        static $cache = [];

        if (isset($cache[$table])) {
            return $cache[$table];
        }

        try {
            $stmt = $pdo->query("SHOW COLUMNS FROM `" . str_replace('`', '``', $table) . "`");
            $cols = [];
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $cols[] = (string)$row['Field'];
            }
            $cache[$table] = $cols;
            return $cols;
        } catch (Throwable $e) {
            $cache[$table] = [];
            return [];
        }
    }
}

if (!function_exists('has_col')) {
    function has_col(array $columns, string $column): bool
    {
        return in_array($column, $columns, true);
    }
}

if (!function_exists('first_existing_col')) {
    function first_existing_col(array $columns, array $candidates): ?string
    {
        foreach ($candidates as $candidate) {
            if (has_col($columns, $candidate)) {
                return $candidate;
            }
        }
        return null;
    }
}

if (!function_exists('sql_col')) {
    function sql_col(string $alias, string $column): string
    {
        return "`" . str_replace('`', '``', $alias) . "`.`" . str_replace('`', '``', $column) . "`";
    }
}

if (!function_exists('consultant_full_name')) {
    function consultant_full_name(array $row): string
    {
        $name = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
        if ($name === '') {
            $name = trim((string)($row['name'] ?? ''));
        }
        return $name;
    }
}

if (!isset($pdo) || !($pdo instanceof PDO)) {
    die('Database connection $pdo was not found. Please check includes/bootstrap.php.');
}

$form = [
    'id'         => 0,
    'firstname'  => '',
    'lastname'   => '',
    'email'      => '',
    'agent_id'   => '',
    'mobile'     => '',
    'remarks'    => '',
    'attachment' => '',
    'phone'      => '',
    'skype'      => '',
];

$errors = [];
$success = '';
$editing = false;

if (isset($_GET['saved']) && (int)$_GET['saved'] === 1) {
    $success = 'Consultant saved successfully.';
}
if (isset($_GET['deleted']) && (int)$_GET['deleted'] === 1) {
    $success = 'Consultant deleted successfully.';
}

/*
 * Schema detection
 */
$consultantTable = 'consultants';
$agentTable = 'agents';

$consultantCols = db_columns($pdo, $consultantTable);
$agentCols = db_columns($pdo, $agentTable);

if (!$consultantCols) {
    $errors[] = "Table `consultants` was not found or cannot be read.";
}
if (!$agentCols) {
    $errors[] = "Table `agents` was not found or cannot be read.";
}

/*
 * Required consultant columns for this screen.
 */
$requiredConsultantCols = ['id', 'firstname', 'lastname', 'email', 'agent_id', 'mobile'];
foreach ($requiredConsultantCols as $requiredCol) {
    if ($consultantCols && !has_col($consultantCols, $requiredCol)) {
        $errors[] = "Missing required column `consultants`.`{$requiredCol}`.";
    }
}

$agentIdCol = first_existing_col($agentCols, ['id', 'AgentID', 'agent_id']);
$agentFirstCol = first_existing_col($agentCols, ['firstname', 'FirstName', 'first_name']);
$agentLastCol = first_existing_col($agentCols, ['lastname', 'LastName', 'last_name']);
$agentNameCol = first_existing_col($agentCols, ['Agent', 'agent', 'name', 'Name', 'company_name', 'CompanyName']);
$agentOfficeCol = first_existing_col($agentCols, ['office', 'Office', 'city', 'City', 'location', 'Location']);

if ($agentCols && !$agentIdCol) {
    $errors[] = "Could not find an ID column on `agents`. Expected one of: id, AgentID, agent_id.";
}
if ($agentCols && !$agentNameCol && (!$agentFirstCol || !$agentLastCol)) {
    $errors[] = "Could not find agent name columns on `agents`. Expected `Agent` or firstname/lastname.";
}

$optionalConsultantCols = [
    'remarks'    => has_col($consultantCols, 'remarks'),
    'attachment' => has_col($consultantCols, 'attachment'),
    'phone'      => has_col($consultantCols, 'phone'),
    'skype'      => has_col($consultantCols, 'skype'),
];

/*
 * Agent dropdown
 */
$agents = [];
if ($agentCols && $agentIdCol && ($agentNameCol || ($agentFirstCol && $agentLastCol))) {
    try {
        $select = [
            sql_col('a', $agentIdCol) . " AS id",
        ];

        if ($agentNameCol) {
            $select[] = sql_col('a', $agentNameCol) . " AS agent_name";
        } else {
            $select[] = "TRIM(CONCAT(COALESCE(" . sql_col('a', $agentFirstCol) . ", ''), ' ', COALESCE(" . sql_col('a', $agentLastCol) . ", ''))) AS agent_name";
        }

        if ($agentOfficeCol) {
            $select[] = sql_col('a', $agentOfficeCol) . " AS office";
        } else {
            $select[] = "'' AS office";
        }

        $orderExpr = $agentNameCol ? sql_col('a', $agentNameCol) : "agent_name";

        $stmt = $pdo->query("
            SELECT " . implode(",\n                   ", $select) . "
            FROM `" . str_replace('`', '``', $agentTable) . "` a
            ORDER BY {$orderExpr}
        ");
        $agents = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        error_log('Consultants agents query failed: ' . $e->getMessage());
        $errors[] = 'Could not load agents: ' . $e->getMessage();
    }
}

/*
 * Save
 */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_consultant'])) {
    $form['id']         = (int)($_POST['id'] ?? 0);
    $form['firstname']  = trim((string)($_POST['firstname'] ?? ''));
    $form['lastname']   = trim((string)($_POST['lastname'] ?? ''));
    $form['email']      = trim((string)($_POST['email'] ?? ''));
    $form['agent_id']   = trim((string)($_POST['agent_id'] ?? ''));
    $form['mobile']     = trim((string)($_POST['mobile'] ?? ''));
    $form['remarks']    = trim((string)($_POST['remarks'] ?? ''));
    $form['attachment'] = trim((string)($_POST['attachment'] ?? ''));
    $form['phone']      = trim((string)($_POST['phone'] ?? ''));
    $form['skype']      = trim((string)($_POST['skype'] ?? ''));

    $editing = $form['id'] > 0;

    if ($form['firstname'] === '') {
        $errors[] = 'First name is required.';
    }
    if ($form['lastname'] === '') {
        $errors[] = 'Last name is required.';
    }
    if ($form['email'] === '') {
        $errors[] = 'Email is required.';
    } elseif (!filter_var($form['email'], FILTER_VALIDATE_EMAIL)) {
        $errors[] = 'Please enter a valid email address.';
    }
    if ($form['agent_id'] === '' || (int)$form['agent_id'] <= 0) {
        $errors[] = 'Please select an agent.';
    }
    if ($form['mobile'] === '') {
        $errors[] = 'Mobile is required.';
    }

    if (!$errors) {
        try {
            $stmt = $pdo->prepare("
                SELECT id
                FROM consultants
                WHERE email = :email
                  AND id <> :id
                LIMIT 1
            ");
            $stmt->execute([
                ':email' => $form['email'],
                ':id'    => (int)$form['id'],
            ]);

            if ($stmt->fetch(PDO::FETCH_ASSOC)) {
                $errors[] = 'A consultant with that email already exists.';
            }
        } catch (Throwable $e) {
            error_log('Consultants duplicate email check failed: ' . $e->getMessage());
            $errors[] = $e->getMessage();
        }
    }

    if (!$errors) {
        try {
            $data = [
                'firstname' => $form['firstname'],
                'lastname'  => $form['lastname'],
                'email'     => $form['email'],
                'agent_id'  => (int)$form['agent_id'],
                'mobile'    => $form['mobile'],
            ];

            foreach ($optionalConsultantCols as $col => $exists) {
                if ($exists) {
                    $data[$col] = $form[$col] !== '' ? $form[$col] : null;
                }
            }

            if ($editing) {
                $sets = [];
                $params = [];
                foreach ($data as $col => $value) {
                    $sets[] = "`{$col}` = :{$col}";
                    $params[":{$col}"] = $value;
                }
                $params[':id'] = (int)$form['id'];

                $stmt = $pdo->prepare("
                    UPDATE consultants
                    SET " . implode(",\n                        ", $sets) . "
                    WHERE id = :id
                ");
                $stmt->execute($params);
            } else {
                $cols = array_keys($data);
                $placeholders = array_map(static fn($col) => ':' . $col, $cols);
                $params = [];
                foreach ($data as $col => $value) {
                    $params[":{$col}"] = $value;
                }

                $stmt = $pdo->prepare("
                    INSERT INTO consultants (`" . implode('`, `', $cols) . "`)
                    VALUES (" . implode(', ', $placeholders) . ")
                ");
                $stmt->execute($params);
            }

            header('Location: consultants.php?saved=1');
            exit;
        } catch (Throwable $e) {
            error_log('Consultants save failed: ' . $e->getMessage());
            $errors[] = $e->getMessage();
        }
    }
}

/*
 * Delete
 */
if (isset($_GET['delete']) && (int)$_GET['delete'] > 0) {
    $deleteId = (int)$_GET['delete'];

    try {
        $stmt = $pdo->prepare("DELETE FROM consultants WHERE id = :id");
        $stmt->execute([':id' => $deleteId]);

        header('Location: consultants.php?deleted=1');
        exit;
    } catch (Throwable $e) {
        error_log('Consultants delete failed: ' . $e->getMessage());
        $errors[] = $e->getMessage();
    }
}

/*
 * Edit load
 */
if (isset($_GET['edit']) && (int)$_GET['edit'] > 0) {
    $editId = (int)$_GET['edit'];

    try {
        $stmt = $pdo->prepare("SELECT * FROM consultants WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $editId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row) {
            $editing = true;
            $form = [
                'id'         => (int)$row['id'],
                'firstname'  => (string)($row['firstname'] ?? ''),
                'lastname'   => (string)($row['lastname'] ?? ''),
                'email'      => (string)($row['email'] ?? ''),
                'agent_id'   => (string)($row['agent_id'] ?? ''),
                'mobile'     => (string)($row['mobile'] ?? ''),
                'remarks'    => (string)($row['remarks'] ?? ''),
                'attachment' => (string)($row['attachment'] ?? ''),
                'phone'      => (string)($row['phone'] ?? ''),
                'skype'      => (string)($row['skype'] ?? ''),
            ];
        } else {
            $errors[] = 'Consultant not found.';
        }
    } catch (Throwable $e) {
        error_log('Consultants edit load failed: ' . $e->getMessage());
        $errors[] = $e->getMessage();
    }
}

/*
 * Consultants list
 */
$search = trim((string)($_GET['q'] ?? ''));
$filterAgentId = (int)($_GET['agent_id'] ?? 0);
$consultants = [];

if (!$errors || $consultantCols) {
    try {
        $agentSelect = [];
        if ($agentNameCol) {
            $agentSelect[] = sql_col('a', $agentNameCol) . " AS agent_name";
        } elseif ($agentFirstCol && $agentLastCol) {
            $agentSelect[] = "TRIM(CONCAT(COALESCE(" . sql_col('a', $agentFirstCol) . ", ''), ' ', COALESCE(" . sql_col('a', $agentLastCol) . ", ''))) AS agent_name";
        } else {
            $agentSelect[] = "'' AS agent_name";
        }

        if ($agentOfficeCol) {
            $agentSelect[] = sql_col('a', $agentOfficeCol) . " AS agent_office";
        } else {
            $agentSelect[] = "'' AS agent_office";
        }

        $sql = "
            SELECT
                c.*,
                " . implode(",\n                ", $agentSelect) . "
            FROM consultants c
        ";

        if ($agentIdCol) {
            $sql .= "
            LEFT JOIN `" . str_replace('`', '``', $agentTable) . "` a
                   ON " . sql_col('a', $agentIdCol) . " = c.agent_id
            ";
        } else {
            $sql .= " ";
        }

        $sql .= " WHERE 1=1 ";
        $params = [];

        if ($search !== '') {
            /*
             * Use a different placeholder for every LIKE.
             * Some PDO/MySQL drivers fail with HY093 / invalid parameter number
             * when the same named placeholder is reused many times in one query.
             */
            $likeValue = '%' . $search . '%';
            $likeParts = [];
            $searchIndex = 0;

            $addLike = static function (string $expression) use (&$likeParts, &$params, &$searchIndex, $likeValue): void {
                $placeholder = ':search_' . $searchIndex;
                $likeParts[] = $expression . ' LIKE ' . $placeholder;
                $params[$placeholder] = $likeValue;
                $searchIndex++;
            };

            $addLike('c.firstname');
            $addLike('c.lastname');
            $addLike('c.email');
            $addLike('c.mobile');

            foreach (['phone', 'skype'] as $col) {
                if (has_col($consultantCols, $col)) {
                    $addLike('c.`' . str_replace('`', '``', $col) . '`');
                }
            }

            if ($agentIdCol) {
                if ($agentNameCol) {
                    $addLike(sql_col('a', $agentNameCol));
                }
                if ($agentFirstCol) {
                    $addLike(sql_col('a', $agentFirstCol));
                }
                if ($agentLastCol) {
                    $addLike(sql_col('a', $agentLastCol));
                }
                if ($agentOfficeCol) {
                    $addLike(sql_col('a', $agentOfficeCol));
                }
            }

            if ($likeParts) {
                $sql .= " AND (" . implode(" OR ", $likeParts) . ") ";
            }
        }

        if ($filterAgentId > 0) {
            $sql .= " AND c.agent_id = :agent_id ";
            $params[':agent_id'] = $filterAgentId;
        }

        $sql .= " ORDER BY c.id DESC LIMIT 500";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $consultants = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        error_log('Consultants list query failed: ' . $e->getMessage());
        $errors[] = 'Could not load consultants: ' . $e->getMessage();
    }
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-person-lines-fill"></i> Masters</div>
            <div class="hero-title mt-2">Consultants</div>
            <div class="hero-subtitle mt-2">
                Manage agent-linked contacts used on reservations as booking contacts.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="consultants.php" class="btn btn-outline-light">
                <i class="bi bi-plus-circle me-2"></i>New Consultant
            </a>
        </div>
    </div>
</div>

<?php if ($success): ?>
    <div class="alert alert-success"><?php echo h($success); ?></div>
<?php endif; ?>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0">
            <?php foreach (array_unique($errors) as $err): ?>
                <li><?php echo h($err); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<div class="row g-4">
    <div class="col-xl-5">
        <div class="form-section">
            <div class="form-section-title">
                <i class="bi bi-person-vcard"></i>
                <?php echo $editing ? 'Edit Consultant' : 'New Consultant'; ?>
            </div>

            <form method="post" class="row g-3">
                <input type="hidden" name="id" value="<?php echo (int)$form['id']; ?>">

                <div class="col-md-6">
                    <label class="form-label">First Name</label>
                    <input type="text" name="firstname" class="form-control" value="<?php echo h($form['firstname']); ?>" required>
                </div>

                <div class="col-md-6">
                    <label class="form-label">Last Name</label>
                    <input type="text" name="lastname" class="form-control" value="<?php echo h($form['lastname']); ?>" required>
                </div>

                <div class="col-12">
                    <label class="form-label">Linked Agent</label>
                    <select name="agent_id" class="form-select" required>
                        <option value="">Select agent</option>
                        <?php foreach ($agents as $agent): ?>
                            <?php
                            $agentName = trim((string)($agent['agent_name'] ?? ''));
                            $office = trim((string)($agent['office'] ?? ''));
                            ?>
                            <option value="<?php echo (int)$agent['id']; ?>" <?php echo (int)$form['agent_id'] === (int)$agent['id'] ? 'selected' : ''; ?>>
                                <?php echo h($agentName); ?><?php echo $office !== '' ? ' - ' . h($office) : ''; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="col-md-6">
                    <label class="form-label">Email</label>
                    <input type="email" name="email" class="form-control" value="<?php echo h($form['email']); ?>" required>
                </div>

                <div class="col-md-6">
                    <label class="form-label">Mobile</label>
                    <input type="text" name="mobile" class="form-control" value="<?php echo h($form['mobile']); ?>" required>
                </div>

                <?php if ($optionalConsultantCols['phone']): ?>
                    <div class="col-md-6">
                        <label class="form-label">Phone</label>
                        <input type="text" name="phone" class="form-control" value="<?php echo h($form['phone']); ?>">
                    </div>
                <?php endif; ?>

                <?php if ($optionalConsultantCols['skype']): ?>
                    <div class="col-md-6">
                        <label class="form-label">Teams ID</label>
                        <input type="text" name="skype" class="form-control" value="<?php echo h($form['skype']); ?>">
                    </div>
                <?php endif; ?>

                <?php if ($optionalConsultantCols['attachment']): ?>
                    <div class="col-12">
                        <label class="form-label">Attachment</label>
                        <input type="text" name="attachment" class="form-control" value="<?php echo h($form['attachment']); ?>" placeholder="File path or filename">
                    </div>
                <?php endif; ?>

                <?php if ($optionalConsultantCols['remarks']): ?>
                    <div class="col-12">
                        <label class="form-label">Remarks</label>
                        <textarea name="remarks" class="form-control" rows="4"><?php echo h($form['remarks']); ?></textarea>
                    </div>
                <?php endif; ?>

                <div class="col-12 d-flex gap-2">
                    <button type="submit" name="save_consultant" value="1" class="btn btn-primary">
                        <i class="bi bi-save me-2"></i><?php echo $editing ? 'Update Consultant' : 'Save Consultant'; ?>
                    </button>
                    <a href="consultants.php" class="btn btn-light">Reset</a>
                </div>
            </form>
        </div>
    </div>

    <div class="col-xl-7">
        <div class="card panel-card">
            <div class="card-header">
                <div class="d-flex flex-wrap justify-content-between align-items-center gap-2">
                    <h5 class="mb-0">Consultants</h5>
                    <form method="get" class="d-flex flex-wrap gap-2">
                        <select name="agent_id" class="form-select">
                            <option value="">All agents</option>
                            <?php foreach ($agents as $agent): ?>
                                <?php $agentName = trim((string)($agent['agent_name'] ?? '')); ?>
                                <option value="<?php echo (int)$agent['id']; ?>" <?php echo $filterAgentId === (int)$agent['id'] ? 'selected' : ''; ?>>
                                    <?php echo h($agentName); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                        <input type="text" name="q" class="form-control" placeholder="Search consultants..." value="<?php echo h($search); ?>">
                        <button class="btn btn-outline-secondary">Search</button>
                        <?php if ($search !== '' || $filterAgentId > 0): ?>
                            <a href="consultants.php" class="btn btn-light">Reset</a>
                        <?php endif; ?>
                    </form>
                </div>
            </div>

            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>Consultant</th>
                                <th>Agent</th>
                                <th>Contact</th>
                                <th>Channel</th>
                                <th class="text-end">Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$consultants): ?>
                                <tr>
                                    <td colspan="6" class="text-center text-muted py-4">No consultants found.</td>
                                </tr>
                            <?php else: ?>
                                <?php foreach ($consultants as $row): ?>
                                    <tr>
                                        <td><?php echo (int)$row['id']; ?></td>
                                        <td>
                                            <div class="fw-semibold"><?php echo h(consultant_full_name($row)); ?></div>
                                            <div class="small text-muted"><?php echo h($row['email'] ?? ''); ?></div>
                                        </td>
                                        <td>
                                            <div><?php echo h($row['agent_name'] ?? ''); ?></div>
                                            <div class="small text-muted"><?php echo h($row['agent_office'] ?? ''); ?></div>
                                        </td>
                                        <td>
                                            <div><?php echo h($row['mobile'] ?? ''); ?></div>
                                            <div class="small text-muted"><?php echo h($row['phone'] ?? ''); ?></div>
                                        </td>
                                        <td>
                                            <div><?php echo h($row['skype'] ?? ''); ?></div>
                                            <div class="small text-muted"><?php echo h($row['attachment'] ?? ''); ?></div>
                                        </td>
                                        <td class="text-end">
                                            <div class="d-inline-flex gap-2">
                                                <a href="consultants.php?edit=<?php echo (int)$row['id']; ?>" class="btn btn-sm btn-primary">Edit</a>
                                                <a href="consultants.php?delete=<?php echo (int)$row['id']; ?>"
                                                   class="btn btn-sm btn-outline-danger"
                                                   onclick="return confirm('Delete this consultant?');">
                                                    Delete
                                                </a>
                                            </div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
                <div class="p-3 small text-muted">
                    Showing maximum 500 records. Use search to find older records.
                </div>
            </div>
        </div>
    </div>
</div>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>
