clear_functions()

cur.execute("""
SET @base_value = 2,
    @extracting_column = 2,
    @number_of_columns = 40,
    @number_of_layers = 5,
    @active_side = 2;
""")

cur.execute(f"""
CREATE FUNCTION numbering_formula
(
    layer INT,
    side INT,
    column_ INT
)
RETURNS int
BEGIN
    DECLARE loop_ INT;
    DECLARE a_upper INT;
    DECLARE ch0 INT;
    DECLARE ch1 INT;
    DECLARE extra INT;
    DECLARE a_lower INT;
    DECLARE cdb0 INT;
    DECLARE cde0 INT;
    DECLARE cdb1 INT;
    DECLARE cde1 INT;

    IF NOT (1 <= layer AND layer <= @number_of_layers AND (side = 1 OR side = 2) AND 1 <= column_ AND column_ <= @number_of_columns AND column_ != @extracting_column) THEN
        RETURN -1;
    END IF;

    SET side = CASE WHEN @active_side = 1 THEN side else flip_side(side) END;
    SET loop_ = CASE WHEN abs(column_ - @extracting_column) <= @base_value THEN layer - 1 ELSE layer + abs(column_ - @extracting_column) - @base_value - 1 END;

    IF abs(column_ - @extracting_column) <= @base_value THEN
        SET a_upper = lower_area(loop_ + 1);
        SET ch0 = circ_horz(loop_, @extracting_column);
        SET ch1 = circ_horz(loop_, flip_column(@extracting_column));
        SET extra = (CASE WHEN side = 1 THEN 0 ELSE ch0 + ch1 END) + (CASE WHEN column_ > @extracting_column THEN 0 ELSE ch0 END);
        RETURN a_upper - 2*(ch0 + ch1) + extra + abs(column_ - @extracting_column);
    ELSE
        SET a_lower = lower_area(loop_);
        SET cdb0 = circ_diag_before(layer, side, column_, loop_, @extracting_column);
        SET cde0 = circ_diag_exists(layer, side, column_, loop_, @extracting_column);
        SET cdb1 = circ_diag_before(layer, side, 2*@extracting_column - column_, loop_, @extracting_column);
        SET cde1 = circ_diag_exists(layer, side, 2*@extracting_column - column_, loop_, @extracting_column);
        RETURN a_lower + 2*(cdb0 + cdb1) + (CASE WHEN side = 1 THEN 0 ELSE cde0 + cde1 END) + (CASE WHEN column_ > @extracting_column THEN 0 ELSE cde1 END) + 1;
    END IF;
END
""")

cur.execute("""
CREATE FUNCTION flip_side
(
    side INT
)
RETURNS INT
BEGIN
    RETURN CASE WHEN side = 1 THEN 2 ELSE 1 END;
END
""")

cur.execute("""
CREATE FUNCTION flip_column
(
    column_ INT
)
RETURNS INT
BEGIN
    RETURN @number_of_columns - column_ + 1;
END
""")

cur.execute("""
CREATE FUNCTION lower_area
(
    loop_ INT
)
RETURNS INT
BEGIN
    RETURN 2 * (area_diag(loop_, @extracting_column) +
                area_diag(loop_, flip_column(@extracting_column)) +
                area_horz(loop_, @extracting_column) +
                area_horz(loop_, flip_column(@extracting_column)));
END
""")

cur.execute("""
CREATE FUNCTION area_diag
(
    loop_ INT,
    extracting_column INT
)
RETURNS INT
BEGIN
    IF extracting_column + @base_value > @number_of_columns THEN
        RETURN 0;
    END IF;

    RETURN bin2(loop_) - bin2(loop_ + extracting_column + @base_value - @number_of_columns) - bin2(loop_ - @number_of_layers) + bin2(loop_ + extracting_column + @base_value - @number_of_columns - @number_of_layers);
END
""")

cur.execute("""
CREATE FUNCTION bin2
(
    n INT
)
RETURNS INT
BEGIN
    RETURN CASE WHEN n > 0 THEN n*(n-1) DIV 2 ELSE 0 END;
END
""")

cur.execute("""
CREATE FUNCTION min_
(
    a INT,
    b INT
)
RETURNS INT
BEGIN
    RETURN CASE WHEN a < b THEN a ELSE b END;
END
""")

cur.execute("""
CREATE FUNCTION max_
(
    a INT,
    b INT
)
RETURNS INT
BEGIN
    RETURN CASE WHEN a < b THEN b ELSE a END;
END
""")

cur.execute("""
CREATE FUNCTION area_horz
(
    loop_ INT,
    extracting_column INT
)
RETURNS INT
BEGIN
    RETURN min_(@base_value, @number_of_columns - extracting_column)*min_(loop_, @number_of_layers);
END
""")

cur.execute("""
CREATE FUNCTION circ_horz
(
    loop_ INT,
    extracting_column INT
)
RETURNS INT
BEGIN
    RETURN CASE WHEN loop_ < @number_of_layers THEN min_(@base_value, @number_of_columns - extracting_column) ELSE 0 END;
END
""")

cur.execute("""
CREATE FUNCTION circ_diag_before
(
    layer INT,
    side INT,
    column_ INT,
    loop_ INT,
    extracting_column INT
)
RETURNS INT
BEGIN
    IF column_ < extracting_column THEN
        SET column_ = flip_column(column_);
        SET extracting_column = flip_column(extracting_column);
    END IF;

    RETURN max_(0, min_(layer - 1, @number_of_columns - column_));
END
""")

cur.execute("""
CREATE FUNCTION circ_diag_exists
(
    layer INT,
    side INT,
    column_ INT,
    loop_ INT,
    extracting_column INT
)
RETURNS INT
BEGIN
    IF column_ < extracting_column THEN
        SET column_ = flip_column(column_);
        SET extracting_column = flip_column(extracting_column);
    END IF;

    RETURN cast(column_ <= @number_of_columns AS INT);
END
""")