<?php
declare(strict_types=1);

require_once dirname(__DIR__) . '/includes/layout.php';
require_once dirname(__DIR__) . '/includes/report-data.php';
require_once __DIR__ . '/_region_config.php';

if (!isset($crmRegionKey) || !is_string($crmRegionKey)) {
    throw new RuntimeException('CRM region configuration is missing.');
}

$definition = crm_region_definition($crmRegionKey);
$db = crm_report_db();
if (!$db) {
    throw new RuntimeException('ABOX database connection is unavailable.');
}
$table = $definition['table'];
if (!crm_report_table($db, $table)) {
    throw new RuntimeException("Required CRM table {$table} is unavailable.");
}

function crm_region_redirect(string $message, string $type = 'success'): void
{
    $_SESSION['crm_region_flash'] = ['message' => $message, 'type' => $type];
    $path = (string)(parse_url($_SERVER['REQUEST_URI'] ?? '', PHP_URL_PATH) ?: '');
    header('Location: ' . $path);
    exit;
}

function crm_region_options(mysqli $db, string $table, array $columns): array
{
    if (!crm_report_table($db, $table)) {
        return [];
    }
    $select = implode(',', array_map(static fn(string $column): string => "`{$column}`", $columns));
    return crm_report_query($db, "SELECT {$select} FROM `{$table}` ORDER BY `name`,`id`");
}

function crm_region_exists(mysqli $db, string $table, int $id, array $parents = []): bool
{
    $where = ['`id` = ?'];
    $types = 'i';
    $values = [$id];
    foreach ($parents as $column => $value) {
        $where[] = "`{$column}` = ?";
        $types .= 'i';
        $values[] = $value;
    }
    $statement = $db->prepare("SELECT `id` FROM `{$table}` WHERE " . implode(' AND ', $where) . ' LIMIT 1');
    $statement->bind_param($types, ...$values);
    $statement->execute();
    return (bool)$statement->get_result()->fetch_assoc();
}

function crm_region_usage_map(mysqli $db, array $definition): array
{
    $map = [];
    foreach ($definition['references'] as $reference) {
        if (!crm_report_table($db, $reference['table'])) {
            continue;
        }
        $rows = crm_report_query(
            $db,
            "SELECT CAST(`{$reference['column']}` AS UNSIGNED) ref_id,COUNT(*) total
             FROM `{$reference['table']}` WHERE CAST(`{$reference['column']}` AS UNSIGNED) > 0
             GROUP BY CAST(`{$reference['column']}` AS UNSIGNED)"
        );
        foreach ($rows as $row) {
            $id = (int)$row['ref_id'];
            $map[$id][] = (int)$row['total'] . ' ' . $reference['label'];
        }
    }
    return $map;
}

