#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test date-current-date {
  SELECT length(date('now')) = 10;
} {1}

do_execsql_test date-specific-date {
  SELECT date('2023-05-18');
} {2023-05-18}

do_execsql_test date-with-time {
  SELECT date('2023-05-18 15:30:45');
} {2023-05-18}

do_execsql_test date-iso8601 {
  SELECT date('2023-05-18T15:30:45');
} {2023-05-18}

do_execsql_test date-with-milliseconds {
  SELECT date('2023-05-18 15:30:45.123');
} {2023-05-18}

do_execsql_test date-julian-day-integer {
  SELECT date(2460082);
} {2023-05-17}

do_execsql_test date-julian-day-float {
  SELECT date(2460082.5);
} {2023-05-18}

do_execsql_test date-invalid-input {
  SELECT date('not a date');
} {{}}

do_execsql_test date-null-input {
  SELECT date(NULL);
} {{}}

do_execsql_test date-out-of-range {
  SELECT date('10001-01-01');
} {{}}

do_execsql_test date-time-only {
  SELECT date('15:30:45');
} {2000-01-01}

do_execsql_test date-with-timezone-utc {
  SELECT date('2023-05-18 15:30:45Z');
} {2023-05-18}

do_execsql_test date-with-timezone-positive {
  SELECT date('2023-05-18 23:30:45+02:00');
} {2023-05-18}

do_execsql_test date-with-timezone-negative {
  SELECT date('2023-05-19 01:30:45-05:00');
} {2023-05-19}

do_execsql_test date-with-timezone-day-change-positive {
  SELECT date('2023-05-18 23:30:45-03:00');
} {2023-05-19}

do_execsql_test date-with-timezone-day-change-negative {
  SELECT date('2023-05-19 01:30:45+03:00');
} {2023-05-18}

do_execsql_test date-with-timezone-iso8601 {
  SELECT date('2023-05-18T15:30:45+02:00');
} {2023-05-18}

do_execsql_test date-with-timezone-and-milliseconds {
  SELECT date('2023-05-18 15:30:45.123+02:00');
} {2023-05-18}

do_execsql_test date-with-invalid-timezone {
  SELECT date('2023-05-18 15:30:45+25:00');
} {{}}

do_execsql_test date-with-modifier-add-days {
  SELECT date('2023-05-18', '+10 days');
} {2023-05-28}

do_execsql_test date-with-modifier-subtract-days {
  SELECT date('2023-05-18', '-10 days');
} {2023-05-08}

do_execsql_test date-with-multiple-modifiers {
  SELECT date('2023-05-18', '+1 days', '-1 days', '+10 days');
} {2023-05-28}

do_execsql_test date-with-invalid-modifier {
  SELECT date('2023-05-18', 'invalid modifier');
} {{}}

do_execsql_test time-no-arg {
  SELECT length(time()) = 8;
} {1}

do_execsql_test time-current-time {
  SELECT length(time('now')) = 8;
} {1}

do_execsql_test time-specific-time {
  SELECT time('04:02:00');
} {04:02:00}

do_execsql_test time-of-datetime {
  SELECT time('2023-05-18 15:30:45');
} {15:30:45}

do_execsql_test time-iso8601 {
  SELECT time('2023-05-18T15:30:45');
} {15:30:45}

do_execsql_test time-with-milliseconds {
  SELECT time('2023-05-18 15:30:45.123');
} {15:30:45}

do_execsql_test time-julian-day-integer {
  SELECT time(2460082);
} {12:00:00}

do_execsql_test time-julian-day-float {
  SELECT time(2460082.2);
} {16:48:00}

do_execsql_test time-invalid-input {
  SELECT time('not a time');
} {{}}

do_execsql_test time-null-input {
  SELECT time(NULL);
} {{}}

do_execsql_test time-out-of-range {
  SELECT time('25:05:01');
} {{}}

