--Setup
DROP TABLE IF EXISTS board;
CREATE TABLE board (p CHAR, r INTEGER, c INTEGER);
INSERT INTO board VALUES('.', 0, 0),('.', 0, 1),('.', 0, 2),('.', 1, 0),('.', 1, 1),('.', 1, 2),('.', 2, 0),('.', 2, 1),('.', 2, 2);


-- Use a trigger for move events
DROP TRIGGER IF EXISTS after_moved;
CREATE TRIGGER after_moved after UPDATE ON board FOR each ROW WHEN NEW.p <> '.' AND NEW.p <> 'O'
BEGIN

    -- Verify move is valid
    SELECT 
        CASE        
            WHEN (SELECT v FROM msg) LIKE '%Wins!' THEN raise(ABORT, 'The game is already over.')               
            WHEN (SELECT OLD.p FROM board WHERE rowid = rid) <> '.' THEN raise(ABORT, 'That position has already been taken.  Please choose an available position.')            
            WHEN NEW.p <> 'X' THEN raise(ABORT, 'Please place an ''X''')
        END 
    FROM (
        SELECT rowid rid FROM board
        WHERE p = NEW.p
        EXCEPT 
        SELECT p FROM board WHERE p = OLD.p
    );


    -- Check for game over
    UPDATE msg SET v = (
        SELECT 
            CASE                                    
                WHEN MAX(num) >= 3 THEN 'X Wins!'
                WHEN (SELECT COUNT(*) FROM board WHERE p = '.') = 0 THEN 'Cat Wins!'
                ELSE 'Move made'
            END 
        FROM ( -- Is Game Over
            SELECT COUNT(*) num FROM board WHERE p = 'X' GROUP BY r UNION -- Horz
            SELECT COUNT(*) num FROM board WHERE p = 'X' GROUP BY c UNION -- Vert
            SELECT COUNT(*) num FROM board WHERE p = 'X' AND r = c UNION -- Diag TL->BR
            SELECT COUNT(*) num FROM board WHERE p = 'X' AND (2-r) = c -- Diag TR->BL
        )
    );


    --Have computer player make a random move
    UPDATE board SET p = 'O' 
    WHERE rowid = (SELECT rid FROM (SELECT MAX(rnd),rid FROM (SELECT rowid rid, random() rnd FROM board WHERE p = '.')))
    AND (SELECT v FROM msg) NOT LIKE '%Wins!';
    --NOTE: SQLite doesn't allow update order by in triggers, otherwise we could just use this beautiful line:
    -- update board set p = 'O' where p = '.' order by random() limit 1;

    --Check to see if the computer player won
    UPDATE msg SET v = (
        SELECT 
            CASE                                    
                WHEN MAX(num) >= 3 THEN 'O Wins!'   
                ELSE v
            END 
        FROM ( -- Is Game Over
            SELECT COUNT(*) num FROM board WHERE p = 'O' GROUP BY r UNION -- Horz
            SELECT COUNT(*) num FROM board WHERE p = 'O' GROUP BY c UNION -- Vert
            SELECT COUNT(*) num FROM board WHERE p = 'O' AND r = c UNION -- Diag TL->BR
            SELECT COUNT(*) num FROM board WHERE p = 'O' AND (2-r) = c -- Diag TR->BL
        )
    );


END;

-- UI to display the logical board as a grid
DROP VIEW IF EXISTS ui;
CREATE VIEW ui AS
SELECT CASE WHEN p = '.' THEN col0.rowid ELSE p END c0, c1, c2
FROM board AS col0 
JOIN (SELECT CASE WHEN p = '.' THEN board.rowid ELSE p END c1, r FROM board WHERE c = 1) AS col1 ON col0.r = col1.r
JOIN (SELECT CASE WHEN p = '.' THEN board.rowid ELSE p END  c2, r FROM board WHERE c = 2) AS col2 ON col0.r = col2.r
WHERE c = 0;

DROP TABLE IF EXISTS msg;
CREATE TABLE msg (v text);
INSERT INTO msg VALUES('');

-- Readme
SELECT * FROM ui;
.print 
.print '