-- Excel semantics for the generated SQL (DuckDB). Generated by Power Migrate.
-- Dates are Excel serial numbers (days since 1899-12-30); Excel errors are NULL.

-- Text of a number as Excel's "General" format shows it (15 significant digits).
CREATE OR REPLACE MACRO xl_text(x) AS
  CASE WHEN x IS NULL THEN NULL ELSE upper(printf('%.15g', CAST(x AS DOUBLE))) END;

-- Rounding on the decimal value, as Excel does: ROUND(1.005, 2) = 1.01, half away from zero.
CREATE OR REPLACE MACRO xl_round(x, n) AS
  CASE WHEN x IS NULL OR n IS NULL THEN NULL
       WHEN abs(x) >= 1e22 THEN round(CAST(x AS DOUBLE), CAST(n AS INTEGER))
       ELSE CAST(round(CAST(x AS DECIMAL(38, 15)), CAST(n AS INTEGER)) AS DOUBLE) END;
CREATE OR REPLACE MACRO xl_roundup(x, n) AS
  CASE WHEN x IS NULL OR n IS NULL THEN NULL
       WHEN abs(xl_round(x, n)) >= abs(x) THEN xl_round(x, n)
       ELSE xl_round(x, n) + sign(x) * power(10, -CAST(n AS INTEGER)) END;
CREATE OR REPLACE MACRO xl_rounddown(x, n) AS
  CASE WHEN x IS NULL OR n IS NULL THEN NULL
       WHEN abs(xl_round(x, n)) <= abs(x) THEN xl_round(x, n)
       ELSE xl_round(x, n) - sign(x) * power(10, -CAST(n AS INTEGER)) END;
CREATE OR REPLACE MACRO xl_mod(a, b) AS
  CASE WHEN a IS NULL OR b IS NULL OR b = 0 THEN NULL ELSE a - b * floor(a / b) END;
CREATE OR REPLACE MACRO xl_sqrt(x) AS CASE WHEN x IS NULL OR x < 0 THEN NULL ELSE sqrt(x) END;
CREATE OR REPLACE MACRO xl_ln(x) AS CASE WHEN x IS NULL OR x <= 0 THEN NULL ELSE ln(x) END;
CREATE OR REPLACE MACRO xl_log10(x) AS CASE WHEN x IS NULL OR x <= 0 THEN NULL ELSE log10(x) END;
CREATE OR REPLACE MACRO xl_power(a, b) AS
  CASE WHEN a IS NULL OR b IS NULL THEN NULL
       WHEN a = 0 AND b <= 0 THEN NULL
       WHEN a < 0 AND b <> floor(b) THEN NULL
       ELSE power(a, b) END;

-- Dates: serial <-> DATE. Valid from 1 March 1900 (serial 61); Excel's phantom 29 Feb 1900 is not modelled.
CREATE OR REPLACE MACRO xl_serial_ok(x) AS CASE WHEN x BETWEEN 0 AND 2958465 THEN x ELSE NULL END;  -- Excel: #NUM! outside
CREATE OR REPLACE MACRO xl_to_date(s) AS
  CASE WHEN s IS NULL OR s < 0 OR s > 2958465 THEN NULL ELSE DATE '1899-12-30' + CAST(floor(s) AS INTEGER) END;
CREATE OR REPLACE MACRO xl_from_date(d) AS CAST(date_diff('day', DATE '1899-12-30', CAST(d AS DATE)) AS DOUBLE);
CREATE OR REPLACE MACRO xl_year(s) AS CASE WHEN s IS NULL THEN NULL ELSE CAST(year(xl_to_date(s)) AS DOUBLE) END;
CREATE OR REPLACE MACRO xl_month(s) AS CASE WHEN s IS NULL THEN NULL ELSE CAST(month(xl_to_date(s)) AS DOUBLE) END;
CREATE OR REPLACE MACRO xl_day(s) AS CASE WHEN s IS NULL THEN NULL ELSE CAST(day(xl_to_date(s)) AS DOUBLE) END;
CREATE OR REPLACE MACRO xl_date_year(y, m) AS floor(y) + CASE WHEN floor(y) < 1900 THEN 1900 ELSE 0 END + floor((floor(m) - 1) / 12);
CREATE OR REPLACE MACRO xl_date(y, m, d) AS
  CASE WHEN y IS NULL OR m IS NULL OR d IS NULL THEN NULL
       WHEN xl_date_year(y, m) NOT BETWEEN 1 AND 9999 THEN NULL  -- Excel: #NUM!
       ELSE xl_serial_ok(xl_from_date(make_date(CAST(xl_date_year(y, m) AS INTEGER), CAST(((CAST(floor(m) AS BIGINT) - 1) % 12 + 12) % 12 + 1 AS INTEGER), 1)) + floor(d) - 1) END;