do_execsql_test time-date-only {
  SELECT time('2024-02-02');
} {00:00:00}

do_execsql_test time-with-timezone-utc {
  SELECT time('2023-05-18 15:30:45Z');
} {15:30:45}

do_execsql_test time-with-timezone-positive {
  SELECT time('2023-05-18 23:30:45+07:00');
} {16:30:45}

do_execsql_test time-with-timezone-negative {
  SELECT time('2023-05-19 01:30:45-05:00');
} {06:30:45}

do_execsql_test time-with-timezone-day-change-positive {
  SELECT time('2023-05-18 23:30:45-03:00');
} {02:30:45}

do_execsql_test time-with-timezone-day-change-negative {
  SELECT time('2023-05-19 01:30:45+03:00');
} {22:30:45}

do_execsql_test time-with-timezone-iso8601 {
  SELECT time('2023-05-18T15:30:45+02:00');
} {13:30:45}

do_execsql_test time-with-timezone-and-milliseconds {
  SELECT time('2023-05-18 15:30:45.123+02:00');
} {13:30:45}

do_execsql_test time-with-invalid-timezone {
  SELECT time('2023-05-18 15:30:45+25:00');
} {{}}

do_execsql_test time-with-modifier-start-of-day {
  SELECT time('2023-05-18 15:30:45', 'start of day');
} {00:00:00}

do_execsql_test time-with-modifier-add-hours {
  SELECT time('2023-05-18 15:30:45', '+2 hours');
} {17:30:45}

do_execsql_test time-with-modifier-add-minutes {
  SELECT time('2023-05-18 15:30:45', '+45 minutes');
} {16:15:45}

do_execsql_test time-with-modifier-add-seconds {
  SELECT time('2023-05-18 15:30:45', '+30 seconds');
} {15:31:15}

do_execsql_test time-with-modifier-subtract-hours {
  SELECT time('2023-05-18 15:30:45', '-3 hours');
} {12:30:45}

do_execsql_test time-with-modifier-subtract-minutes {
  SELECT time('2023-05-18 15:30:45', '-15 minutes');
} {15:15:45}

do_execsql_test time-with-modifier-subtract-seconds {
  SELECT time('2023-05-18 15:30:45', '-45 seconds');
} {15:30:00}

do_execsql_test time-with-multiple-modifiers {
  SELECT time('2023-05-18 15:30:45', '+1 hours', '-30 minutes', '+15 seconds');
} {16:01:00}

do_execsql_test time-with-invalid-modifier {
  SELECT time('2023-05-18 15:30:45', 'invalid modifier');
} {{}}

do_execsql_test time-with-invalid-modifier {
  SELECT time('2023-05-18 15:30:45', '+1 hour', 'invalid modifier');
} {{}}

do_execsql_test unixepoch-at-start {
  SELECT unixepoch('1970-01-01');
} {0}

do_execsql_test unixepoch-at-1-second-before-epochtime {
  SELECT unixepoch('1969-12-31 23:59:59');
} {-1}

do_execsql_test unixepoch-at-future {
  SELECT unixepoch('9999-12-31 23:59:59');
} {253402300799}

do_execsql_test unixepoch-at-start-of-time {
  SELECT unixepoch('0000-01-01 00:00:00');
} {-62167219200}

do_execsql_test unixepoch-at-millisecond-precision-input-produces-seconds-precision-output {
  SELECT unixepoch('2022-01-27 12:59:28.052');
} {1643288368}

do_execsql_test date-with-modifier-start-of-day {
  SELECT date('2023-05-18 15:30:45', 'start of day');
} {2023-05-18}

do_execsql_test date-with-modifier-start-of-month {
  SELECT date('2023-05-18', 'start of month');
} {2023-05-01}

do_execsql_test date-with-modifier-start-of-year {
  SELECT date('2023-05-18', 'start of year');
} {2023-01-01}

