// SPDX-FileCopyrightText: © 2026 Jeffrey C. Ollie <jeff@ocjtech.us>
// SPDX-License-Identifier: MIT

//! An installer database as text: what `zixl-info` and `zixl-dump` print.
//!
//! Tables are written as text archive (`.idt`) files, in the format
//! Microsoft documents for `MsiDatabaseExport`, and can also be written as
//! SQL statements, as msitools' `msiinfo export -s` writes them.
//! `docs/adr/0006-text-archives-follow-microsofts-format.md` says where
//! that format and msitools' part company. Times are in UTC, since zixl has
//! no local time zone to put them in.

const std = @import("std");
const Allocator = std.mem.Allocator;
const Io = std.Io;

const Database = @import("Database.zig");
const Column = Database.Column;
const Value = Database.Value;
const codepage = @import("codepage.zig");
const written = @import("write.zig");

pub const Error = Allocator.Error || Io.Writer.Error;

/// Lines end as they do on Windows.
const eol = "\r\n";

/// The column definition of `c`, as the second row of a text archive
/// writes it: `s72`, `L0`, `i2`, `v0`.
pub fn definition(c: Column) Definition {
    return .{ .column = c };
}

pub const Definition = struct {
    column: Column,

    pub fn format(d: Definition, w: *Io.Writer) Io.Writer.Error!void {
        const c = d.column;
        const letter: u8 = switch (c.type) {
            .string => if (c.localizable) 'l' else 's',
            .short, .long => 'i',
            .binary => 'v',
        };
        try w.writeByte(if (c.nullable) std.ascii.toUpper(letter) else letter);
        switch (c.type) {
            .string => try w.print("{d}", .{c.size}),
            .short => try w.writeByte('2'),
            .long => try w.writeByte('4'),
            .binary => try w.writeByte('0'),
        }
    }
};

/// The name of the file a binary cell's contents are exported to, in a
/// folder named for its table: the row's key values joined by periods,
/// then `.ibd`.
pub fn binaryFileName(gpa: Allocator, t: *const Database.Table, row: []const Value) Allocator.Error![]u8 {
    const stream = try t.streamName(gpa, row);
    defer gpa.free(stream);
    return std.fmt.allocPrint(gpa, "{s}.ibd", .{stream[t.name.len + 1 ..]});
}

/// Writes `value` with the control characters a text archive translates,
/// so that none can be taken for a delimiter.
fn writeTranslated(w: *Io.Writer, value: []const u8) Io.Writer.Error!void {
    for (value) |c| try w.writeByte(switch (c) {
        0 => 21,
        8 => 27,
        '\t' => 16,
        '\n' => 25,
        12 => 24,
        '\r' => 17,
        else => c,
    });
}

fn isAscii(s: []const u8) bool {
    for (s) |c| if (c >= 0x80) return false;
    return true;
}

/// Writes table `t` of a database in code page `cp` as a text archive. A
/// table holding non-ASCII text is written in that code page, which is
/// named first on the third row; when some text cannot be stored in it, it
/// is written in UTF-8 instead, and named 65001.
pub fn writeTable(gpa: Allocator, w: *Io.Writer, cp: u16, t: *const Database.Table) Error!void {
    var arena_state: std.heap.ArenaAllocator = .init(gpa);
    defer arena_state.deinit();
    const arena = arena_state.allocator();

    // Whether any text is non-ASCII, and whether it all fits the code page.
    var ascii = true;
    var fits = true;
    for (t.rows.items) |row| for (row) |v| if (v == .string and !isAscii(v.string)) {
        ascii = false;
        _ = codepage.encode(arena, cp, v.string) catch |e| switch (e) {
            error.Unencodable => fits = false,
            error.OutOfMemory => return error.OutOfMemory,
        };
    };
    const out_cp: u16 = if (fits) cp else codepage.utf8;

    for (t.columns, 0..) |c, i| try w.print("{s}{s}", .{ if (i == 0) "" else "\t", c.name });
    try w.writeAll(eol);
    for (t.columns, 0..) |c, i| try w.print("{s}{f}", .{ if (i == 0) "" else "\t", definition(c) });
    try w.writeAll(eol);
    if (!ascii) try w.print("{d}\t", .{out_cp});
    try w.writeAll(t.name);
    for (t.columns) |c| if (c.key) try w.print("\t{s}", .{c.name});
    try w.writeAll(eol);

    for (t.rows.items) |row| {
        for (row, t.columns, 0..) |v, c, i| {
            if (i > 0) try w.writeByte('\t');
            switch (v) {
                .null => {},
                .int => |n| try w.print("{d}", .{n}),
                .string => |text| try writeTranslated(w, if (out_cp == codepage.utf8) text else codepage.encode(arena, out_cp, text) catch unreachable),
                .stream => {
                    _ = c;
                    try w.writeAll(try binaryFileName(arena, t, row));
                },
            }
        }
        try w.writeAll(eol);
    }
}

