<?php
declare(strict_types=1);

function sync_table_registry(): array
{
    static $tables = null;
    if ($tables === null) {
        $tables = require __DIR__ . '/table_map.php';
    }

    return $tables;
}

function sync_allowed_table(string $table): array
{
    $registry = sync_table_registry();
    if (!isset($registry[$table])) {
        sync_fail("Table `$table` tidak diizinkan untuk sync.", 400);
    }

    $config = $registry[$table];

    return [
        'company_column' => 'company_id',
        'uuid_column' => 'uuid',
        'updated_at_column' => 'updated_at',
        'deleted_column' => 'is_deleted',
        'ignore_columns' => [],
        'parent_rules' => [],
    ] + $config;
}

function sync_table_columns(mysqli $conn, string $table): array
{
    static $cache = [];
    if (isset($cache[$table])) {
        return $cache[$table];
    }

    $result = $conn->query("SHOW COLUMNS FROM `{$table}`");
    if (!$result) {
        sync_fail("Gagal membaca kolom table `$table`: " . $conn->error, 500);
    }

    $columns = [];
    while ($row = $result->fetch_assoc()) {
        $columns[] = $row['Field'];
    }

    $cache[$table] = $columns;
    return $columns;
}

function sync_table_has_column(mysqli $conn, string $table, string $column): bool
{
    return in_array($column, sync_table_columns($conn, $table), true);
}

function sync_clean_text($value): string
{
    return trim((string)$value);
}

function sync_now_sql(): string
{
    return gmdate('Y-m-d H:i:s');
}

function sync_normalize_timestamp(?string $value): ?string
{
    $raw = trim((string)$value);
    if ($raw === '') {
        return null;
    }

    $timestamp = strtotime($raw);
    if ($timestamp === false) {
        return null;
    }

    return gmdate('Y-m-d H:i:s', $timestamp);
}

function sync_compare_timestamps(?string $left, ?string $right): int
{
    $leftTs = $left ? strtotime($left) : false;
    $rightTs = $right ? strtotime($right) : false;

    $leftValue = $leftTs === false ? 0 : $leftTs;
    $rightValue = $rightTs === false ? 0 : $rightTs;

    return $leftValue <=> $rightValue;
}

function sync_request_company_id(array $input, ?array $row = null): string
{
    $rootCompanyId = sync_clean_text($input['company_id'] ?? $input['store_id'] ?? '');
    $rowCompanyId = sync_clean_text(($row ?? [])['company_id'] ?? ($row ?? [])['store_id'] ?? '');

    if ($rootCompanyId !== '' && $rowCompanyId !== '' && $rootCompanyId !== $rowCompanyId) {
        sync_fail('company_id root dan row tidak cocok.', 400);
    }

    $companyId = $rowCompanyId !== '' ? $rowCompanyId : $rootCompanyId;
    if ($companyId === '') {
        sync_fail('company_id wajib diisi.', 400);
    }

    return $companyId;
}

function sync_stmt_bind_params(mysqli_stmt $stmt, array $params): void
{
    if ($params === []) {
        return;
    }

    $types = '';
    $values = [];

    foreach ($params as $value) {
        if (is_int($value)) {
            $types .= 'i';
        } elseif (is_float($value)) {
            $types .= 'd';
        } else {
            $types .= 's';
        }
        $values[] = $value;
    }

    $refs = [];
    $refs[] = $types;

    foreach ($values as $index => $value) {
        $refs[] = &$values[$index];
    }

    call_user_func_array([$stmt, 'bind_param'], $refs);
}

function sync_stmt_fetch_one_assoc(mysqli_stmt $stmt): ?array
{
    if (!$stmt->execute()) {
        sync_fail('Gagal execute statement: ' . $stmt->error, 500);
    }

    $meta = $stmt->result_metadata();
    if (!$meta) {
        return null;
    }

    $row = [];
    $bind = [];
    $fields = $meta->fetch_fields();

    foreach ($fields as $field) {
        $row[$field->name] = null;
        $bind[] = &$row[$field->name];
    }

    call_user_func_array([$stmt, 'bind_result'], $bind);

    if (!$stmt->fetch()) {
        return null;
    }

    $result = [];
    foreach ($row as $key => $value) {
        $result[$key] = $value;
    }

    return $result;
}