do_execsql_test date-with-modifier-add-months {
  SELECT date('2023-05-18', '+2 months');
} {2023-07-18}

do_execsql_test date-with-modifier-subtract-months {
  SELECT date('2023-05-18', '-3 months');
} {2023-02-18}

do_execsql_test date-with-modifier-add-years {
  SELECT date('2023-05-18', '+1 year');
} {2024-05-18}

do_execsql_test date-with-modifier-subtract-years {
  SELECT date('2023-05-18', '-2 years');
} {2021-05-18}

do_execsql_test date-with-modifier-weekday {
  SELECT date('2023-05-18', 'weekday 0');
} {2023-05-21}

do_execsql_test date-with-multiple-modifiers {
  SELECT date('2023-05-18', '+1 month', '-10 days', 'start of year');
} {2023-01-01}

do_execsql_test date-with-subsec {
  SELECT date('2023-05-18 15:30:45.123', 'subsec');
} {2023-05-18}

do_execsql_test time-with-modifier-add-hours {
  SELECT time('2023-05-18 15:30:45', '+5 hours');
} {20:30:45}

do_execsql_test time-with-modifier-subtract-hours {
  SELECT time('2023-05-18 15:30:45', '-2 hours');
} {13:30:45}

do_execsql_test time-with-modifier-add-minutes {
  SELECT time('2023-05-18 15:30:45', '+45 minutes');
} {16:15:45}

do_execsql_test time-with-modifier-subtract-seconds {
  SELECT time('2023-05-18 15:30:45', '-50 seconds');
} {15:29:55}

do_execsql_test time-with-subsec {
  SELECT time('2023-05-18 15:30:45.123', 'subsec');
} {15:30:45.123}

do_execsql_test time-with-modifier-add {
  SELECT time('15:30:45', '+15:30:15');
} {{07:01:00}}

do_execsql_test time-with-modifier-sub {
  SELECT time('15:30:45', '-15:30:15');
} {{00:00:30}}

do_execsql_test date-with-modifier-add-months {
  SELECT date('2023-01-31', '+1 month');
} {2023-03-03}

do_execsql_test date-with-modifier-subtract-months {
  SELECT date('2023-03-31', '-1 month');
} {2023-03-03}

do_execsql_test date-with-modifier-add-months-large {
  SELECT date('2023-01-31', '+13 months');
} {2024-03-02}

do_execsql_test date-with-modifier-subtract-months-large {
  SELECT date('2023-01-31', '-13 months');
} {2021-12-31}

do_execsql_test date-with-modifier-february-leap-year {
  SELECT date('2020-02-29', '+12 months');
} {2021-03-01}

do_execsql_test date-with-modifier-february-non-leap-year {
  SELECT date('2019-02-28', '+12 months');
} {2020-02-28}

do_execsql_test date-with-modifier-invalid-date {
  SELECT date('2023-02-15 15:30:45', '-0001-01-01 00:00');
} {2022-01-14}

do_execsql_test date-with-modifier-date {
  SELECT date('2023-02-15 15:30:45', '+0001-01-01');
} {2024-03-16}

do_execsql_test datetime-with-modifier-datetime-pos {
  SELECT datetime('2023-02-15 15:30:45', '+0001-01-01 15:30');
} {{2024-03-17 07:00:45}}

do_execsql_test datetime-with-modifier-datetime-neg {
  SELECT datetime('2023-02-15 15:30:45', '+0001-01-01 15:30');
} {{2024-03-17 07:00:45}}

do_execsql_test datetime-with-modifier-datetime-large {
   SELECT datetime('2023-02-15 15:30:45', '+7777-10-10 23:59');
} {{9800-12-26 15:29:45}}

do_execsql_test datetime-with-modifier-datetime-sub-large {
   SELECT datetime('2023-02-15 15:30:45', '-2024-10-10 23:59');
} {{-0002-04-04 15:31:45}}