/// Writes `_ForceCodepage.idt`: two blank lines, then the code page.
pub fn writeForceCodepage(w: *Io.Writer, cp: u16) Io.Writer.Error!void {
    try w.print(eol ++ eol ++ "{d}\t_ForceCodepage" ++ eol, .{cp});
}

/// Writes `_SummaryInformation.idt`: each property present, by its ID, with
/// times as `YYYY/MM/DD hh:mm:ss`.
pub fn writeSummary(gpa: Allocator, w: *Io.Writer, s: Database.SummaryInformation) Error!void {
    var arena_state: std.heap.ArenaAllocator = .init(gpa);
    defer arena_state.deinit();
    const arena = arena_state.allocator();

    const strings = [_]struct { u8, ?[]const u8 }{
        .{ 2, s.title },
        .{ 3, s.subject },
        .{ 4, s.author },
        .{ 5, s.keywords },
        .{ 6, s.comments },
        .{ 7, s.template },
        .{ 8, s.last_saved_by },
        .{ 9, s.revision_number },
        .{ 18, s.creating_application },
    };
    var ascii = true;
    var fits = true;
    for (strings) |f| if (f[1]) |v| if (!isAscii(v)) {
        ascii = false;
        _ = codepage.encode(arena, s.codepage, v) catch |e| switch (e) {
            error.Unencodable => fits = false,
            error.OutOfMemory => return error.OutOfMemory,
        };
    };
    const out_cp: u16 = if (fits) s.codepage else codepage.utf8;

    try w.writeAll("PropertyId\tValue" ++ eol ++ "i2\tl255" ++ eol);
    if (!ascii) try w.print("{d}\t", .{out_cp});
    try w.writeAll("_SummaryInformation\tPropertyId" ++ eol);
    try w.print("1\t{d}" ++ eol, .{s.codepage});
    const Line = struct { u8, union(enum) { text: []const u8, time: i64, int: i32 } };
    var lines: std.ArrayList(Line) = .empty;
    for (strings) |f| if (f[1]) |v| try lines.append(arena, .{ f[0], .{ .text = v } });
    if (s.create_time) |t| try lines.append(arena, .{ 12, .{ .time = t } });
    if (s.last_save_time) |t| try lines.append(arena, .{ 13, .{ .time = t } });
    if (s.page_count) |v| try lines.append(arena, .{ 14, .{ .int = v } });
    if (s.word_count) |v| try lines.append(arena, .{ 15, .{ .int = v } });
    if (s.character_count) |v| try lines.append(arena, .{ 16, .{ .int = v } });
    if (s.security) |v| try lines.append(arena, .{ 19, .{ .int = v } });
    std.mem.sort(Line, lines.items, {}, struct {
        fn lessThan(_: void, a: Line, b: Line) bool {
            return a[0] < b[0];
        }
    }.lessThan);
    for (lines.items) |l| {
        try w.print("{d}\t", .{l[0]});
        switch (l[1]) {
            .text => |text| try writeTranslated(w, if (out_cp == codepage.utf8) text else codepage.encode(arena, out_cp, text) catch unreachable),
            .time => |t| {
                const c: Civil = .fromSeconds(t);
                try w.print("{d:0>4}/{d:0>2}/{d:0>2} {d:0>2}:{d:0>2}:{d:0>2}", .{ @as(u64, @intCast(@max(c.year, 0))), c.month, c.day, c.hour, c.minute, c.second });
            },
            .int => |n| try w.print("{d}", .{n}),
        }
        try w.writeAll(eol);
    }
}