function crm_region_usage(mysqli $db, array $definition, int $id): array
{
    $usage = [];
    foreach ($definition['references'] as $reference) {
        if (!crm_report_table($db, $reference['table'])) {
            continue;
        }
        $statement = $db->prepare(
            "SELECT COUNT(*) total FROM `{$reference['table']}` WHERE CAST(`{$reference['column']}` AS UNSIGNED) = ?"
        );
        $statement->bind_param('i', $id);
        $statement->execute();
        $count = (int)($statement->get_result()->fetch_assoc()['total'] ?? 0);
        if ($count > 0) {
            $usage[] = $count . ' ' . $reference['label'];
        }
    }
    return $usage;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $token = (string)($_POST['csrf_token'] ?? '');
    if ($token === '' || !hash_equals(crm_csrf_token(), $token)) {
        crm_region_redirect('Your session token expired. Please try again.', 'error');
    }
    $action = (string)($_POST['action'] ?? 'save');
    $id = filter_var($_POST['id'] ?? 0, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) ?: 0;
    try {
        if ($action === 'delete') {
            if ($id < 1) {
                throw new RuntimeException('Invalid region record.');
            }
            $usage = crm_region_usage($db, $definition, $id);
            if ($usage !== []) {
                throw new RuntimeException('Delete blocked: this record is used by ' . implode(' and ', $usage) . '.');
            }
            $statement = $db->prepare("DELETE FROM `{$table}` WHERE `id` = ?");
            $statement->bind_param('i', $id);
            $statement->execute();
            crm_region_redirect($definition['singular'] . ' deleted safely.');
        }

        $name = trim((string)($_POST['name'] ?? ''));
        if ($name === '' || mb_strlen($name) > 200) {
            throw new RuntimeException($definition['singular'] . ' name is required and must be 200 characters or fewer.');
        }
        $stateId = filter_var($_POST['stateid'] ?? 0, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) ?: 0;
        $districtId = filter_var($_POST['districtid'] ?? 0, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) ?: 0;
        $talukaId = filter_var($_POST['talukaid'] ?? 0, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) ?: 0;
        $code = filter_var($_POST['code'] ?? 0, FILTER_VALIDATE_INT);
        if ($code === false || $code < 0 || $code > 999999999) {
            throw new RuntimeException('Code must be a whole number between 0 and 999999999.');
        }

        if ($crmRegionKey !== 'state' && !crm_region_exists($db, 'jos_crm_mstates', $stateId)) {
            throw new RuntimeException('Please select a valid state.');
        }
        if (in_array($crmRegionKey, ['taluka', 'locality'], true)
            && !crm_region_exists($db, 'jos_crm_district', $districtId, ['stateid' => $stateId])) {
            throw new RuntimeException('The selected district does not belong to the selected state.');
        }
        if ($crmRegionKey === 'locality'
            && !crm_region_exists($db, 'jos_crm_mcitys', $talukaId, ['stateid' => $stateId, 'districtid' => $districtId])) {
            throw new RuntimeException('The selected taluka does not belong to the selected district.');
        }

        $scopeColumn = ['state' => null, 'district' => 'stateid', 'taluka' => 'districtid', 'locality' => 'talukaid'][$crmRegionKey];
        $scopeValue = ['state' => 0, 'district' => $stateId, 'taluka' => $districtId, 'locality' => $talukaId][$crmRegionKey];
        $duplicateSql = "SELECT `id` FROM `{$table}` WHERE LOWER(TRIM(`name`))=LOWER(TRIM(?)) AND `id`<>?";
        $duplicateTypes = 'si';
        $duplicateValues = [$name, $id];
        if ($scopeColumn !== null) {
            $duplicateSql .= " AND `{$scopeColumn}`=?";
            $duplicateTypes .= 'i';
            $duplicateValues[] = $scopeValue;
        }
        $duplicate = $db->prepare($duplicateSql . ' LIMIT 1');
        $duplicate->bind_param($duplicateTypes, ...$duplicateValues);
        $duplicate->execute();
        if ($duplicate->get_result()->fetch_assoc()) {
            throw new RuntimeException($definition['singular'] . ' already exists in the selected region.');
        }

        if ($crmRegionKey === 'state') {
            if ($id > 0) {
                $statement = $db->prepare('UPDATE `jos_crm_mstates` SET `name`=?,`code`=? WHERE `id`=?');
                $statement->bind_param('sii', $name, $code, $id);
            } else {
                $statement = $db->prepare('INSERT INTO `jos_crm_mstates` (`name`,`code`) VALUES (?,?)');
                $statement->bind_param('si', $name, $code);
            }
        } elseif ($crmRegionKey === 'district') {
            if ($id > 0) {
                $statement = $db->prepare('UPDATE `jos_crm_district` SET `stateid`=?,`name`=? WHERE `id`=?');
                $statement->bind_param('isi', $stateId, $name, $id);
            } else {
                $statement = $db->prepare('INSERT INTO `jos_crm_district` (`stateid`,`name`) VALUES (?,?)');
                $statement->bind_param('is', $stateId, $name);
            }
        } elseif ($crmRegionKey === 'taluka') {
            if ($id > 0) {
                $statement = $db->prepare('UPDATE `jos_crm_mcitys` SET `stateid`=?,`districtid`=?,`city_code`=?,`name`=? WHERE `id`=?');
                $statement->bind_param('iiisi', $stateId, $districtId, $code, $name, $id);
            } else {
                $statement = $db->prepare('INSERT INTO `jos_crm_mcitys` (`stateid`,`districtid`,`city_code`,`name`) VALUES (?,?,?,?)');
                $statement->bind_param('iiis', $stateId, $districtId, $code, $name);
            }
        } else {
            if ($id > 0) {
                $statement = $db->prepare('UPDATE `jos_crm_locality` SET `stateid`=?,`districtid`=?,`talukaid`=?,`name`=? WHERE `id`=?');
                $statement->bind_param('iiisi', $stateId, $districtId, $talukaId, $name, $id);
            } else {
                $statement = $db->prepare('INSERT INTO `jos_crm_locality` (`stateid`,`districtid`,`talukaid`,`name`) VALUES (?,?,?,?)');
                $statement->bind_param('iiis', $stateId, $districtId, $talukaId, $name);
            }
        }
        $statement->execute();
        crm_region_redirect($definition['singular'] . ($id > 0 ? ' updated successfully.' : ' added successfully.'));
    } catch (Throwable $exception) {
        $_SESSION['crm_region_old'] = $_POST;
        crm_region_redirect($exception->getMessage(), 'error');
    }
}

