/*jslint beta, bitwise, browser, devel, nomen*/

import {

assertOrThrow,

csvFromListofList,

dbCloseAsync,

dbExecAsync,

dbFileSaveAsync,

dbFileLoadAsync,

dbOpenAsync,

dbTableImportAsync,

debugInline,

noop,

sqlmathWebworkerInit

} from "./sqlmath.mjs";

let BLOB_SAVE;

let CRISPX = -0.5;

let CRISPY = 0.5;

let {

CodeMirror

} = window;

let DBTABLE_DICT = new Map();

let DB_CHART;

let DB_DICT = new Map();

let DB_INIT = Promise.resolve();

let DB_MAIN;

let DB_QUERY;

let DEBOUNCE_DICT = Object.create(null);

let UI_ANIMATE_DATENOW;

let UI_ANIMATE_DURATION = 250;

let UI_ANIMATE_DURATION_INV = 1 / UI_ANIMATE_DURATION;

let UI_ANIMATE_LIST = [];

let UI_CONTEXTMENU = document.getElementById("contextmenu1");

let UI_CONTEXTMENU_BATON;

let UI_CRUD = document.getElementById("crudPanel1");

let UI_EDITOR;

let UI_FILE_OPEN = document.createElement("input");

let UI_FILE_SAVE = document.createElement("a");

let UI_LOADING = document.getElementById("loadingPanel1");

let UI_LOADING_COUNTER = 0;

let UI_PAGE_SIZE = 256;

let UI_ROW_HEIGHT = 16;

let UI_VIEW_SIZE = 20;

noop(debugInline);

async function dbFileAttachAsync({

db,

dbData

}) {

// this function will attach database <dbData> to <db>

let dbAttached;

let dbName = dbNameNext("attach{{ii}}", new Set(DB_DICT.keys()));

dbAttached = await dbOpenAsync({

dbData,

filename: `file:${dbName}?mode=memory&cache=shared`

});

dbAttached.dbName = dbName;

await dbExecAsync({

db,

sql: `ATTACH DATABASE '${dbAttached.filename}' AS ${dbName}`

});

DB_DICT.set(dbName, dbAttached);

// normalize order

DB_DICT = Array.from(DB_DICT.values());

DB_DICT = new Map([

DB_DICT.slice(0, 3),

DB_DICT.slice(3).sort(function (aa, bb) {

aa = aa.dbName;

bb = bb.dbName;

return (

aa < bb

? -1

: 1

);

})

].flat().map(function (db) {

return [

db.dbName, db

];

}));

}

function dbNameNext(template, bag) {

// this function will get next incremental name in <bag> from given <template>

let ii = 0;

let name;

while (true) {

ii += 1;

name = template.replace("{{ii}}", String(ii).padStart(2, "0"));

if (!bag.has(name)) {

return name;

}

}

}

function debounce(key, func, ...argList) {

// this function will debounce <func> with given <key>

let val = DEBOUNCE_DICT[key];

if (val) {

val.func = func;

return;

}

val = {

func: noop,

timerTimeout: setTimeout(function () {

delete DEBOUNCE_DICT[key];

val.func(...argList);

}, 250)

};

DEBOUNCE_DICT[key] = val;

// if first-time, then immediately call <func>

func(...argList);

}