/// The tables that describe the others, `_Tables` and `_Columns`, built as
/// `write` stores them.
pub fn systemTable(arena: Allocator, db: *const Database, name: []const u8) Allocator.Error!?*Database.Table {
    const t = try arena.create(Database.Table);
    if (std.mem.eql(u8, name, "_Tables")) {
        t.* = .{ .name = "_Tables", .columns = &written.tables_columns };
        for (db.tables.items) |each| try t.insert(arena, &.{.{ .string = each.name }});
    } else if (std.mem.eql(u8, name, "_Columns")) {
        t.* = .{ .name = "_Columns", .columns = &written.columns_columns };
        for (db.tables.items) |each| for (each.columns, 1..) |c, n| try t.insert(arena, &.{
            .{ .string = each.name },
            .{ .int = @intCast(n) },
            .{ .string = c.name },
            .{ .int = written.columnType(c) },
        });
    } else return null;
    return t;
}

/// Writes table `t` as SQL: a `CREATE TABLE` statement, then an `INSERT`
/// for each row, naming only the columns that are not null. A binary cell
/// is written as an empty string, since SQL cannot insert one. Text is
/// UTF-8, with quotes, backslashes and line breaks escaped by a backslash.
pub fn writeSql(w: *Io.Writer, t: *const Database.Table) Io.Writer.Error!void {
    try w.print("CREATE TABLE `{s}` (", .{t.name});
    for (t.columns, 0..) |c, i| {
        if (i > 0) try w.writeAll(", ");
        try w.print("`{s}` ", .{c.name});
        switch (c.type) {
            .string => try w.print("CHAR({d})", .{c.size}),
            .short => try w.writeAll("INT"),
            .long => try w.writeAll("LONG"),
            .binary => try w.writeAll("OBJECT"),
        }
        if (!c.nullable) try w.writeAll(" NOT NULL");
        if (c.localizable) try w.writeAll(" LOCALIZABLE");
    }
    try w.writeAll(" PRIMARY KEY ");
    var first = true;
    for (t.columns) |c| if (c.key) {
        try w.print("{s}`{s}`", .{ if (first) "" else ", ", c.name });
        first = false;
    };
    try w.writeAll(")\n");

    for (t.rows.items) |row| {
        try w.print("INSERT INTO `{s}` (", .{t.name});
        first = true;
        for (row, t.columns) |v, c| if (present(v)) {
            try w.print("{s}`{s}`", .{ if (first) "" else ", ", c.name });
            first = false;
        };
        try w.writeAll(") VALUES (");
        first = true;
        for (row) |v| if (present(v)) {
            if (!first) try w.writeAll(", ");
            first = false;
            switch (v) {
                .int => |n| try w.print("{d}", .{n}),
                .string => |text| {
                    try w.writeByte('\'');
                    for (text) |ch| switch (ch) {
                        '\'' => try w.writeAll("\\'"),
                        '\\' => try w.writeAll("\\\\"),
                        '\n' => try w.writeAll("\\n"),
                        '\r' => try w.writeAll("\\r"),
                        else => try w.writeByte(ch),
                    };
                    try w.writeByte('\'');
                },
                .stream => try w.writeAll("''"),
                .null => unreachable,
            }
        };
        try w.writeAll(")\n");
    }
}

fn present(v: Value) bool {
    return switch (v) {
        .null => false,
        .string => |s| s.len > 0,
        else => true,
    };
}