$flash = $_SESSION['crm_region_flash'] ?? null;
unset($_SESSION['crm_region_flash']);
$old = $_SESSION['crm_region_old'] ?? [];
unset($_SESSION['crm_region_old']);
$editId = filter_var($_GET['edit'] ?? 0, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) ?: 0;
$editing = null;
if ($editId > 0) {
    $statement = $db->prepare("SELECT * FROM `{$table}` WHERE `id`=? LIMIT 1");
    $statement->bind_param('i', $editId);
    $statement->execute();
    $editing = $statement->get_result()->fetch_assoc() ?: null;
}

$states = crm_region_options($db, 'jos_crm_mstates', ['id', 'name']);
$districts = crm_region_options($db, 'jos_crm_district', ['id', 'stateid', 'name']);
$talukas = crm_region_options($db, 'jos_crm_mcitys', ['id', 'stateid', 'districtid', 'name']);
$stateMap = array_column($states, 'name', 'id');
$districtMap = array_column($districts, 'name', 'id');
$talukaMap = array_column($talukas, 'name', 'id');

$search = trim((string)($_GET['q'] ?? ''));
$sql = "SELECT * FROM `{$table}`";
if ($search !== '') {
    $sql .= ' WHERE `name` LIKE ?';
}
$sql .= ' ORDER BY `name`,`id` LIMIT 1000';
$statement = $db->prepare($sql);
if ($search !== '') {
    $like = '%' . $search . '%';
    $statement->bind_param('s', $like);
}
$statement->execute();
$records = $statement->get_result()->fetch_all(MYSQLI_ASSOC);
$usageMap = crm_region_usage_map($db, $definition);
$selectedState = (int)($old['stateid'] ?? $editing['stateid'] ?? 0);
$selectedDistrict = (int)($old['districtid'] ?? $editing['districtid'] ?? 0);
$selectedTaluka = (int)($old['talukaid'] ?? $editing['talukaid'] ?? 0);
$selectedCode = (int)($old['code'] ?? ($crmRegionKey === 'state' ? ($editing['code'] ?? 0) : ($editing['city_code'] ?? 0)));

crm_page_start($definition['title'], 'masters', $definition['description']);
?>
<?php if (is_array($flash)): ?>
    <div class="master-alert <?= $flash['type'] === 'error' ? 'error' : 'success' ?>" role="alert">
        <i class="fa-solid <?= $flash['type'] === 'error' ? 'fa-circle-exclamation' : 'fa-circle-check' ?>"></i>
        <?= crm_e((string)$flash['message']) ?>
    </div>