async function demoDefault() {

// this function will run demo-default

// attach demo-db

await dbFileAttachAsync({

db: DB_MAIN,

dbData: new ArrayBuffer(0)

});

UI_EDITOR.setValue(String(`

DROP TABLE IF EXISTS __stock_historical;

CREATE TABLE __stock_historical(sym TEXT, date TEXT, price REAL);

INSERT INTO __stock_historical (sym, date, price) VALUES

('aapl', '2020-01-01', 77.37), ('aapl', '2020-02-01', 68.33),

('aapl', '2020-03-01', 63.57), ('aapl', '2020-04-01', 73.44),

('aapl', '2020-05-01', 79.48), ('aapl', '2020-06-01', 91.19),

('aapl', '2020-07-01', 106.26), ('aapl', '2020-08-01', 129.03),

('aapl', '2020-09-01', 115.80), ('aapl', '2020-10-01', 108.86),

('aapl', '2020-11-01', 119.05), ('aapl', '2020-12-01', 132.69),

('goog', '2020-01-01', 1434.23), ('goog', '2020-02-01', 1339.33),

('goog', '2020-03-01', 1162.81), ('goog', '2020-04-01', 1348.66),

('goog', '2020-05-01', 1428.92), ('goog', '2020-06-01', 1413.61),

('goog', '2020-07-01', 1482.96), ('goog', '2020-08-01', 1634.18),

('goog', '2020-09-01', 1469.60), ('goog', '2020-10-01', 1621.01),

('goog', '2020-11-01', 1760.74), ('goog', '2020-12-01', 1751.88);

DROP TABLE IF EXISTS __test1;

CREATE TABLE __test1(

col1,

col2,

column_long_name_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

);

DROP TABLE IF EXISTS __test2;

CREATE TABLE __test2 AS

SELECT * FROM __test1

UNION ALL SELECT 1, 2, 3

UNION ALL SELECT 4, 5, 6;

DROP TABLE IF EXISTS attach01.__test3;

CREATE TABLE attach01.__test3 AS SELECT * FROM __test2;

SELECT

*,

random() AS c1,

random() AS c2,

random() AS c3,

random() AS c4,

random(),

random(),

random(),

random(),

1 AS sentinel

FROM __stock_historical

LEFT JOIN __test1 ON __test1.col1 = __stock_historical.sym

CROSS JOIN (SELECT random() FROM __stock_historical);

DROP TABLE IF EXISTS chart.__stock_chart;

CREATE TABLE chart.__stock_chart (

datatype TEXT NOT NULL,

series_index INTEGER,

xx REAL,

yy REAL,

series_label REAL,

xx_label TEXT,

options TEXT

);

INSERT INTO chart.__stock_chart (datatype, options)

SELECT

'options' AS datatype,

'{

"title": "price vs. date comparison of multiple stocks",

"xaxisTitle": "date",

"yaxisTitle": "percent gain",

"yvalueSuffix": " %"

}' AS options;

INSERT INTO chart.__stock_chart (datatype, series_index, series_label)

SELECT

'series_label' AS datatype,

rownum AS series_index,

sym AS series_label

FROM (

SELECT

ROW_NUMBER() OVER (ORDER BY sym) AS rownum,

sym

FROM (SELECT DISTINCT sym FROM __stock_historical)

WHERE

sym IS NOT NULL

);

INSERT INTO chart.__stock_chart (datatype, xx, xx_label)

SELECT

'xx_label' AS datatype,

rownum AS xx,

date AS xx_label

FROM (

SELECT

ROW_NUMBER() OVER (ORDER BY date) AS rownum,

date

FROM (SELECT DISTINCT date FROM __stock_historical)

);

INSERT INTO chart.__stock_chart (datatype, series_index, xx, yy)

SELECT

'yy_value' AS datatype,

series_index,

xx,

price AS yy

FROM (

SELECT

series_index,

series_label,

xx,

xx_label

FROM (

SELECT

series_index,

series_label

FROM chart.__stock_chart

WHERE

datatype = 'series_label'

)

JOIN (

SELECT

xx,

xx_label

FROM chart.__stock_chart

WHERE

datatype = 'xx_label'

)

)

LEFT JOIN __stock_historical ON sym = series_label AND date = xx_label;

UPDATE chart.__stock_chart

SET

yy = yy * inv - 1

FROM (

--

SELECT

1.0 / yy AS inv,

series_index

FROM (

SELECT

ROW_NUMBER() OVER (

PARTITION BY series_index ORDER BY xx

) AS rownum,

yy,

series_index

FROM chart.__stock_chart

WHERE

datatype = 'yy_value'

AND yy > 0

)

WHERE

rownum = 1

--

) AS __join1 WHERE __join1.series_index = __stock_chart.series_index;

`).trim() + "\n");

// exec demo-sql-query

await onDbExec({});

return true;

}