do_execsql_test datetime-with-timezone-utc {
  SELECT datetime('2023-05-18 15:30:45Z');
} {{2023-05-18 15:30:45}}

do_execsql_test datetime-with-modifier-sub {
   SELECT datetime('2023-12-12', '-0002-10-10 15:30:45');
} {{2021-02-01 08:29:15}}

do_execsql_test datetime-with-modifier-add {
   SELECT datetime('2023-12-12', '+0002-10-10 15:30:45');
} {{2026-10-22 15:30:45}}

do_execsql_test time-with-multiple-modifiers {
  SELECT time('2023-05-18 15:30:45', '+1 hours', '-20 minutes', '+15 seconds', 'subsec');
} {16:11:00.000}

do_execsql_test datetime-with-multiple-modifiers {
select datetime('2024-01-31', '+1 month', '+13 hours', '+5 minutes', '+62 seconds');
} {{2024-03-02 13:06:02}}

do_execsql_test datetime-with-weekday {
  SELECT datetime('2023-05-18', 'weekday 3');
} {{2023-05-24 00:00:00}}

do_execsql_test unixepoch-subsec {
  SELECT unixepoch('2023-05-18 15:30:45.123');
} {1684423845}

do_execsql_test unixepoch-invalid-date {
  SELECT unixepoch('not-a-date');
} {{}}

do_execsql_test unixepoch-leap-second {
  SELECT unixepoch('2015-06-30 23:59:60');
} {{}}

do_execsql_test unixepoch-negative-timestamp {
  SELECT unixepoch('1969-12-31 23:59:59');
} {-1}

do_execsql_test unixepoch-large-date {
  SELECT unixepoch('9999-12-31 23:59:59');
} {253402300799}

do_execsql_test datetime-with-timezone {
  SELECT datetime('2023-05-19 01:30:45+03:00');
} {{2023-05-18 22:30:45}}

do_execsql_test julianday-fractional {
    SELECT julianday('2023-05-18 15:30:45.123');
} {2460083.14635559}

do_execsql_test julianday-fractional-2 {
    SELECT julianday('2000-01-01 12:00:00.500');
} {2451545.00000579}

do_execsql_test julianday-rounded-up {
    SELECT julianday('2023-05-18 15:30:45.129');
} {2460083.14635566}

do_execsql_test julianday-with-timezone {
    SELECT julianday('2023-05-18 15:30:45+02:00');
} {2460083.06302083}

do_execsql_test julianday-fractional-seconds {
    SELECT julianday('2023-05-18 15:30:45.123');
} {2460083.14635559}

do_execsql_test julianday-time-only {
    SELECT julianday('15:30:45');
} {2451545.14635417}

do_execsql_test julianday-midnight {
    SELECT julianday('2023-05-18 00:00:00');
} {2460082.5}

do_execsql_test julianday-noon {
    SELECT julianday('2023-05-18 12:00:00');
} {2460083.0}

do_execsql_test julianday-fractional-zero {
    SELECT julianday('2023-05-18 00:00:00.000');
} {2460082.5}

do_execsql_test julianday-date-only {
    SELECT julianday('2023-05-18');
} {2460082.5}

do_execsql_test julianday-with-modifier-day {
    SELECT julianday(2454832.5,'+1 day');
} {2454833.5}

do_execsql_test julianday-with-modifier-hour {
    SELECT julianday(2454832.5,'-3 hours');
} {2454832.375}

do_execsql_test julianday-max-day {
  SELECT julianday('9999-12-31 23:59:59');
} {5373484.49998843}



# Strftime tests


set FMT {%d,%e,%f,%F,%G,%g,%H,%I,%j,%J,%k,%l,%i,%m,%M,%p,%P,%R,%s,%S,%T,%U,%u,%V,%w,%W,%Y,%%}

do_execsql_test strftime-day {
    SELECT strftime('%d', '2025-01-23T13:10:30.567');
} {23}

