--         File: crack_the_code.sql
--      Version: 1.0
-- Last Changed: 2020-04-15
--           by: Damir;  https://www.damirsystems.com
--      Project: Crack The Code
--  Description: Fun challenge from Decision Management Community
--               https://dmcommunity.org/challenge/challenge-sep-2019/
--
--           DB: PostgreSQL


-- Composite types for pattern and match requirements
--
CREATE TYPE pattern AS (
     a integer
   , b integer
   , c integer
);

CREATE TYPE match AS (
     digits    integer  -- number of digits to match
   , positions integer  -- number of positions to match
);

-- Function returns True if a row matches pattern
-- in required number of digits and positions.
--
CREATE FUNCTION fn (rw pattern, pt pattern, mc match)
RETURNS boolean
AS $BODY$
DECLARE
   rs boolean := false; -- result
   md integer := 0;     -- matching digits
   mp integer := 0;     -- matching positions
BEGIN
   IF rw.a = pt.a THEN md := md + 1; mp := mp + 1; END IF;
   IF rw.b = pt.a THEN md := md + 1; END IF;
   IF rw.c = pt.a THEN md := md + 1; END IF;

   IF rw.a = pt.b THEN md := md + 1; END IF;
   IF rw.b = pt.b THEN md := md + 1; mp := mp + 1; END IF;
   IF rw.c = pt.b THEN md := md + 1; END IF;

   IF rw.a = pt.c THEN md := md + 1; END IF;
   IF rw.b = pt.c THEN md := md + 1; END IF;
   IF rw.c = pt.c THEN md := md + 1; mp := mp + 1; END IF;

   md := least (md, 3);

   IF (md = mc.digits) AND (mp = mc.positions)
   THEN
      rs := true;
   END IF ;

   return rs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;


-- The query
--
WITH
dgt AS ( -- single column of integers (0..9)
   SELECT d
   FROM (
      values (0), (1), (2), (3), (4),
             (5), (6), (7), (8), (9)
   ) AS x(d)
),
possible AS ( -- generate 10^3 combinations
   SELECT a.d AS A
        , b.d AS B
        , c.d AS C
   FROM       dgt AS a
   CROSS JOIN dgt AS b
   CROSS JOIN dgt AS c
)
SELECT A, B, C
FROM possible
      -- (7,3,8) nothing is correct
WHERE fn((A,B,C),(7,3,8),(0,0))

      -- (6,8,2) - exactly one number is correct
      -- and is in the correct position.
  AND fn((A,B,C),(6,8,2),(1,1))

      -- (6,4,5) exactly one number is correct,
      -- but in the wrong position.
  AND fn((A,B,C),(6,4,5),(1,0))

      -- (7,8,0) exactly one number is correct,
      -- but in the wrong position.
  AND fn((A,B,C),(7,8,0),(1,0))

      -- (2,0,6) - exactly two numbers are correct,
      -- but in the wrong positions.
  AND fn((A,B,C),(2,0,6),(2,0))
;


--  Result
--  +---+---+---+
--  | A | B | C |
--  +---+---+---+
--  | 0 | 5 | 2 |
--  +---+---+---+