function sync_stmt_fetch_all_assoc(mysqli_stmt $stmt): array
{
    if (!$stmt->execute()) {
        sync_fail('Gagal execute statement: ' . $stmt->error, 500);
    }

    $meta = $stmt->result_metadata();
    if (!$meta) {
        return [];
    }

    $row = [];
    $bind = [];
    $fields = $meta->fetch_fields();

    foreach ($fields as $field) {
        $row[$field->name] = null;
        $bind[] = &$row[$field->name];
    }

    call_user_func_array([$stmt, 'bind_result'], $bind);

    $rows = [];
    while ($stmt->fetch()) {
        $item = [];
        foreach ($row as $key => $value) {
            $item[$key] = $value;
        }
        $rows[] = $item;
    }

    return $rows;
}

function sync_bind_and_execute(mysqli_stmt $stmt, array $params, bool $execute = true): void
{
    sync_stmt_bind_params($stmt, $params);

    if ($execute && !$stmt->execute()) {
        sync_fail('Gagal execute statement: ' . $stmt->error, 500);
    }
}

function sync_prepare_row(
    mysqli $conn,
    string $table,
    array $row,
    array $tableConfig,
    string $companyId
): array {
    $columns = sync_table_columns($conn, $table);
    $ignoreColumns = array_merge(['id'], $tableConfig['ignore_columns']);

    $uuidColumn = $tableConfig['uuid_column'];
    $updatedAtColumn = $tableConfig['updated_at_column'];
    $companyColumn = $tableConfig['company_column'];
    $deletedColumn = $tableConfig['deleted_column'];

    $uuid = sync_clean_text($row[$uuidColumn] ?? '');
    if ($uuid === '') {
        sync_fail("uuid wajib diisi untuk table `$table`.", 400);
    }

    $updatedAt = sync_normalize_timestamp((string)($row[$updatedAtColumn] ?? ''));
    if ($updatedAt === null) {
        sync_fail("updated_at wajib valid untuk table `$table`.", 400);
    }

    $payload = [];
    foreach ($row as $key => $value) {
        if (!in_array($key, $columns, true)) {
            continue;
        }

        if (in_array($key, $ignoreColumns, true)) {
            continue;
        }

        $payload[$key] = $value;
    }

    $payload[$uuidColumn] = $uuid;
    $payload[$companyColumn] = $companyId;
    $payload[$updatedAtColumn] = $updatedAt;

    if (in_array('created_at', $columns, true)) {
        $createdAt = sync_normalize_timestamp((string)($row['created_at'] ?? ''));
        $payload['created_at'] = $createdAt ?? $updatedAt;
    }

    if (in_array($deletedColumn, $columns, true)) {
        $payload[$deletedColumn] = (int)($row[$deletedColumn] ?? 0) === 1 ? 1 : 0;
    }

    if (in_array('is_synced', $columns, true)) {
        $payload['is_synced'] = 1;
    }

    return $payload;
}

function sync_assert_parent_rows(
    mysqli $conn,
    string $table,
    array $row,
    array $tableConfig,
    string $companyId
): void {
    foreach ($tableConfig['parent_rules'] as $rule) {
        $childColumn = $rule['column'];
        $parentTable = $rule['parent_table'];
        $parentUuidColumn = $rule['parent_uuid_column'] ?? 'uuid';

        if (!array_key_exists($childColumn, $row)) {
            continue;
        }

        $parentUuid = sync_clean_text($row[$childColumn]);
        if ($parentUuid === '') {
            continue;
        }

        $stmt = $conn->prepare(
            "SELECT 1 AS found
             FROM `{$parentTable}`
             WHERE `company_id` = ?
               AND `{$parentUuidColumn}` = ?
             LIMIT 1"
        );

        if (!$stmt) {
            sync_fail('Gagal prepare parent check: ' . $conn->error, 500);
        }

        $stmt->bind_param('ss', $companyId, $parentUuid);
        $found = sync_stmt_fetch_one_assoc($stmt);
        $stmt->close();

        if ($found === null) {
            sync_fail(
                "Row parent belum ada untuk `$table`.`{$childColumn}` = `$parentUuid`.",
                409,
                [
                    'table' => $table,
                    'missing_parent' => $parentTable,
                    'missing_uuid' => $parentUuid,
                ]
            );
        }
    }
}

