<?php

require_once __DIR__ . '/store_settings.php';

function ensureQboNameMapTable(mysqli $conn): void
{
    $sql = "
        CREATE TABLE IF NOT EXISTS qbo_name_map (
            id INT(11) NOT NULL AUTO_INCREMENT,
            original_name VARCHAR(255) NOT NULL,
            csv_content LONGTEXT NOT NULL,
            uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
    ";

    if (!$conn->query($sql)) {
        throw new RuntimeException('Could not prepare the QBO name map table: ' . $conn->error);
    }
}

function qboNameMapStoreUploadedFile(mysqli $conn, array $file): void
{
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        throw new RuntimeException('Please upload your QBO name mapping CSV.');
    }

    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
        throw new RuntimeException('Could not read the uploaded QBO mapping file.');
    }

    $tmpName = (string) ($file['tmp_name'] ?? '');
    if ($tmpName === '' || !is_uploaded_file($tmpName)) {
        throw new RuntimeException('The uploaded QBO mapping file was not available to read.');
    }

    $csvContent = file_get_contents($tmpName);
    if ($csvContent === false || trim($csvContent) === '') {
        throw new RuntimeException('The uploaded QBO mapping file was empty or could not be read.');
    }

    ensureQboNameMapTable($conn);

    if (!$conn->query("TRUNCATE TABLE qbo_name_map")) {
        throw new RuntimeException('Could not replace the existing QBO name map.');
    }

    $originalName = trim((string) ($file['name'] ?? 'qbo_name_map.csv'));
    $stmt = $conn->prepare("
        INSERT INTO qbo_name_map (original_name, csv_content)
        VALUES (?, ?)
    ");
    $stmt->bind_param('ss', $originalName, $csvContent);
    $stmt->execute();
    $stmt->close();
}

function qboNameMapGetMeta(mysqli $conn): array
{
    ensureQboNameMapTable($conn);

    $meta = [
        'exists' => false,
        'original_name' => '',
        'saved_at' => '',
    ];

    $result = $conn->query("
        SELECT original_name, uploaded_at
        FROM qbo_name_map
        ORDER BY id DESC
        LIMIT 1
    ");

    if ($result && ($row = $result->fetch_assoc())) {
        $meta['exists'] = true;
        $meta['original_name'] = (string) ($row['original_name'] ?? '');
        $meta['saved_at'] = (string) ($row['uploaded_at'] ?? '');
    }

    return $meta;
}

function qboNameMapParseCsvContent(string $csvContent): array
{
    $handle = fopen('php://temp', 'r+');
    if ($handle === false) {
        throw new RuntimeException('Could not read the saved QBO mapping CSV.');
    }

    fwrite($handle, $csvContent);
    rewind($handle);

    $map = [];
    $firstRow = true;
    while (($data = fgetcsv($handle)) !== false) {
        if (count($data) < 2) {
            continue;
        }

        $fromName = trim((string) $data[0]);
        $toName = trim((string) $data[1]);
        if ($fromName === '' || $toName === '') {
            continue;
        }

        if ($firstRow) {
            $normalizedHeader = strtolower($fromName);
            $normalizedTarget = strtolower($toName);
            if (in_array($normalizedHeader, ['customer', 'housetab customer', 'house tab customer', 'source'], true)
                && in_array($normalizedTarget, ['qbo customer', 'quickbooks customer', 'target', 'mapped'], true)) {
                $firstRow = false;
                continue;
            }
        }

        $map[strtolower($fromName)] = $toName;
        $firstRow = false;
    }
    fclose($handle);

    if ($map === []) {
        throw new RuntimeException('The saved QBO mapping file did not contain any usable customer name rows.');
    }

    return $map;
}

function qboNameMapLoadSaved(mysqli $conn): array
{
    ensureQboNameMapTable($conn);

    $result = $conn->query("
        SELECT csv_content
        FROM qbo_name_map
        ORDER BY id DESC
        LIMIT 1
    ");

    $row = $result ? $result->fetch_assoc() : null;
    $csvContent = (string) ($row['csv_content'] ?? '');
    if ($csvContent === '') {
        throw new RuntimeException('No saved QBO mapping file was found for this store.');
    }

    return qboNameMapParseCsvContent($csvContent);
}