/// A date and time in the proleptic Gregorian calendar, in UTC.
pub const Civil = struct {
    year: i64,
    /// 1 to 12.
    month: u8,
    /// 1 to 31.
    day: u8,
    hour: u8,
    minute: u8,
    second: u8,
    /// 0 for Sunday to 6 for Saturday.
    weekday: u8,

    /// The time `seconds` after the Unix epoch. Howard Hinnant's
    /// civil_from_days, which is exact across the whole range.
    pub fn fromSeconds(seconds: i64) Civil {
        const days = @divFloor(seconds, 86400);
        const rest: u32 = @intCast(@mod(seconds, 86400));
        const z = @as(i128, days) + 719468;
        const era = @divFloor(z, 146097);
        const doe: u32 = @intCast(z - era * 146097);
        const yoe = (doe - doe / 1460 + doe / 36524 - doe / 146096) / 365;
        const doy = doe - (365 * yoe + yoe / 4 - yoe / 100);
        const mp = (5 * doy + 2) / 153;
        const d: u8 = @intCast(doy - (153 * mp + 2) / 5 + 1);
        const m: u8 = @intCast(if (mp < 10) mp + 3 else mp - 9);
        const y = @as(i128, yoe) + era * 400 + @intFromBool(m <= 2);
        return .{
            .year = @intCast(std.math.clamp(y, std.math.minInt(i64), std.math.maxInt(i64))),
            .month = m,
            .day = d,
            .hour = @intCast(rest / 3600),
            .minute = @intCast(rest / 60 % 60),
            .second = @intCast(rest % 60),
            // 1970-01-01 was a Thursday.
            .weekday = @intCast(@mod(@as(i128, days) + 4, 7)),
        };
    }
};

/// A time as C's `ctime` writes it: `Thu Jan  1 00:00:00 1970`.
pub fn ctime(seconds: i64) Ctime {
    return .{ .civil = .fromSeconds(seconds) };
}

