-- predict_glm() MVP stage 2: binomial/logit models over numeric predictors
-- return probabilities (R's predict(..., type = "response")).
-- Reference values in scripts/parity_reference.R.
CREATE TEMP TABLE t_binomial (
    y integer,
    x double precision
);
INSERT INTO t_binomial VALUES
    (0, 0.1), (0, 0.4), (1, 0.8), (0, 1.0),
    (1, 1.2), (0, 1.5), (1, 1.8), (1, 2.0),
    (0, 2.2), (1, 2.5), (1, 2.8), (0, 3.0);
CREATE TEMP TABLE t_new_binomial (
    id integer,
    x  double precision
);
INSERT INTO t_new_binomial VALUES (1, 0.5), (2, 1.5), (3, 2.5);
CREATE TEMP TABLE t_model_b AS
SELECT *
FROM fbsql.fit_glm(
    relation => $$ SELECT y, x FROM t_binomial $$,
    formula  => 'y ~ x',
    family   => 'binomial');
SELECT id, x, round(y_predicted::numeric, 4) AS y_predicted
FROM fbsql.predict_glm(
    relation => $$ SELECT id, x FROM t_new_binomial $$,
    model    => $$ SELECT * FROM t_model_b $$
) AS p(id integer, x double precision, y_predicted double precision)
ORDER BY id;
 id |  x  | y_predicted 
----+-----+-------------
  1 | 0.5 |      0.3315
  2 | 1.5 |      0.4826
  3 | 2.5 |      0.6370
(3 rows)

-- A boolean response (as in the running example's churn_flag) fits with
-- data_classes y = "logical"; prediction must still work and give the same
-- probabilities, since the response column plays no role in scoring.
CREATE TEMP TABLE t_model_bool AS
SELECT *
FROM fbsql.fit_glm(
    relation => $$ SELECT y::boolean AS y, x FROM t_binomial $$,
    formula  => 'y ~ x',
    family   => 'binomial');
SELECT id, x, round(y_predicted::numeric, 4) AS y_predicted
FROM fbsql.predict_glm(
    relation => $$ SELECT id, x FROM t_new_binomial $$,
    model    => $$ SELECT * FROM t_model_bool $$
) AS p(id integer, x double precision, y_predicted double precision)
ORDER BY id;
 id |  x  | y_predicted 
----+-----+-------------
  1 | 0.5 |      0.3315
  2 | 1.5 |      0.4826
  3 | 2.5 |      0.6370
(3 rows)