function sync_fetch_existing_row(
    mysqli $conn,
    string $table,
    array $tableConfig,
    string $companyId,
    string $uuid
): ?array {
    $stmt = $conn->prepare(
        "SELECT *
         FROM `{$table}`
         WHERE `{$tableConfig['company_column']}` = ?
           AND `{$tableConfig['uuid_column']}` = ?
         LIMIT 1"
    );

    if (!$stmt) {
        sync_fail('Gagal prepare fetch existing row: ' . $conn->error, 500);
    }

    $stmt->bind_param('ss', $companyId, $uuid);
    $row = sync_stmt_fetch_one_assoc($stmt);
    $stmt->close();

    return $row;
}

function sync_insert_row(mysqli $conn, string $table, array $payload): void
{
    $columns = array_keys($payload);
    $placeholders = implode(', ', array_fill(0, count($columns), '?'));
    $columnSql = '`' . implode('`, `', $columns) . '`';

    $sql = "INSERT INTO `{$table}` ({$columnSql}) VALUES ({$placeholders})";
    $stmt = $conn->prepare($sql);

    if (!$stmt) {
        sync_fail('Gagal prepare insert row: ' . $conn->error, 500);
    }

    sync_bind_and_execute($stmt, array_values($payload));
    $stmt->close();
}

function sync_update_row(
    mysqli $conn,
    string $table,
    array $payload,
    string $companyId,
    string $uuid,
    array $tableConfig
): void {
    $assignments = [];
    $values = [];

    foreach ($payload as $column => $value) {
        $assignments[] = "`{$column}` = ?";
        $values[] = $value;
    }

    $values[] = $companyId;
    $values[] = $uuid;

    $sql = "UPDATE `{$table}`
            SET " . implode(', ', $assignments) . "
            WHERE `{$tableConfig['company_column']}` = ?
              AND `{$tableConfig['uuid_column']}` = ?";

    $stmt = $conn->prepare($sql);

    if (!$stmt) {
        sync_fail('Gagal prepare update row: ' . $conn->error, 500);
    }

    sync_bind_and_execute($stmt, $values);
    $stmt->close();
}

function sync_upsert_row(mysqli $conn, string $table, array $input, array $row): array
{
    $tableConfig = sync_allowed_table($table);
    $companyId = sync_request_company_id($input, $row);
    sync_assert_company_access($companyId);

    $payload = sync_prepare_row($conn, $table, $row, $tableConfig, $companyId);
    sync_assert_parent_rows($conn, $table, $payload, $tableConfig, $companyId);

    $uuid = $payload[$tableConfig['uuid_column']];
    $incomingUpdatedAt = $payload[$tableConfig['updated_at_column']];

    $existingRow = sync_fetch_existing_row($conn, $table, $tableConfig, $companyId, $uuid);

    if ($existingRow !== null) {
        $existingUpdatedAt = sync_normalize_timestamp(
            (string)($existingRow[$tableConfig['updated_at_column']] ?? '')
        );

        if (sync_compare_timestamps($incomingUpdatedAt, $existingUpdatedAt) <= 0) {
            return [
                'success' => true,
                'status' => 'skipped',
                'reason' => 'incoming_older_or_same',
                'table' => $table,
                'company_id' => $companyId,
                'uuid' => $uuid,
            ];
        }

        sync_update_row($conn, $table, $payload, $companyId, $uuid, $tableConfig);

        return [
            'success' => true,
            'status' => 'updated',
            'table' => $table,
            'company_id' => $companyId,
            'uuid' => $uuid,
        ];
    }

    sync_insert_row($conn, $table, $payload);

    return [
        'success' => true,
        'status' => 'inserted',
        'table' => $table,
        'company_id' => $companyId,
        'uuid' => $uuid,
    ];
}