do_execsql_test strftime-day-without-leading-zero-1 {
    SELECT strftime('%e', '2025-01-23T13:10:30.567');
} {23}

do_execsql_test strftime-day-without-leading-zero-2 {
    SELECT strftime('%e', '2025-01-02T13:10:30.567');
} {" 2"} 
# TODO not a typo in sqlite there is also a space 

do_execsql_test strftime-fractional-seconds {
    SELECT strftime('%f', '2025-01-02T13:10:30.567');
} {30.567}

do_execsql_test strftime-iso-8601-date {
    SELECT strftime('%F', '2025-01-23T13:10:30.567');
} {2025-01-23}

do_execsql_test strftime-iso-8601-year {
    SELECT strftime('%G', '2025-01-23T13:10:30.567');
} {2025}

do_execsql_test strftime-iso-8601-year-2_digit {
    SELECT strftime('%g', '2025-01-23T13:10:30.567');
} {25}

do_execsql_test strftime-hour {
    SELECT strftime('%H', '2025-01-23T13:10:30.567');
} {13}

do_execsql_test strftime-hour-12-hour-clock {
    SELECT strftime('%I', '2025-01-23T13:10:30.567');
} {01}

do_execsql_test strftime-day-of-year {
    SELECT strftime('%j', '2025-01-23T13:10:30.567');
} {023}

do_execsql_test strftime-julianday {
    SELECT strftime('%J', '2025-01-23T13:10:30.567');
} {2460699.048964896}

do_execsql_test strftime-hour-without-leading-zero-1 {
    SELECT strftime('%k', '2025-01-23T13:10:30.567');
} {13}

do_execsql_test strftime-hour-without-leading-zero-2 {
    SELECT strftime('%k', '2025-01-23T02:10:30.567');
} {" 2"}

do_execsql_test strftime-hour-12-hour-clock-without-leading-zero-2 {
    SELECT strftime('%l', '2025-01-23T13:10:30.567');
} {" 1"}

do_execsql_test strftime-month {
    SELECT strftime('%m', '2025-01-23T13:10:30.567');
} {01}

do_execsql_test strftime-minute {
    SELECT strftime('%M', '2025-01-23T13:14:30.567');
} {14}

do_execsql_test strftime-am-pm=1 {
    SELECT strftime('%p', '2025-01-23T11:14:30.567');
} {AM}

do_execsql_test strftime-am-pm-2 {
    SELECT strftime('%p', '2025-01-23T13:14:30.567');
} {PM}

do_execsql_test strftime-am-pm-lower-1 {
    SELECT strftime('%P', '2025-01-23T11:14:30.567');
} {am}

do_execsql_test strftime-am-pm-lower-2 {
    SELECT strftime('%P', '2025-01-23T13:14:30.567');
} {pm}

do_execsql_test strftime-iso8601-time {
    SELECT strftime('%R', '2025-01-23T13:14:30.567');
} {13:14}

do_execsql_test strftime-seconds-since-epoch {
    SELECT strftime('%s', '2025-01-23T13:14:30.567');
} {1737638070}

do_execsql_test strftime-seconds {
    SELECT strftime('%S', '2025-01-23T13:14:30.567');
} {30}

do_execsql_test strftime-iso8601-with-seconds {
    SELECT strftime('%T', '2025-01-23T13:14:30.567');
} {13:14:30}

do_execsql_test strftime-week-year-start-sunday {
    SELECT strftime('%U', '2025-01-23T13:14:30.567');
} {03}

do_execsql_test strftime-day-week-start-monday {
    SELECT strftime('%u', '2025-01-23T13:14:30.567');
} {4}

do_execsql_test strftime-iso8601-week-year {
    SELECT strftime('%V', '2025-01-23T13:14:30.567');
} {04}

do_execsql_test strftime-day-week-start-sunday {
    SELECT strftime('%w', '2025-01-23T13:14:30.567');
} {4}

