--         File: queens_3_data_pg.sql
--      Version: 1.2
-- Last Changed: 2017-12-25
--           by: Damir;  https://www.damirsystems.com
--      Project: Eight Queens
--  Description: Part 3; Relation values (populate tables)
--           DB: PostgreSQL


-- [p_01] The column labelled (CL) with
-- the assigned column number (CL_NO) exists.
--
WITH
q_00 as ( 
    SELECT unnest(enum_range(null::col_lbl)) as CL
)
INSERT INTO a_col (CL, CL_NO)

SELECT
      CL
    , row_number() over (ORDER BY CL asc) as CL_NO
FROM q_00
;



-- [p_02] The row labelled (RW) with
-- the assigned row number (RW_NO) exists.
--
WITH
q_00 as ( 
    SELECT unnest(enum_range(null::row_lbl)) as RW
)
INSERT INTO a_row (RW, RW_NO)

SELECT
      RW
    , row_number() over (ORDER BY RW asc) as RW_NO
FROM q_00
;



-- [p_03] The board square named (SQUARE) is located
-- in the row labelled (RW) and the column labelled (CL).
--
INSERT INTO board (CL, RW, SQUARE)

SELECT CL
     , RW
     , cast(CL::text || RW::text as square_name) as SQUARE
FROM       a_col
CROSS JOIN a_row
;



-- [p_04] The line identified by the starting square (ANCHOR)
-- and the direction of movement (MOV_DIR) exists.
--
INSERT INTO line (ANCHOR, MOV_DIR)

-- rows
SELECT SQUARE             as ANCHOR
     , 'RGHT'::mov_dir    as MOV_DIR
FROM board
WHERE CL = 'a'::col_lbl
  and RW BETWEEN '1'::row_lbl and '8'::row_lbl 

UNION

-- columns
SELECT SQUARE             as ANCHOR
     , 'UP'::mov_dir      as MOV_DIR
FROM board
WHERE RW = '1'::row_lbl
  and CL BETWEEN 'a'::col_lbl and 'h'::col_lbl 

UNION

-- diagonals a1:h8 to a7:b8
SELECT SQUARE             as ANCHOR
     , 'RGHT-UP'::mov_dir as MOV_DIR
FROM board
WHERE CL = 'a'::col_lbl
  and RW BETWEEN '1'::row_lbl and '7'::row_lbl

UNION

-- diagonals b1:h7 to g1:h2
SELECT SQUARE             as ANCHOR
     , 'RGHT-UP'::mov_dir as MOV_DIR
FROM board
WHERE RW = '1'::row_lbl 
  and CL BETWEEN 'b'::col_lbl and 'g'::col_lbl

UNION

-- diagonals a2:b1 to a8:h1
SELECT SQUARE             as ANCHOR
     , 'RGHT-DN'::mov_dir as MOV_DIR
FROM board
WHERE CL = 'a'::col_lbl
  and RW BETWEEN '2'::row_lbl and '8'::row_lbl

UNION

-- diagonals b8:h2 to g8:h8
SELECT SQUARE             as ANCHOR
     , 'RGHT-DN'::mov_dir as MOV_DIR
FROM board
WHERE RW = '8'::row_lbl 
  and CL BETWEEN 'b'::col_lbl and 'g'::col_lbl
;




-- [p_05] The square named (SQUARE) is located on the line
-- identified by the starting square (ANCHOR) and 
-- the direction of movement (MOV_DIR).
--
WITH
q_00 as (
    SELECT generate_series(0,7) as off
),
q_01 as (
    SELECT e.ANCHOR
         , e.MOV_DIR
         , w.off
     
    , CASE WHEN e.MOV_DIR = 'RGHT-UP' THEN b.CL_NO + w.off
           WHEN e.MOV_DIR = 'RGHT-DN' THEN b.CL_NO + w.off
           WHEN e.MOV_DIR = 'RGHT'    THEN b.CL_NO + w.off
           WHEN e.MOV_DIR = 'UP'      THEN b.CL_NO
      END as n_CL_NO

    , CASE WHEN e.MOV_DIR = 'RGHT-UP' THEN b.RW_NO + w.off
           WHEN e.MOV_DIR = 'RGHT-DN' THEN b.RW_NO - w.off
           WHEN e.MOV_DIR = 'RGHT'    THEN b.RW_NO
           WHEN e.MOV_DIR = 'UP'      THEN b.RW_NO + w.off
      END as n_RW_NO

    FROM  line        as e
    JOIN  board1      as b ON b.SQUARE = e.ANCHOR
    CROSS JOIN q_00   as w
),
q_02 as (
    SELECT q.ANCHOR
         , q.MOV_DIR
         , t.SQUARE
    FROM q_01   as q
    JOIN board1 as t ON t.CL_NO = q.n_CL_NO
                    and t.RW_NO = q.n_RW_NO
)
INSERT INTO square_line (SQUARE, ANCHOR, MOV_DIR)

SELECT SQUARE, ANCHOR, MOV_DIR 
FROM q_02
ORDER BY SQUARE, ANCHOR, MOV_DIR 
;