CREATE TABLE program (
  id    SMALLINT PRIMARY KEY,
  price INT,
  name  VARCHAR(24)
--   qualification INT
);

INSERT INTO program (id, name, price) VALUES
  (11, 'Easy Start', 10000),
  (12, 'Easy Finish', NULL),
  (13, 'Medium Start', 30000),
  (14, 'Medium Finish', NULL),
  (15, 'Business Start', 90000),
  (16, 'Business Finish', NULL),
  (17, 'Premium Start', 270000),
  (18, 'Premium Finish', NULL);

CREATE OR REPLACE VIEW program_view AS
  WITH t AS (
      SELECT
        *,
        id % 2 = 0 AS finish,
        CASE WHEN id % 2 = 0
          THEN 3
        ELSE 4 END AS level
      FROM program)
  SELECT
    *,
    power(2, t.level) AS width
  FROM t;

CREATE OR REPLACE VIEW refund AS
  SELECT
    p2.id,
    p2.price,
    p2.name,
    CASE WHEN p2.price IS NULL
      THEN
        p1.price * 4 - coalesce(p3.price, 0)
    ELSE p2.price END                AS refund,
    coalesce(p2.price, p1.price * 4) AS cell
  FROM program p2
    LEFT JOIN program p1 ON p1.id = p2.id - 1
    LEFT JOIN program p3 ON p3.id = p2.id + 1;

------------------ node -------------------

CREATE TABLE node (
  id      SERIAL PRIMARY KEY,
  "user"  INT REFERENCES "user" (id)
  ON UPDATE CASCADE ON DELETE CASCADE,
  program INT       NOT NULL REFERENCES program (id),
  invest  INT REFERENCES node (id),
  parent  INT REFERENCES node (id),
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed TIMESTAMP,
  qualification INT REFERENCES node (id),
  time    TIMESTAMP
);

ALTER SEQUENCE node_id_seq RESTART 100;

----------------- reward ------------------

CREATE TABLE reward (
  level   SMALLINT PRIMARY KEY,
  percent FLOAT NOT NULL
);

INSERT INTO reward VALUES
  (1, 0.40),
  (2, 0.07),
  (3, 0.03);

----------------- matrix ------------------

CREATE VIEW matrix AS
  WITH RECURSIVE r(program, root, id, parent, level) AS (
    SELECT
      program,
      id AS root,
      id,
      parent,
      0  AS "level"
    FROM node
    UNION
    SELECT
      n.program,
      r.root,
      n.id,
      n.parent,
      r.level + 1 AS "level"
    FROM node n
      JOIN r ON r.id = n.parent
    WHERE r."level" < 50 -- recursion limit
  )
  SELECT
    r.*,
    row_number()
    OVER (
      PARTITION BY root, r."level"
      ORDER BY r.id ) AS "offset"
  FROM r;

CREATE OR REPLACE VIEW matrix_node AS
      SELECT
        root     AS id,
        program,
        width,
        count(*) AS count
      FROM matrix m
        JOIN program_view p ON m.program = p.id AND m.level = p.level
      GROUP BY root, program, width;

CREATE OR REPLACE VIEW node_qualification AS
  SELECT
    *,
    (SELECT count(q.*) FROM node q WHERE q.qualification = n.id) as qualified
  FROM node n;