function sync_pull_rows(
    mysqli $conn,
    string $table,
    string $companyId,
    ?string $since,
    int $limit
): array {
    $tableConfig = sync_allowed_table($table);
    $columns = sync_table_columns($conn, $table);
    $companyColumn = $tableConfig['company_column'];
    $updatedAtColumn = $tableConfig['updated_at_column'];

    $where = [];
    $params = [];

    if (in_array($companyColumn, $columns, true)) {
        $where[] = "`{$companyColumn}` = ?";
        $params[] = $companyId;
    }

    if ($since !== null && in_array($updatedAtColumn, $columns, true)) {
        $where[] = "`{$updatedAtColumn}` > ?";
        $params[] = $since;
    }

    $sql = "SELECT * FROM `{$table}`";
    if ($where !== []) {
        $sql .= ' WHERE ' . implode(' AND ', $where);
    }

    if (in_array($updatedAtColumn, $columns, true)) {
        $sql .= " ORDER BY `{$updatedAtColumn}` ASC";
    }

    $sql .= " LIMIT " . max(1, $limit);

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        sync_fail('Gagal prepare pull rows: ' . $conn->error, 500);
    }

    sync_stmt_bind_params($stmt, $params);
    $rows = sync_stmt_fetch_all_assoc($stmt);
    $stmt->close();

    return $rows;
}

function sync_force_resend_rows(
    mysqli $conn,
    string $table,
    string $companyId,
    int $limit
): array {
    if (!sync_table_has_column($conn, $table, 'force_sync_at')) {
        return [];
    }

    $stmt = $conn->prepare(
        "SELECT *
         FROM `{$table}`
         WHERE `company_id` = ?
           AND `force_sync_at` IS NOT NULL
         ORDER BY `force_sync_at` ASC
         LIMIT ?"
    );

    if (!$stmt) {
        sync_fail('Gagal prepare force resend rows: ' . $conn->error, 500);
    }

    $stmt->bind_param('si', $companyId, $limit);
    $rows = sync_stmt_fetch_all_assoc($stmt);
    $stmt->close();

    return $rows;
}

function sync_mark_force_resend(
    mysqli $conn,
    string $table,
    string $companyId,
    array $uuids,
    bool $clear
): int {
    if (!sync_table_has_column($conn, $table, 'force_sync_at')) {
        return 0;
    }

    $affected = 0;

    foreach ($uuids as $uuidRaw) {
        $uuid = sync_clean_text($uuidRaw);
        if ($uuid === '') {
            continue;
        }

        if ($clear) {
            $sql = "UPDATE `{$table}`
                    SET `force_sync_at` = NULL
                    WHERE `company_id` = ?
                      AND `uuid` = ?";
            $stmt = $conn->prepare($sql);

            if (!$stmt) {
                sync_fail('Gagal prepare clear force sync: ' . $conn->error, 500);
            }

            $stmt->bind_param('ss', $companyId, $uuid);
        } else {
            $value = sync_now_sql();
            $sql = "UPDATE `{$table}`
                    SET `force_sync_at` = ?
                    WHERE `company_id` = ?
                      AND `uuid` = ?";
            $stmt = $conn->prepare($sql);

            if (!$stmt) {
                sync_fail('Gagal prepare mark force sync: ' . $conn->error, 500);
            }

            $stmt->bind_param('sss', $value, $companyId, $uuid);
        }

        if (!$stmt->execute()) {
            sync_fail('Gagal update force sync: ' . $stmt->error, 500);
        }

        $affected += $stmt->affected_rows;
        $stmt->close();
    }

    return $affected;
}