<?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 (" 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'));

}