async function demoTradebot() {

// this function will run demo-tradebot

let tmp;

let tradebotState;

try {

tmp = await fetch(`.tradebot_public.sqlite?aa=${Date.now()}`);

if (tmp.status !== 200) {

return;

}

} catch (ignore) {

return;

}

tmp = await tmp.arrayBuffer();

await dbFileLoadAsync({

db: DB_MAIN,

dbData: tmp

});

tradebotState = noop(

await dbExecAsync({

db: DB_MAIN,

sql: "SELECT * FROM tradebot_state;"

})

)[0][0];

UI_EDITOR.setValue([

(`

-- table - tradebot_intraday_day - insert

DROP TABLE IF EXISTS tradebot_intraday_day;

CREATE TABLE tradebot_intraday_day AS

SELECT

sym,

xdate,

price

FROM tradebot_intraday

WHERE xdate >= (SELECT datemkt0_beg FROM tradebot_state)

--

UNION ALL

--

SELECT

sym,

DATETIME(datemkt0_beg, '-1 MINUTE') AS xdate,

price

FROM tradebot_historical

JOIN tradebot_state

WHERE tradebot_historical.xdate = datemkt0_lag;

-- table - tradebot_intraday_month - insert

DROP TABLE IF EXISTS tradebot_intraday_month;

CREATE TABLE tradebot_intraday_month AS

SELECT

sym,

xdate,

price

FROM tradebot_intraday

WHERE xdate = xdate2;

-- table - tradebot_tech_intra_day - insert - lmt

DROP TABLE IF EXISTS tradebot_tech_intra_day;

CREATE TABLE tradebot_tech_intra_day(

tname TEXT,

xdate REAL,

xdate2 REAL,

tval REAL

);

INSERT INTO tradebot_tech_intra_day

SELECT

*

FROM (

SELECT

'1a_stk_pct' AS tname,

xdate,

xdate2,

stk_pct AS tval

FROM tradebot_tech_intra

--

UNION ALL

--

SELECT '1b_stk_lmt', xdate, xdate2, stk_lmt FROM tradebot_tech_intra

--

UNION ALL

--

SELECT '1c_stk_pnl', xdate, xdate2, stk_pnl FROM tradebot_tech_intra

--

UNION ALL

--

SELECT '2a_spy_prc', xdate, xdate2, spy_prc FROM tradebot_tech_intra

--

UNION ALL

--

SELECT '2b_spy_cos', xdate, xdate2, spy_cos FROM tradebot_tech_intra

--

UNION ALL

--

SELECT '2c_spy_sin', xdate, xdate2, spy_sin FROM tradebot_tech_intra

--

UNION ALL

--

SELECT '2d_spy_syy', xdate, xdate2, spy_syy FROM tradebot_tech_intra

--

UNION ALL

--

SELECT '2e_spy_zcl', xdate, xdate2, spy_zcl FROM tradebot_tech_intra

)

WHERE xdate AND tval IS NOT NULL;

-- table - tradebot_tech_intra_month - insert - lmt

DROP TABLE IF EXISTS tradebot_tech_intra_month;

CREATE TABLE tradebot_tech_intra_month(

tname TEXT,

xdate REAL,

xdate2 REAL,

tval REAL

);

INSERT INTO tradebot_tech_intra_month

SELECT * FROM tradebot_tech_intra_day WHERE xdate = xdate2;

DELETE FROM tradebot_tech_intra_day

WHERE xdate < (SELECT datemkt0 FROM tradebot_state);

ALTER TABLE tradebot_tech_intra_day DROP COLUMN xdate2;

ALTER TABLE tradebot_tech_intra_month DROP COLUMN xdate2;

`),

[

"1 day",

"1 month",

"ytd",

"1 year",

"3 year",

"3 year reverse timeline"

].map(function (dateInterval) {

let optionDict;

let tableChart;

let tableData;

tableData = (

dateInterval === "1 day"

? "tradebot_intraday_day"

: dateInterval === "1 month"

? "tradebot_intraday_month"

: "tradebot_historical"

);

tableChart = (

"chart._{{ii}}_tradebot_historical_"

+ dateInterval.replace((

/\W/g

), "_")

);

optionDict = {

title: (

"tradebot historical performance vs market - "

+ dateInterval

+ (

dateInterval === "1 day"

? "\n[ updated " + new Date(

tradebotState.datenow + "Z"

).toUTCString() + " ]"

: ""

)

),

xaxisTitle: "date",

xstep: (

dateInterval === "1 day"

? 60

: dateInterval === "1 month"

? 15 * 60

: 1

),

xvalueConvert: (

(dateInterval === "1 day" || dateInterval === "1 month")

? "unixepochToTimeutc"

: "juliandayToDate"

),

yaxisTitle: "percent gain",

yvalueSuffix: " %"

};

return (`

-- chart - ${tableChart} - create

DROP TABLE IF EXISTS ${tableChart};

CREATE TABLE ${tableChart} (

datatype TEXT NOT NULL,

series_index INTEGER,

xx REAL,

yy REAL,

series_label REAL,

xx_label TEXT,

options TEXT

);

INSERT INTO ${tableChart} (datatype, options)

SELECT

'options' AS datatype,

'${JSON.stringify(optionDict)}' AS options;

INSERT INTO ${tableChart} (datatype, options, series_index, series_label)

SELECT

'series_label' AS datatype,

JSON_OBJECT(

'isDummy', is_dummy,

'isHidden', NOT sym IN ('11_mybot', 'spy')

) AS options,

rownum AS series_index,

sym AS series_label

FROM (

SELECT

sym LIKE '-%' AS is_dummy,

ROW_NUMBER() OVER (

ORDER BY

sym = '11_mybot' DESC,

sym = '----' DESC,

sym = 'spy' DESC,

sym = 'qqq' DESC,

sym = 'dia' DESC,

sym = '---- ' DESC,

sym

) AS rownum,

sym

FROM (

SELECT DISTINCT sym FROM ${tableData}

--

UNION ALL

--

SELECT '----'

--

UNION ALL

--

SELECT '---- '

)

);

DROP TABLE IF EXISTS __tmp1;

CREATE TEMP TABLE __tmp1 AS

SELECT

*

FROM (SELECT DISTINCT xdate FROM ${tableData})

JOIN (SELECT MIN(xdate) AS aa, MAX(xdate) AS bb FROM ${tableData});

UPDATE __tmp1

SET

aa = aa2

FROM (

SELECT

xdate AS aa2

FROM __tmp1

JOIN (

SELECT

MAX(aa,

${

(

dateInterval === "3 year reverse timeline"

? "DATE(bb, '-3 YEAR')"

: dateInterval === "ytd"

? "DATE(STRFTIME('%Y', bb) || '-01-01', '-1 DAY')"

: "DATE(bb, '-" + dateInterval + "')"

)

}

) AS aa2

FROM (SELECT aa, bb FROM __tmp1 LIMIT 1)

)

WHERE

xdate <= aa2

ORDER BY

xdate DESC

LIMIT 1

);

INSERT INTO ${tableChart} (datatype, xx, xx_label)

SELECT

'xx_label' AS datatype,

rownum AS xx,

xdate AS xx_label

FROM (

SELECT

ROW_NUMBER() OVER (ORDER BY xdate ASC) AS rownum,

xdate

FROM __tmp1

WHERE

aa <= xdate AND xdate <= bb

);

INSERT INTO ${tableChart} (datatype, series_index, xx, yy)

SELECT

'yy_value' AS datatype,

series_index,

xx,

price AS yy

FROM (

SELECT

series_index,

series_label,

xx,

xx_label

FROM (

SELECT

series_index,

series_label

FROM ${tableChart}

WHERE

datatype = 'series_label'

)

JOIN (

SELECT

xx,

xx_label

FROM ${tableChart}

WHERE

datatype = 'xx_label'

)

)

LEFT JOIN ${tableData} ON sym = series_label AND xdate = xx_label;

UPDATE ${tableChart}

SET

${

(

dateInterval === "3 year reverse timeline"

? "yy = ROUND(100 * (1.0 / (yy * inv) - 1), 4)"

: "yy = ROUND(100 * (yy * inv - 1), 4)"

)

}

FROM (

--

SELECT

1.0 / yy AS inv,

series_index

FROM (

SELECT

ROW_NUMBER() OVER (

PARTITION BY series_index ORDER BY xx

${

(

dateInterval === "3 year reverse timeline"

? "DESC"

: "ASC"

)

}

) AS rownum,

yy,

series_index

FROM ${tableChart}

WHERE

datatype = 'yy_value'

AND yy > 0

)

WHERE

rownum = 1

--

) AS __join1 WHERE __join1.series_index = ${tableChart}.series_index;

UPDATE ${tableChart}

SET

series_label = printf(

'%+06.2f%% - %s%s',

yy_today,

series_label,

IIF(CASTTEXTOREMPTY(company_name) = '', '', ' - ' || company_name)

)

FROM (

--

SELECT

${tableChart}.rowid,

--

company_name,

yy_today

FROM ${tableChart}

--

LEFT JOIN (

SELECT

series_index,

yy_today

FROM (

SELECT

ROW_NUMBER() OVER (

PARTITION BY series_index ORDER BY xx DESC

) AS rownum,

series_index,

yy AS yy_today

FROM ${tableChart}

WHERE

datatype = 'yy_value'

)

WHERE

rownum = 1

) USING (series_index)

LEFT JOIN tradebot_stock_basket ON sym = series_label

WHERE

datatype = 'series_label'

--

) AS __join1 WHERE __join1.rowid = ${tableChart}.rowid;

-- chart - tradebot_historical - normalize xx to unixepoch

UPDATE ${tableChart}

SET

xx = ${(

(dateInterval === "1 day" || dateInterval === "1 month")

? "UNIXEPOCH(xdate)"

: "JULIANDAY(xdate)"

)}

FROM (

--

SELECT

${tableChart}.rowid,

--

xdate

FROM ${tableChart}

--

JOIN (

SELECT

xx,

xx_label AS xdate

FROM ${tableChart}

WHERE

datatype = 'xx_label'

) USING (xx)

WHERE

datatype = 'yy_value'

--

) AS __join1 WHERE __join1.rowid = ${tableChart}.rowid;

INSERT INTO ${tableChart} (datatype, series_index, xx, yy)

SELECT

'yy_value' AS datatype,

series_index,

xx0 AS xx,

0 AS yy

FROM (

SELECT

series_index

FROM ${tableChart}

WHERE

datatype = 'series_label'

)

JOIN (

SELECT

MIN(xx) AS xx0

FROM ${tableChart}

WHERE

datatype = 'yy_value'

)

WHERE

${(dateInterval === "1 day" || dateInterval === "1 month")};

DELETE FROM ${tableChart} WHERE datatype = 'xx_label';

`);

}),

[

"subindustry",

"stock"

].map(function (grouping) {

return [

"performance",

"holding"

].map(function (ptype) {

let columnData;

let optionDict;

let sqlSelect;

let tableChart;

columnData = (

ptype === "performance"

? "perc_gain_today"

: "perc_holding"

);

optionDict = {

isBarchart: true,

title: `tradebot ${ptype} today by ${grouping}`,

xaxisTitle: grouping,

yaxisTitle: (

ptype === "performance"

? "percent gain"

: "percent holding"

),

yvalueSuffix: " %"

};

sqlSelect = (

grouping === "subindustry"

? (`

SELECT

(CASE

WHEN (category LIKE 'index%') THEN 3

WHEN (category LIKE '___-') THEN 1

ELSE 5

END) AS series_color,

category LIKE '-%' AS is_dummy,

0 AS is_hidden,

printf('%05.4f%% - %s', ${columnData}, category) AS series_label,

ROW_NUMBER() OVER (

ORDER BY

grouping_index,

category != '----' DESC,

${columnData} DESC

) AS xx,

category AS xx_label,

${columnData} AS yy

FROM (

SELECT

category,

grouping,

grouping_index,

${columnData},

perc_holding

FROM tradebot_position_category

WHERE

grouping = 'subindustry'

)

`)

: (`

SELECT

(CASE

WHEN (subindustry LIKE '___+') THEN 3

WHEN (subindustry LIKE '___-') THEN 1

ELSE 2

END) AS series_color,

0 AS is_dummy,

0 AS is_hidden,

printf(

'%+.4f%% - %s - %s - %s',

${columnData},

'long',

sym,

company_name

) AS series_label,

ROW_NUMBER() OVER (ORDER BY ${columnData} DESC) AS xx,

sym AS xx_label,

${columnData} AS yy

FROM tradebot_position_stock

`)

);

tableChart = `chart._{{ii}}_tradebot_${grouping}_${ptype}`;

return (`

-- chart - ${tableChart} - create

DROP TABLE IF EXISTS __tmp1;

CREATE TEMP TABLE __tmp1 AS SELECT * FROM (${sqlSelect}) ORDER BY xx;

DROP TABLE IF EXISTS ${tableChart};

CREATE TABLE ${tableChart} (

datatype TEXT NOT NULL,

series_index INTEGER,

xx REAL,

yy REAL,

series_label REAL,

xx_label TEXT,

options TEXT

);

INSERT INTO ${tableChart} (datatype, options)

SELECT

'options' AS datatype,

'${JSON.stringify(optionDict)}' AS options;

INSERT INTO ${tableChart} (

datatype,

options,

series_index,

series_label

)

SELECT

'series_label' AS datatype,

JSON_OBJECT(

'isDummy', is_dummy,

'isHidden', is_hidden,

'seriesColor', series_color

) AS options,

xx AS series_index,

series_label

FROM __tmp1;

INSERT INTO ${tableChart} (datatype, xx, xx_label)

SELECT

'xx_label' AS datatype,

xx,

xx_label

FROM __tmp1;

INSERT INTO ${tableChart} (

datatype,

series_index,

xx,

yy

)

SELECT

'yy_value' AS datatype,

xx AS series_index,

xx,

ROUNDORZERO(yy, 4) AS yy

FROM __tmp1;

`);

});

}),

(`

-- chart - tradebot_buysell_history

DROP TABLE IF EXISTS __tmp1;

CREATE TEMP TABLE __tmp1 AS

SELECT

series_color,

printf(

'%s %s',

xx_label,

series_label

) AS series_label,

xx,

xx_label,

yy

FROM (

SELECT

(CASE

WHEN (subindustry LIKE '___+') THEN 3

WHEN (subindustry LIKE '___-') THEN 1

ELSE 2

END) AS series_color,

(

buy_or_sell

|| ' '

|| sym

|| ' - '

|| company_name

) AS series_label,

UNIXEPOCH(time_filled) AS xx,

TIME(time_filled) AS xx_label,

ROUNDORZERO(

(

100 * 1.0 / asset_under_mgmt

* IIF(buy_or_sell = 'buy', 1, -1)

* order_value

),

4

) AS yy

FROM tradebot_account

JOIN tradebot_buysell_history

ORDER BY

time_filled

);

DROP TABLE IF EXISTS chart._{{ii}}_tradebot_buysell_history;

CREATE TABLE chart._{{ii}}_tradebot_buysell_history (

datatype TEXT NOT NULL,

series_index INTEGER,

xx REAL,

yy REAL,

series_label REAL,

xx_label TEXT,

options TEXT

);

INSERT INTO chart._{{ii}}_tradebot_buysell_history (datatype, options)

SELECT

'options' AS datatype,

'{

"isBarchart": true,

"title": "tradebot buy/sell history today\\n[ updated '

|| '${new Date(tradebotState.datenow + "Z").toUTCString()}'

|| ' ]",

"xaxisTitle": "time",

"xvalueConvert": "unixepochToTimeutc",

"yaxisTitle": "buy/sell value as percentage of account",

"yvalueSuffix": " %"

}' AS options

FROM tradebot_state;

INSERT INTO chart._{{ii}}_tradebot_buysell_history (

datatype,

options,

series_index,

series_label

)

SELECT

'series_label' AS datatype,

JSON_OBJECT('seriesColor', series_color) AS options,

__tmp1.rowid AS series_index,

series_label

FROM __tmp1;

INSERT INTO chart._{{ii}}_tradebot_buysell_history (

datatype,

series_index,

xx,

yy

)

SELECT

'yy_value' AS datatype,

__tmp1.rowid AS series_index,

xx,

yy

FROM __tmp1;

`),

[

"1 day",

"1 month"

].map(function (dateInterval) {

let optionDict;

let tableChart;

let tableData;

tableData = (

dateInterval === "1 day"

? "tradebot_tech_intra_day"

: dateInterval === "1 month"

? "tradebot_tech_intra_month"

: "tradebot_tech_intra"

);

tableChart = (

"chart._{{ii}}_tradebot_tech_intra_"

+ dateInterval.replace((

/\W/g

), "_")

);

optionDict = {

title: (

"tradebot technical intraday - "

+ dateInterval

+ (

dateInterval === "1 day"

? "\n[ updated " + new Date(

tradebotState.datenow + "Z"

).toUTCString() + " ]"

: ""

)

),

xaxisTitle: "date",

xstep: (

dateInterval === "1 day"

? 60

: dateInterval === "1 month"

? 5 * 60

: 1

),

xvalueConvert: (

(dateInterval === "1 day" || dateInterval === "1 month")

? "unixepochToTimeutc"

: "juliandayToDate"

),

yaxisTitle: "percent gain",

yvalueSuffix: " %"

};

return (`

UPDATE ${tableData}

SET

xdate = UNIXEPOCH(xdate),

tval = ROUNDORZERO(tval, 4);

-- chart - ${tableChart} - create

DROP TABLE IF EXISTS ${tableChart};

CREATE TABLE ${tableChart} (

datatype TEXT NOT NULL,

series_index INTEGER,

xx REAL,

yy REAL,

series_label REAL,

xx_label TEXT,

options TEXT

);

INSERT INTO ${tableChart} (datatype, options)

SELECT

'options' AS datatype,

'${JSON.stringify(optionDict)}' AS options;