<?php
// CSV export/import and the email report. Depends only on db.php so cron.php
// can run it without starting a session.
require_once __DIR__ . '/db.php';

function setting_get(string $key, ?string $default = null): ?string
{
    $st = db()->prepare('SELECT value FROM settings WHERE key = ?');
    $st->execute([$key]);
    $v = $st->fetchColumn();
    return $v === false ? $default : $v;
}

function setting_set(string $key, string $value): void
{
    db()->prepare('INSERT INTO settings (key, value) VALUES (?, ?)
                   ON CONFLICT(key) DO UPDATE SET value = excluded.value')->execute([$key, $value]);
}

// Secret for cron.php, created on first use (i.e. first visit to report.php).
function cron_token(): string
{
    $t = setting_get('cron_token');
    if ($t === null || $t === '') {
        $t = bin2hex(random_bytes(16));
        setting_set('cron_token', $t);
    }
    return $t;
}

// Columns that round-trip through CSV export/import, in export order.
function csv_columns(): array
{
    return ['id', 'type', 'color_name', 'brand', 'color_hex', 'color_hex2', 'total_weight_g', 'spool_weight_g',
            'original_g', 'remaining_g', 'abrasive', 'highflow', 'silk', 'matte', 'rainbow', 'transparent', 'twotone',
            'sparkle', 'cost', 'notes', 'last_used_at', 'created_at', 'updated_at'];
}

// The whole inventory as a CSV string (with UTF-8 BOM so Excel decodes it).
function inventory_csv(): string
{
    $cols = csv_columns();
    $rows = db()->query(
        'SELECT ' . implode(', ', $cols) . ' FROM filaments
         ORDER BY brand COLLATE NOCASE, type COLLATE NOCASE, id'
    )->fetchAll();
    $fh = fopen('php://temp', 'r+');
    fputcsv($fh, $cols, ',', '"', '');
    foreach ($rows as $r) {
        fputcsv($fh, array_values($r), ',', '"', '');
    }
    rewind($fh);
    $csv = stream_get_contents($fh);
    fclose($fh);
    return "\xEF\xBB\xBF" . $csv;
}

// Header cell -> canonical column name; '' for empty/unknown headers.
function csv_canon_header(string $h): string
{
    // First cell of a file may carry the UTF-8 BOM.
    $h = str_replace("\xEF\xBB\xBF", '', $h);
    $h = trim(preg_replace('/[^a-z0-9]+/', '_', strtolower($h)), '_');
    $alias = [
        'material' => 'type', 'filament_type' => 'type',
        'manufacturer' => 'brand', 'maker' => 'brand', 'vendor' => 'brand',
        'colour_name' => 'color_name', 'name' => 'color_name',
        'hex' => 'color_hex', 'colour_hex' => 'color_hex', 'color' => 'color_hex', 'colour' => 'color_hex',
        'total_weight' => 'total_weight_g', 'total_g' => 'total_weight_g', 'gross_g' => 'total_weight_g',
        'spool_weight' => 'spool_weight_g', 'empty_spool_g' => 'spool_weight_g', 'tare' => 'spool_weight_g', 'tare_g' => 'spool_weight_g',
        'original' => 'original_g', 'nominal_g' => 'original_g', 'label_weight_g' => 'original_g',
        'remaining' => 'remaining_g', 'left_g' => 'remaining_g', 'remaining_weight_g' => 'remaining_g',
        'price' => 'cost', 'note' => 'notes', 'comment' => 'notes',
        'clear' => 'transparent',
        'hex2' => 'color_hex2', 'color2' => 'color_hex2', 'colour_hex2' => 'color_hex2', 'second_color' => 'color_hex2',
        'two_tone' => 'twotone', 'dual_color' => 'twotone', 'color_shifting' => 'twotone',
        'glitter' => 'sparkle', 'galaxy' => 'sparkle', 'sparkly' => 'sparkle',
    ];
    $h = $alias[$h] ?? $h;
    return in_array($h, csv_columns(), true) ? $h : '';
}

// "1.234,5" / "1,5" / "812.4 g" -> float; ''/garbage -> null. Whichever of
// . and , comes last is taken as the decimal separator.
function csv_num(?string $s): ?float
{
    $s = preg_replace('/[^0-9,.\-]/', '', trim((string)$s)); // strip units, currency, spaces
    if ($s === '') {
        return null;
    }
    $lastComma = strrpos($s, ',');
    if ($lastComma !== false && $lastComma > (int)strrpos($s, '.')) {
        $s = str_replace(',', '.', str_replace('.', '', $s));
    } else {
        $s = str_replace(',', '', $s);
    }
    return is_numeric($s) ? (float)$s : null;
}

function csv_flag(?string $s): int
{
    return in_array(strtolower(trim((string)$s)), ['1', 'true', 'yes', 'y', 'x'], true) ? 1 : 0;
}

// Import a CSV file. Rows with an id matching an existing spool are updated
// (only the columns present in the file) when $updateExisting is on;
// everything else is inserted. Returns [inserted, updated, skipped] or throws
// with a user-facing message.
function import_csv(string $path, bool $updateExisting): array
{
    $fh = fopen($path, 'r');
    if (!$fh) {
        throw new RuntimeException('Could not read the uploaded file.');
    }
    // Excel in some locales writes semicolon-separated "CSV".
    $firstLine = (string)fgets($fh);
    $delim = substr_count($firstLine, ';') > substr_count($firstLine, ',') ? ';' : ',';
    rewind($fh);

    $head = fgetcsv($fh, 0, $delim, '"', '');
    if (!$head) {
        fclose($fh);
        throw new RuntimeException('The file is empty.');
    }
    $map = array_map('csv_canon_header', array_map('strval', $head));
    if (!in_array('type', $map, true)) {
        fclose($fh);
        throw new RuntimeException('No "type" column found — the first row must be a header row (as produced by the CSV export).');
    }

    $pdo = db();
    $inserted = $updated = $skipped = 0;
    backup_db();
    $pdo->beginTransaction();
    try {
        while (($line = fgetcsv($fh, 0, $delim, '"', '')) !== false) {
            $row = [];
            foreach ($map as $i => $col) {
                if ($col !== '' && isset($line[$i])) {
                    $row[$col] = trim((string)$line[$i]);
                }
            }
            if ($row === [] || implode('', $row) === '') {
                continue; // blank line
            }

            $type = (string)($row['type'] ?? '');
            if ($type === '') {
                $skipped++;
                continue;
            }
            $sw    = csv_num($row['spool_weight_g'] ?? null) ?? 0.0;
            $orig  = csv_num($row['original_g'] ?? null);
            $orig  = ($orig !== null && $orig > 0) ? $orig : 1000.0;
            $total = csv_num($row['total_weight_g'] ?? null);
            $rem   = csv_num($row['remaining_g'] ?? null);
            // Weights that files from other tools may not carry are derived:
            // total from what's on the spool, remaining from total - tare.
            $total ??= ($rem ?? $orig) + $sw;
            $rem ??= $total - $sw;
            $rem = max(0.0, $rem);
            if ($total <= 0 || $sw < 0 || $total <= $sw) {
                $skipped++;
                continue;
            }

            $color = strtoupper((string)($row['color_hex'] ?? ''));
            if ($color !== '' && preg_match('/^[0-9A-F]{6}$/', $color)) {
                $color = '#' . $color; // allow hex without the #
            }
            if (!preg_match('/^#[0-9A-Fa-f]{6}$/', $color)) {
                $color = '#888888';
            }
            $color2 = strtoupper((string)($row['color_hex2'] ?? ''));
            if ($color2 !== '' && preg_match('/^[0-9A-F]{6}$/', $color2)) {
                $color2 = '#' . $color2;
            }
            if (!preg_match('/^#[0-9A-Fa-f]{6}$/', $color2)) {
                $color2 = null; // the second color is optional
            }

            $vals = [
                'type' => $type,
                'brand' => (string)($row['brand'] ?? ''),
                'color_name' => (string)($row['color_name'] ?? ''),
                'color_hex' => $color,
                'color_hex2' => $color2,
                'total_weight_g' => $total,
                'spool_weight_g' => $sw,
                'original_g' => $orig,
                'remaining_g' => $rem,
                'abrasive' => csv_flag($row['abrasive'] ?? null),
                'highflow' => csv_flag($row['highflow'] ?? null),
                'silk' => csv_flag($row['silk'] ?? null),
                'matte' => csv_flag($row['matte'] ?? null),
                'rainbow' => csv_flag($row['rainbow'] ?? null),
                'transparent' => csv_flag($row['transparent'] ?? null),
                'twotone' => csv_flag($row['twotone'] ?? null),
                'sparkle' => csv_flag($row['sparkle'] ?? null),
                'cost' => csv_num($row['cost'] ?? null),
                'notes' => (string)($row['notes'] ?? ''),
                'last_used_at' => ($row['last_used_at'] ?? '') !== '' ? $row['last_used_at'] : null,
            ];

            $id = (int)($row['id'] ?? 0);
            $exists = false;
            if ($updateExisting && $id > 0) {
                $st = $pdo->prepare('SELECT 1 FROM filaments WHERE id = ?');
                $st->execute([$id]);
                $exists = (bool)$st->fetchColumn();
            }
            if ($exists) {
                // Only touch columns the file actually has values for, so a
                // trimmed-down CSV (say id/remaining_g) doesn't blank the rest
                // or overwrite real weights with derived ones.
                $set = [];
                $params = [];
                foreach ($vals as $col => $v) {
                    if (($row[$col] ?? '') !== '') {
                        $set[] = "$col=?";
                        $params[] = $v;
                    }
                }
                if (!$set) {
                    $skipped++;
                    continue;
                }
                $params[] = $id;
                $pdo->prepare('UPDATE filaments SET ' . implode(', ', $set) . ", updated_at=datetime('now') WHERE id=?")
                    ->execute($params);
                $updated++;
            } else {
                $cols = array_keys($vals);
                if (($row['created_at'] ?? '') !== '') {
                    $vals['created_at'] = $row['created_at'];
                    $cols[] = 'created_at';
                }
                $pdo->prepare(
                    'INSERT INTO filaments (' . implode(', ', $cols) . ') VALUES (' . implode(',', array_fill(0, count($cols), '?')) . ')'
                )->execute(array_values($vals));
                $inserted++;
            }
        }
        $pdo->commit();
    } catch (Throwable $ex) {
        $pdo->rollBack();
        fclose($fh);
        throw new RuntimeException('Import failed, nothing was changed: ' . $ex->getMessage());
    }
    fclose($fh);
    return [$inserted, $updated, $skipped];
}

// Plain-text inventory summary for the email body. Low spools first so the
// morning glance answers "do I need to order filament?".
function report_text(): string
{
    $rows = db()->query('SELECT * FROM filaments')->fetchAll();
    $pct = function (array $r): int {
        $orig = (float)($r['original_g'] ?? 0);
        if ($orig <= 0) {
            $orig = 1000.0;
        }
        return max(0, min(100, (int)round(100 * (float)$r['remaining_g'] / $orig)));
    };
    usort($rows, fn($a, $b) => $pct($a) <=> $pct($b));

    $line = function (array $r) use ($pct): string {
        $label = trim(($r['brand'] ?: '') . ' ' . $r['type']);
        if (!empty($r['color_name'])) {
            $label .= ' · ' . $r['color_name'];
        }
        return sprintf('  %-44s %5d g left (%d%%)', $label, round((float)$r['remaining_g']), $pct($r));
    };

    $totalG = array_sum(array_map(fn($r) => (float)$r['remaining_g'], $rows));
    $low = array_filter($rows, fn($r) => $pct($r) < 15);

    $out = config()['site_name'] . ' — report for ' . date('D, M j, Y') . "\n\n";
    $out .= count($rows) . ' spool' . (count($rows) === 1 ? '' : 's') . ' · '
          . number_format($totalG / 1000, 2) . " kg filament remaining\n";
    if ($low) {
        $out .= "\nRunning low (under 15% left):\n" . implode("\n", array_map($line, $low)) . "\n";
    }
    $out .= "\nAll spools:\n" . ($rows ? implode("\n", array_map($line, $rows)) : "  (inventory is empty)") . "\n";
    $out .= "\nThe full inventory is attached as CSV.\n";
    return $out;
}

// Email the report with the CSV attached. Uses PHP's mail(); $err carries a
// hint when sending fails.
function send_report_email(string $to, ?string &$err = null): bool
{
    if (!filter_var($to, FILTER_VALIDATE_EMAIL)) {
        $err = 'Invalid email address.';
        return false;
    }
    $from = (string)(config()['mail_from'] ?? '');
    if ($from === '') {
        $host = strtok((string)($_SERVER['HTTP_HOST'] ?? 'localhost'), ':');
        $from = 'filament-inventory@' . ($host ?: 'localhost');
    }
    $fname = 'filaments-' . date('Y-m-d') . '.csv';
    $boundary = 'bnd_' . bin2hex(random_bytes(12));
    $body = "--$boundary\r\n"
          . "Content-Type: text/plain; charset=UTF-8\r\n"
          . "Content-Transfer-Encoding: base64\r\n\r\n"
          . chunk_split(base64_encode(report_text()))
          . "--$boundary\r\n"
          . "Content-Type: text/csv; charset=UTF-8; name=\"$fname\"\r\n"
          . "Content-Disposition: attachment; filename=\"$fname\"\r\n"
          . "Content-Transfer-Encoding: base64\r\n\r\n"
          . chunk_split(base64_encode(inventory_csv()))
          . "--$boundary--\r\n";
    $headers = "From: $from\r\n"
             . "MIME-Version: 1.0\r\n"
             . "Content-Type: multipart/mixed; boundary=\"$boundary\"";
    $subject = config()['site_name'] . ' report — ' . date('M j, Y');

    error_clear_last();
    $ok = @mail($to, $subject, $body, $headers);
    if (!$ok) {
        // With html_errors on, PHP pre-escapes the message (&quot; etc.).
        $err = error_get_last()['message'] ?? '';
        $err = $err !== '' ? html_entity_decode($err, ENT_QUOTES)
                           : 'mail() returned false — is the server set up to send email?';
    }
    return $ok;
}

// Send the daily report if one is enabled, due (past the chosen hour, server
// time), and not yet attempted today. Called on inventory views and by
// cron.php, so the report goes out even without a cron job — just later,
// on the first visit of the day.
function maybe_send_daily_report(): void
{
    $to = (string)setting_get('report_email', '');
    if ($to === '' || setting_get('report_daily') !== '1') {
        return;
    }
    $today = date('Y-m-d');
    if (setting_get('report_last_sent') === $today || (int)date('G') < (int)setting_get('report_hour', '7')) {
        return;
    }
    // Mark the attempt first so a slow or crashing mail() can't fire on every
    // page view; a failed day is visible on report.php and can be re-sent.
    setting_set('report_last_sent', $today);
    $ok = send_report_email($to, $err);
    setting_set('report_last_status', ($ok ? "Sent to $to" : 'FAILED: ' . $err) . ' — ' . date('Y-m-d H:i'));
}