// SQLite Bulk Insert via JSON
// Demonstrates high-performance insertion of many records in a single SQL call 
// by passing a JSON string and using the json_each() table function.

uses System.Data;

// 1. Prepare data on the script side using an array of anonymous records
var users : array of record
   Name : String;
   Email : String;
   Level : Integer;
end;

users.SetLength(4);
users[0].Name := 'Alice';   users[0].Email := 'alice@example.com';   users[0].Level := 5;
users[1].Name := 'Bob';     users[1].Email := 'bob@example.com';     users[1].Level := 12;
users[2].Name := 'Charlie'; users[2].Email := 'charlie@example.com'; users[2].Level := 8;
users[3].Name := 'Dave';    users[3].Email := 'dave@example.com';    users[3].Level := 20;

var db := DataBase.Create('SQLite', [':memory:']);
db.Exec('CREATE TABLE Users (Name TEXT, Email TEXT, Level INTEGER)');

// 2. Convert the entire array to a single JSON string
var jsonData := JSON.Stringify(users);

PrintLn('<h3>High-Performance Bulk Insert</h3>');
PrintLn('<p>Passing ' + IntToStr(users.Length) + ' records as a single JSON parameter.</p>');

// 3. Execute bulk insert using a multiline raw string for SQL
db.Exec(#'
  INSERT INTO Users (Name, Email, Level)
  SELECT json_extract(value, "$.Name"), 
         json_extract(value, "$.Email"), 
         json_extract(value, "$.Level")
  FROM json_each(?)
  ', [ jsonData ]
);

// Verify results - Selecting specific fields allows using efficient indexed access
var ds := db.Query(#'
  SELECT Name, Email, Level
  FROM Users
  ORDER BY Level DESC
');
PrintLn('<h4>Resulting Table:</h4>');
PrintLn('<table class="table table-sm table-striped">');
PrintLn('<tr><th>Name</th><th>Email</th><th>Level</th></tr>');

// ds.Step() is a convenient way to iterate: it moves to the next record and returns True
while ds.Step do begin
   PrintLn(Format(
     '<tr><td>%s</td><td>%s</td><td>%d</td></tr>',
     [
       ds.AsString(0).ToHtml,
       ds.AsString(1).ToHtml,
       ds.AsInteger(2) 
     ]
   ));
end;
PrintLn('</table>');