CREATE OR REPLACE MACRO xl_month_shift_ok(s, k) AS
  xl_to_date(s) IS NOT NULL AND (year(xl_to_date(s)) * 12 + month(xl_to_date(s)) - 1 + floor(k)) BETWEEN 1900 * 12 + 2 AND 9999 * 12 + 11;
CREATE OR REPLACE MACRO xl_edate(s, k) AS
  CASE WHEN s IS NULL OR k IS NULL OR NOT xl_month_shift_ok(s, k) THEN NULL
       ELSE xl_from_date(CAST(xl_to_date(s) + to_months(CAST(floor(k) AS INTEGER)) AS DATE)) END;
CREATE OR REPLACE MACRO xl_eomonth(s, k) AS
  CASE WHEN s IS NULL OR k IS NULL OR NOT xl_month_shift_ok(s, k) THEN NULL
       ELSE xl_from_date(last_day(CAST(xl_to_date(s) + to_months(CAST(floor(k) AS INTEGER)) AS DATE))) END;
-- Excel's weekday rule: serial 0 is a Saturday, 1 a Sunday. WEEKDAY(s) with return type 1 (Sunday = 1).
CREATE OR REPLACE MACRO xl_dow(s) AS ((CAST(floor(s) AS BIGINT) % 7) + 7) % 7;          -- 0 Sat, 1 Sun, 2 Mon .. 6 Fri
CREATE OR REPLACE MACRO xl_weekday(s) AS
  CASE WHEN s IS NULL OR s < 0 OR s > 2958465 THEN NULL ELSE CAST(((xl_dow(s) - 1 + 7) % 7) + 1 AS DOUBLE) END;