do_execsql_test strftime-day-week-start-sunday {
    SELECT strftime('%w', '2025-01-23T13:14:30.567');
} {4}

do_execsql_test strftime-week-year-start-sunday {
    SELECT strftime('%W', '2025-01-23T13:14:30.567');
} {03}

do_execsql_test strftime-year {
    SELECT strftime('%Y', '2025-01-23T13:14:30.567');
} {2025}

do_execsql_test strftime-percent {
    SELECT strftime('%%', '2025-01-23T13:14:30.567');
} {%}

# Tests that should return null or empty string
# Will test formatter strings that exist in chrono
# But should not exist in sqlite

set FMT [list %S.%3f %C %y %b %B %h %a %A %D %x %v %.f %.3f %.6f %.9f %3f %6f %9f %X %r %Z %z %:z %::z %:::z %#z %c %+ %t %n %-? %_? %0?]

foreach i $FMT {
  do_execsql_test strftime-invalid-$i "SELECT strftime('$i','2025-01-23T13:14:30.567');" {}
}

do_execsql_test strftime-julianday {
  SELECT strftime('%Y-%m-%d %H:%M:%fZ', 2459717.08070103);
} {"2022-05-17 13:56:12.569Z"}


# Tests for the TIMEDIFF function

do_execsql_test timediff-basic-positive {
  SELECT timediff('14:30:45', '12:00:00');
} {"+0000-00-00 02:30:45.000"}

do_execsql_test timediff-basic-negative {
  SELECT timediff('12:00:00', '14:30:45');
} {"-0000-00-00 02:30:45.000"}

do_execsql_test timediff-with-milliseconds-positive {
  SELECT timediff('12:00:01.300', '12:00:00.500');
} {"+0000-00-00 00:00:00.800"}

do_execsql_test timediff-same-time {
  SELECT timediff('12:00:00', '12:00:00');
} {"+0000-00-00 00:00:00.000"}

do_execsql_test timediff-across-dates {
  SELECT timediff('2023-05-11 01:15:00', '2023-05-10 23:30:00');
} {"+0000-00-00 01:45:00.000"}

do_execsql_test timediff-across-dates-negative {
  SELECT timediff('2023-05-10 23:30:00', '2023-05-11 01:15:00');
} {"-0000-00-00 01:45:00.000"}

do_execsql_test timediff-different-formats {
  SELECT timediff('2023-05-10T23:30:00', '2023-05-10 14:15:00');
} {"+0000-00-00 09:15:00.000"}

do_execsql_test timediff-with-timezone {
  SELECT timediff('2023-05-10 23:30:00+02:00', '2023-05-10 18:30:00Z');
} {"+0000-00-00 03:00:00.000"}

do_execsql_test timediff-large-difference {
  SELECT timediff('2023-05-12 10:00:00', '2023-05-10 08:00:00');
} {"+0000-00-02 02:00:00.000"}

do_execsql_test timediff-with-seconds-precision {
  SELECT timediff('12:30:45.123', '12:30:44.987');
} {"+0000-00-00 00:00:00.136"}

do_execsql_test timediff-null-first-arg {
  SELECT timediff(NULL, '12:00:00');
} {{}}

do_execsql_test timediff-null-second-arg {
  SELECT timediff('12:00:00', NULL);
} {{}}

do_execsql_test timediff-invalid-first-arg {
  SELECT timediff('not-a-time', '12:00:00');
} {{}}

do_execsql_test timediff-invalid-second-arg {
  SELECT timediff('12:00:00', 'not-a-time');
} {{}}

do_execsql_test timediff-julian-day {
  SELECT timediff(2460000, 2460000.5);
} {"-0000-00-00 12:00:00.000"}

do_execsql_test timediff-different-time-formats {
  SELECT timediff('23:59:59', '00:00:00');
} {"+0000-00-00 23:59:59.000"}