pub const Ctime = struct {
    civil: Civil,

    pub fn format(t: Ctime, w: *Io.Writer) Io.Writer.Error!void {
        const days = [_][]const u8{ "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" };
        const months = [_][]const u8{ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
        const c = t.civil;
        try w.print("{s} {s} {d:2} {d:0>2}:{d:0>2}:{d:0>2} {d}", .{
            days[c.weekday], months[c.month - 1], c.day, c.hour, c.minute, c.second, c.year,
        });
    }
};

test "ctime" {
    var buf: [64]u8 = undefined;
    try std.testing.expectEqualStrings("Thu Jan  1 00:00:00 1970", try std.fmt.bufPrint(&buf, "{f}", .{ctime(0)}));
    try std.testing.expectEqualStrings("Tue Nov 14 22:13:20 2023", try std.fmt.bufPrint(&buf, "{f}", .{ctime(1_700_000_000)}));
    try std.testing.expectEqualStrings("Mon Dec 31 23:59:59 1979", try std.fmt.bufPrint(&buf, "{f}", .{ctime(315_532_799)}));
    try std.testing.expectEqualStrings("Wed Mar  1 00:00:00 2000", try std.fmt.bufPrint(&buf, "{f}", .{ctime(951_868_800)}));
}

test "the extremes do not overflow" {
    _ = Civil.fromSeconds(std.math.maxInt(i64));
    _ = Civil.fromSeconds(std.math.minInt(i64));
}

fn expectTable(cp: u16, t: *const Database.Table, expected: []const u8) !void {
    var out: Io.Writer.Allocating = .init(std.testing.allocator);
    defer out.deinit();
    try writeTable(std.testing.allocator, &out.writer, cp, t);
    try std.testing.expectEqualStrings(expected, out.written());
}

test "a table as a text archive" {
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    const a = arena.allocator();
    var t: Database.Table = .{ .name = "Media", .columns = @import("schema.zig").find("Media").?.columns };
    try t.insert(a, &.{ .{ .int = 1 }, .{ .int = 3 }, .null, .{ .string = "#one.cab" }, .null, .null });
    try expectTable(1252, &t, "DiskId\tLastSequence\tDiskPrompt\tCabinet\tVolumeLabel\tSource\r\n" ++
        "i2\ti4\tL64\tS255\tS32\tS72\r\n" ++
        "Media\tDiskId\r\n" ++
        "1\t3\t\t#one.cab\t\t\r\n");
}

test "non-ASCII text is in the code page, which the third row names" {
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    const a = arena.allocator();
    var t: Database.Table = .{ .name = "Property", .columns = @import("schema.zig").find("Property").?.columns };
    try t.insert(a, &.{ .{ .string = "Name" }, .{ .string = "Café" } });
    try expectTable(1252, &t, "Property\tValue\r\ns72\tl0\r\n1252\tProperty\tProperty\r\nName\tCaf\xe9\r\n");
    // What the code page cannot hold is written in UTF-8.
    try t.insert(a, &.{ .{ .string = "Greek" }, .{ .string = "Ω" } });
    try expectTable(1252, &t, "Property\tValue\r\ns72\tl0\r\n65001\tProperty\tProperty\r\nName\tCafé\r\nGreek\tΩ\r\n");
}

test "control characters are translated" {
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    const a = arena.allocator();
    var t: Database.Table = .{ .name = "Property", .columns = @import("schema.zig").find("Property").?.columns };
    try t.insert(a, &.{ .{ .string = "P" }, .{ .string = "a\tb\r\nc\x00\x08\x0c" } });
    try expectTable(0, &t, "Property\tValue\r\ns72\tl0\r\nProperty\tProperty\r\nP\ta\x10b\x11\x19c\x15\x1b\x18\r\n");
}

test "a binary cell names its file" {
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    const a = arena.allocator();
    var t: Database.Table = .{ .name = "Binary", .columns = @import("schema.zig").find("Binary").?.columns };
    try t.insert(a, &.{ .{ .string = "Books" }, .{ .stream = "..." } });
    try expectTable(0, &t, "Name\tData\r\ns72\tv0\r\nBinary\tName\r\nBooks\tBooks.ibd\r\n");
}

test "_ForceCodepage and _SummaryInformation" {
    var out: Io.Writer.Allocating = .init(std.testing.allocator);
    defer out.deinit();
    try writeForceCodepage(&out.writer, 932);
    try std.testing.expectEqualStrings("\r\n\r\n932\t_ForceCodepage\r\n", out.written());
    out.clearRetainingCapacity();
    try writeSummary(std.testing.allocator, &out.writer, .{ .codepage = 1252, .title = "Installation Database", .create_time = 922_116_345, .page_count = 110 });
    try std.testing.expectEqualStrings(
        "PropertyId\tValue\r\ni2\tl255\r\n_SummaryInformation\tPropertyId\r\n" ++
            "1\t1252\r\n2\tInstallation Database\r\n12\t1999/03/22 15:25:45\r\n14\t110\r\n",
        out.written(),
    );
}

test "a table as SQL" {
    var arena: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena.deinit();
    const a = arena.allocator();
    var t: Database.Table = .{ .name = "Property", .columns = @import("schema.zig").find("Property").?.columns };
    try t.insert(a, &.{ .{ .string = "Q" }, .{ .string = "it's a\\b" } });
    var out: Io.Writer.Allocating = .init(std.testing.allocator);
    defer out.deinit();
    try writeSql(&out.writer, &t);
    try std.testing.expectEqualStrings(
        "CREATE TABLE `Property` (`Property` CHAR(72) NOT NULL, `Value` CHAR(0) NOT NULL LOCALIZABLE PRIMARY KEY `Property`)\n" ++
            "INSERT INTO `Property` (`Property`, `Value`) VALUES ('Q', 'it\\'s a\\\\b')\n",
        out.written(),
    );
}