CREATE OR REPLACE MACRO xl_is_weekend(s) AS xl_dow(s) IN (0, 1);
-- NETWORKDAYS without a holiday list: whole weeks times five plus the leftover days that are not weekends.
CREATE OR REPLACE MACRO xl_span_days(s, e) AS CAST(abs(floor(e) - floor(s)) AS BIGINT) + 1;
CREATE OR REPLACE MACRO xl_networkdays(s, e) AS
  CASE WHEN s IS NULL OR e IS NULL OR s < 0 OR s > 2958465 OR e < 0 OR e > 2958465 THEN NULL ELSE
  CAST(CASE WHEN floor(s) <= floor(e) THEN 1 ELSE -1 END AS DOUBLE) * (
    CAST((xl_span_days(s, e) // 7) * 5 AS DOUBLE)
    + (CASE WHEN xl_span_days(s, e) % 7 > 0 AND NOT xl_is_weekend(greatest(floor(s), floor(e)) - 0) THEN 1 ELSE 0 END)
    + (CASE WHEN xl_span_days(s, e) % 7 > 1 AND NOT xl_is_weekend(greatest(floor(s), floor(e)) - 1) THEN 1 ELSE 0 END)
    + (CASE WHEN xl_span_days(s, e) % 7 > 2 AND NOT xl_is_weekend(greatest(floor(s), floor(e)) - 2) THEN 1 ELSE 0 END)
    + (CASE WHEN xl_span_days(s, e) % 7 > 3 AND NOT xl_is_weekend(greatest(floor(s), floor(e)) - 3) THEN 1 ELSE 0 END)
    + (CASE WHEN xl_span_days(s, e) % 7 > 4 AND NOT xl_is_weekend(greatest(floor(s), floor(e)) - 4) THEN 1 ELSE 0 END)
    + (CASE WHEN xl_span_days(s, e) % 7 > 5 AND NOT xl_is_weekend(greatest(floor(s), floor(e)) - 5) THEN 1 ELSE 0 END)
  ) END;
-- WORKDAY without a holiday list. A weekend start counts from the Friday before (forwards) or the Monday after (backwards).
CREATE OR REPLACE MACRO xl_workday_start(s, n) AS
  CASE WHEN n > 0 THEN floor(s) - CASE xl_dow(s) WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 0 END
       ELSE floor(s) + CASE xl_dow(s) WHEN 0 THEN 2 WHEN 1 THEN 1 ELSE 0 END END;
CREATE OR REPLACE MACRO xl_workday(s, n) AS
  CASE WHEN s IS NULL OR n IS NULL OR s < 0 OR s > 2958465 THEN NULL
       WHEN floor(n) = 0 THEN floor(s)
       WHEN n > 0 THEN xl_serial_ok(xl_workday_start(s, n) + (CAST(floor(n) AS BIGINT) // 5) * 7 + (CAST(floor(n) AS BIGINT) % 5)
            + CASE WHEN ((xl_dow(xl_workday_start(s, n)) - 2 + 7) % 7) + (CAST(floor(n) AS BIGINT) % 5) > 4 THEN 2 ELSE 0 END)
       ELSE xl_serial_ok(xl_workday_start(s, n) - (CAST(floor(-n) AS BIGINT) // 5) * 7 - (CAST(floor(-n) AS BIGINT) % 5)
            - CASE WHEN ((xl_dow(xl_workday_start(s, n)) - 2 + 7) % 7) - (CAST(floor(-n) AS BIGINT) % 5) < 0 THEN 2 ELSE 0 END) END;
-- DATEDIF units.
CREATE OR REPLACE MACRO xl_datedif_m(s, e) AS
  CASE WHEN s IS NULL OR e IS NULL OR s > e THEN NULL ELSE
  CAST((year(xl_to_date(e)) - year(xl_to_date(s))) * 12 + (month(xl_to_date(e)) - month(xl_to_date(s)))
       - CASE WHEN day(xl_to_date(e)) < day(xl_to_date(s)) THEN 1 ELSE 0 END AS DOUBLE) END;
CREATE OR REPLACE MACRO xl_datedif_y(s, e) AS floor(xl_datedif_m(s, e) / 12);
CREATE OR REPLACE MACRO xl_datedif_ym(s, e) AS xl_datedif_m(s, e) - 12 * floor(xl_datedif_m(s, e) / 12);
CREATE OR REPLACE MACRO xl_datedif_d(s, e) AS CASE WHEN s IS NULL OR e IS NULL OR s > e THEN NULL ELSE floor(e) - floor(s) END;
CREATE OR REPLACE MACRO xl_datedif_md(s, e) AS
  CASE WHEN s IS NULL OR e IS NULL OR s > e THEN NULL
       WHEN day(xl_to_date(e)) >= day(xl_to_date(s)) THEN CAST(day(xl_to_date(e)) - day(xl_to_date(s)) AS DOUBLE)
       ELSE CAST(day(xl_to_date(e)) - day(xl_to_date(s)) + day(last_day(CAST(xl_to_date(e) - to_months(1) AS DATE))) AS DOUBLE) END;
-- YEARFRAC. Basis 0 (US 30/360), 1 (actual/actual with Excel's year-length rule), 2 (actual/360), 3 (actual/365), 4 (European 30/360).
CREATE OR REPLACE MACRO xl_is_leap(y) AS (y % 4 = 0 AND (y % 100 <> 0 OR y % 400 = 0));
CREATE OR REPLACE MACRO xl_days360_us(d1, d2) AS
  (year(d2) - year(d1)) * 360 + (month(d2) - month(d1)) * 30
  + (CASE WHEN day(d2) = 31 AND (day(d1) >= 30 OR (month(d1) = 2 AND d1 = last_day(d1))) THEN 30
          WHEN month(d2) = 2 AND d2 = last_day(d2) AND month(d1) = 2 AND d1 = last_day(d1) THEN 30
          ELSE day(d2) END)
  - (CASE WHEN day(d1) = 31 OR (month(d1) = 2 AND d1 = last_day(d1)) THEN 30 ELSE day(d1) END);
CREATE OR REPLACE MACRO xl_days360_eu(d1, d2) AS
  (year(d2) - year(d1)) * 360 + (month(d2) - month(d1)) * 30 + least(day(d2), 30) - least(day(d1), 30);
CREATE OR REPLACE MACRO xl_yearfrac(s, e, basis) AS
  CASE WHEN s IS NULL OR e IS NULL OR basis IS NULL THEN NULL
       WHEN floor(basis) = 0 THEN abs(xl_days360_us(xl_to_date(least(s, e)), xl_to_date(greatest(s, e)))) / 360.0
       WHEN floor(basis) = 4 THEN abs(xl_days360_eu(xl_to_date(least(s, e)), xl_to_date(greatest(s, e)))) / 360.0
       WHEN floor(basis) = 2 THEN abs(floor(e) - floor(s)) / 360.0
       WHEN floor(basis) = 3 THEN abs(floor(e) - floor(s)) / 365.0
       WHEN floor(basis) = 1 THEN abs(floor(e) - floor(s)) / (
         CASE
           WHEN year(xl_to_date(s)) = year(xl_to_date(e)) THEN CASE WHEN xl_is_leap(year(xl_to_date(s))) THEN 366.0 ELSE 365.0 END
           WHEN year(xl_to_date(greatest(s, e))) = year(xl_to_date(least(s, e))) + 1
                AND (month(xl_to_date(greatest(s, e))), day(xl_to_date(greatest(s, e)))) <= (month(xl_to_date(least(s, e))), day(xl_to_date(least(s, e))))
             THEN CASE WHEN (xl_is_leap(year(xl_to_date(least(s, e)))) AND xl_to_date(least(s, e)) <= make_date(year(xl_to_date(least(s, e))), 2, 29))
                        OR (xl_is_leap(year(xl_to_date(greatest(s, e)))) AND xl_to_date(greatest(s, e)) >= make_date(year(xl_to_date(greatest(s, e))), 2, 29))
                       THEN 366.0 ELSE 365.0 END
           ELSE (SELECT avg(CASE WHEN xl_is_leap(y) THEN 366.0 ELSE 365.0 END)
                 FROM range(year(xl_to_date(least(s, e))), year(xl_to_date(greatest(s, e))) + 1) t(y))
         END)
       ELSE NULL END;

-- Finance. Money paid out is negative; ``kind`` is Excel's type flag (any non-zero value means start of period).
CREATE OR REPLACE MACRO xl_type(kind) AS CASE WHEN coalesce(kind, 0) <> 0 THEN 1.0 ELSE 0.0 END;
CREATE OR REPLACE MACRO xl_pmt(rate, nper, pv, fv, kind) AS
  CASE WHEN rate IS NULL OR nper IS NULL OR pv IS NULL OR nper = 0 THEN NULL
       WHEN rate = 0 THEN -(pv + coalesce(fv, 0)) / nper
       ELSE -(pv * power(1 + rate, nper) + coalesce(fv, 0)) * rate / NULLIF((1 + rate * xl_type(kind)) * (power(1 + rate, nper) - 1), 0) END;
CREATE OR REPLACE MACRO xl_fv(rate, nper, pmt, pv, kind) AS
  CASE WHEN rate IS NULL OR nper IS NULL OR pmt IS NULL THEN NULL
       WHEN rate = 0 THEN -(coalesce(pv, 0) + pmt * nper)
       ELSE -(coalesce(pv, 0) * power(1 + rate, nper) + pmt * (1 + rate * xl_type(kind)) * (power(1 + rate, nper) - 1) / rate) END;
CREATE OR REPLACE MACRO xl_pv(rate, nper, pmt, fv, kind) AS
  CASE WHEN rate IS NULL OR nper IS NULL OR pmt IS NULL THEN NULL
       WHEN rate = 0 THEN -(pmt * nper + coalesce(fv, 0))
       ELSE -(pmt * (1 + rate * xl_type(kind)) * (power(1 + rate, nper) - 1) / rate + coalesce(fv, 0)) / NULLIF(power(1 + rate, nper), 0) END;
CREATE OR REPLACE MACRO xl_ipmt(rate, per, nper, pv, fv, kind) AS
  CASE WHEN rate IS NULL OR per IS NULL OR nper IS NULL OR pv IS NULL OR per < 1 OR per > nper THEN NULL
       WHEN xl_type(kind) = 0 THEN xl_fv(rate, per - 1, xl_pmt(rate, nper, pv, fv, kind), pv, 0) * rate
       WHEN per = 1 THEN 0.0
       ELSE (xl_fv(rate, per - 2, xl_pmt(rate, nper, pv, fv, kind), pv, 1) - xl_pmt(rate, nper, pv, fv, kind)) * rate END;
CREATE OR REPLACE MACRO xl_ppmt(rate, per, nper, pv, fv, kind) AS
  xl_pmt(rate, nper, pv, fv, kind) - xl_ipmt(rate, per, nper, pv, fv, kind);
CREATE OR REPLACE MACRO xl_nper(rate, pmt, pv, fv, kind) AS
  CASE WHEN rate IS NULL OR pmt IS NULL OR pv IS NULL THEN NULL
       WHEN rate = 0 THEN CASE WHEN pmt = 0 THEN NULL ELSE -(pv + coalesce(fv, 0)) / pmt END
       ELSE xl_ln((pmt * (1 + rate * xl_type(kind)) - coalesce(fv, 0) * rate) / NULLIF(pmt * (1 + rate * xl_type(kind)) + pv * rate, 0)) / NULLIF(xl_ln(1 + rate), 0) END;