<?php endif; ?>
<div class="region-path" aria-label="Region hierarchy">
    <?php foreach (['State', 'District', 'Taluka', 'Locality'] as $level): ?>
        <span class="<?= strtolower($level) === $crmRegionKey ? 'active' : '' ?>"><?= crm_e($level) ?></span>
        <?php if ($level !== 'Locality'): ?><i class="fa-solid fa-chevron-right"></i><?php endif; ?>
    <?php endforeach; ?>
</div>
<div class="master-workspace">
    <section class="card master-form-card">
        <div class="card-head"><div class="card-title">
            <i class="fa-solid <?= crm_e($definition['icon']) ?>"></i>
            <div><h2><?= $editing ? 'Edit ' : 'New ' ?><?= crm_e($definition['singular']) ?></h2><p>Region Config</p></div>
        </div></div>
        <form method="post" class="card-body" autocomplete="off">
            <input type="hidden" name="csrf_token" value="<?= crm_e(crm_csrf_token()) ?>">
            <input type="hidden" name="action" value="save">
            <input type="hidden" name="id" value="<?= (int)($editing['id'] ?? 0) ?>">
            <div class="master-form-fields">
                <?php if ($crmRegionKey !== 'state'): ?>
                    <div class="field full"><label for="region-state">State <span class="required">*</span></label>
                        <select class="control" id="region-state" name="stateid" required>
                            <option value="">Select State</option>
                            <?php foreach ($states as $state): ?><option value="<?= (int)$state['id'] ?>" <?= $selectedState === (int)$state['id'] ? 'selected' : '' ?>><?= crm_e($state['name']) ?></option><?php endforeach; ?>
                        </select>
                    </div>
                <?php endif; ?>
                <?php if (in_array($crmRegionKey, ['taluka', 'locality'], true)): ?>
                    <div class="field full"><label for="region-district">District <span class="required">*</span></label>
                        <select class="control" id="region-district" name="districtid" required>
                            <option value="">Select District</option>
                            <?php foreach ($districts as $district): ?><option value="<?= (int)$district['id'] ?>" data-state="<?= (int)$district['stateid'] ?>" <?= $selectedDistrict === (int)$district['id'] ? 'selected' : '' ?>><?= crm_e($district['name']) ?></option><?php endforeach; ?>
                        </select>
                    </div>
                <?php endif; ?>
                <?php if ($crmRegionKey === 'locality'): ?>
                    <div class="field full"><label for="region-taluka">Taluka <span class="required">*</span></label>
                        <select class="control" id="region-taluka" name="talukaid" required>
                            <option value="">Select Taluka</option>
                            <?php foreach ($talukas as $taluka): ?><option value="<?= (int)$taluka['id'] ?>" data-state="<?= (int)$taluka['stateid'] ?>" data-district="<?= (int)$taluka['districtid'] ?>" <?= $selectedTaluka === (int)$taluka['id'] ? 'selected' : '' ?>><?= crm_e($taluka['name']) ?></option><?php endforeach; ?>
                        </select>
                    </div>
                <?php endif; ?>
                <?php if (in_array($crmRegionKey, ['state', 'taluka'], true)): ?>
                    <div class="field full"><label for="region-code"><?= $crmRegionKey === 'state' ? 'State Code' : 'City Code' ?></label>
                        <input class="control" id="region-code" name="code" type="number" min="0" max="999999999" value="<?= $selectedCode ?>">
                    </div>
                <?php endif; ?>
                <div class="field full"><label for="region-name"><?= crm_e($definition['singular']) ?> Name <span class="required">*</span></label>
                    <input class="control" id="region-name" name="name" maxlength="200" required value="<?= crm_e((string)($old['name'] ?? $editing['name'] ?? '')) ?>" placeholder="Enter <?= crm_e(strtolower($definition['singular'])) ?> name">
                </div>
            </div>
            <div class="master-form-actions">
                <button class="button primary" type="submit"><i class="fa-solid fa-floppy-disk"></i> <?= $editing ? 'Update' : 'Save' ?></button>
                <?php if ($editing): ?><a class="button secondary" href="<?= crm_e((string)(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH) ?: '')) ?>">Cancel</a><?php endif; ?>
            </div>
        </form>
    </section>
    <section class="card master-list-card">
        <div class="card-head">
            <div class="card-title"><i class="fa-solid fa-list"></i><div><h2><?= crm_e($definition['singular']) ?> List</h2><p><?= count($records) ?> records shown</p></div></div>
            <form method="get" class="master-search"><i class="fa-solid fa-magnifying-glass"></i><input name="q" value="<?= crm_e($search) ?>" placeholder="Search records" aria-label="Search records"><?php if ($search !== ''): ?><a href="<?= crm_e((string)(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH) ?: '')) ?>"><i class="fa-solid fa-xmark"></i></a><?php endif; ?></form>
        </div>
        <div class="table-wrap"><table class="data-table master-table region-table">
            <thead><tr><th>ID</th><th><?= crm_e($definition['singular']) ?></th><th>Region</th><?php if (in_array($crmRegionKey, ['state', 'taluka'], true)): ?><th>Code</th><?php endif; ?><th>In Use</th><th>Action</th></tr></thead>
            <tbody>
            <?php if ($records === []): ?><tr class="empty-row"><td colspan="6">No matching records found.</td></tr><?php endif; ?>
            <?php foreach ($records as $record): ?>
                <?php
                $recordId = (int)$record['id'];
                $regionParts = [];
                if (isset($record['stateid'])) $regionParts[] = $stateMap[(int)$record['stateid']] ?? 'Unknown state';
                if (isset($record['districtid'])) $regionParts[] = $districtMap[(int)$record['districtid']] ?? 'Unknown district';
                if (isset($record['talukaid'])) $regionParts[] = $talukaMap[(int)$record['talukaid']] ?? 'Unknown taluka';
                $usage = $usageMap[$recordId] ?? [];
                ?>
                <tr>
                    <td>#<?= $recordId ?></td>
                    <td><strong><?= crm_e($record['name']) ?></strong></td>
                    <td><span class="region-context"><?= $regionParts === [] ? '—' : crm_e(implode(' › ', $regionParts)) ?></span></td>
                    <?php if (in_array($crmRegionKey, ['state', 'taluka'], true)): ?><td><?= (int)($crmRegionKey === 'state' ? $record['code'] : $record['city_code']) ?></td><?php endif; ?>
                    <td><?= $usage === [] ? '<span class="master-unused">Unused</span>' : crm_e(implode(', ', $usage)) ?></td>
                    <td><div class="inline-actions">
                        <a class="button secondary small" href="?edit=<?= $recordId ?>"><i class="fa-solid fa-pen"></i> Edit</a>
                        <form method="post" onsubmit="return confirm('Delete this unused <?= crm_e(strtolower($definition['singular'])) ?>?');">
                            <input type="hidden" name="csrf_token" value="<?= crm_e(crm_csrf_token()) ?>"><input type="hidden" name="action" value="delete"><input type="hidden" name="id" value="<?= $recordId ?>">
                            <button class="button danger small" type="submit" <?= $usage !== [] ? 'disabled title="Cannot delete a record that is in use"' : '' ?>><i class="fa-solid fa-trash"></i></button>
                        </form>
                    </div></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table></div>
    </section>
</div>
<script>
(() => {
    const state = document.getElementById('region-state');
    const district = document.getElementById('region-district');
    const taluka = document.getElementById('region-taluka');
    const filter = (select, attribute, value) => {
        if (!select) return;
        [...select.options].forEach((option, index) => {
            if (index === 0) return;
            const visible = value !== '' && option.dataset[attribute] === value;
            option.hidden = !visible;
            option.disabled = !visible;
        });
        if (select.selectedOptions[0]?.disabled) select.value = '';
    };
    const syncTaluka = () => filter(taluka, 'district', district?.value || '');
    const syncDistrict = () => { filter(district, 'state', state?.value || ''); syncTaluka(); };
    state?.addEventListener('change', syncDistrict);
    district?.addEventListener('change', syncTaluka);
    syncDistrict();
})();
</script>
<?php crm_page_end(); ?>
