sqlcmd="psql postgresql://airetro:madden92@zorak.brak/airetro -qAt -F '|' -c"
--sqlcmd = "sqlite3 madden92.db"

function waitframes(frames)

    i = frames
    while i > 0 do

        i = i - 1

        emu.frameadvance()
    end
end

function split(s, delimiter)
    result = {};
    for match in (s..delimiter):gmatch("(.-)"..delimiter) do
        table.insert(result, match);
    end
    return result;
end

function all_trim(s)
return s:match"^%s*(.*)":match"(.-)%s*$"
end

function getmatches()

    --handle = io.popen("date \"+%Y-%m-%d\"")
    --local result = handle:read("*a")
    --local date = all_trim(result)
    --handle:close()

    sqlcmd="psql postgresql://airetro:madden92@zorak.brak/airetro -qAt -F '|' -c"
    handle = io.popen(sqlcmd.." \"SELECT COUNT(match_id) FROM airetro_matches WHERE is_finished=0 AND game_id=1 AND scheduled_date<=NOW() AND is_started=0;\"")
    local result = handle:read("*a")
    local matches = tonumber(result)
    handle:close()

    return matches
end


::madden92::

console.writeline(emu.getsystemid())
console.writeline(gameinfo.getromname())


matches = tonumber(getmatches())

handle = io.popen("date \"+%Y-%m-%d %H:%M\"")
local result = handle:read("*a")
date = all_trim(result)
handle:close()

handle = io.popen(sqlcmd.." \"SELECT match_id, hometeam_id, awayteam_id FROM airetro_matches WHERE is_started=0 AND game_id=1 ORDER BY match_id ASC LIMIT 1\"")
local result = handle:read("*a")
local splitted = split(result, "|")
handle:close()
match_id = tonumber(splitted[1])
team_home_id = tonumber(splitted[2])
team_away_id = tonumber(splitted[3])

handle = io.popen(sqlcmd.." \"SELECT team_name FROM teams WHERE game_id=1 AND team_id="..team_home_id..";\"")
local result = handle:read("*a")
local splitted = split(result, "|")
handle:close()
team_home = all_trim(splitted[1])

handle = io.popen(sqlcmd.." \"SELECT team_name FROM teams WHERE game_id=1 AND team_id="..team_away_id..";\"")
local result = handle:read("*a")
local splitted = split(result, "|")
handle:close()
team_away = all_trim(splitted[1])

console.writeline(team_away.." at "..team_home.." has started there are "..matches.." remaining matches including this one.")

message=team_home.." at "..team_away.." has started"
gui.addmessage(message)
handle = io.popen(sqlcmd.." \"INSERT INTO match_messages (match_message_id, match_id, game_id, message) VALUES (DEFAULT, "..match_id..", 1, '"..message.."');\"")
handle:close()

handle = io.popen(sqlcmd.." \"UPDATE airetro_matches SET started_at='"..date.."' WHERE match_id="..match_id..";\"")
handle:close()
handle = io.popen(sqlcmd.." \"UPDATE airetro_matches SET is_started=1 WHERE match_id="..match_id..";\"")
handle:close()

memory.usememorydomain("68K RAM")

waitframes(500)

-- computer ai demo mode
memory.write_u8(0xEA6F, 0)
memory.write_u8(0xEA71, team_home_id)
memory.write_u8(0xEA73, team_away_id)
-- play 15 minute quarters
--memory.write_u8(0xEA75, 2)

memory.write_u16_be(0xE880, 0)
waitframes(150)
gui.addmessage("AIRetro Presents")
waitframes(150)
gui.addmessage("John Madden Football 92")
waitframes(150)
gui.addmessage(team_away.." at "..team_home)
waitframes(150)
gui.addmessage(date)
waitframes(150)


waitframes(2000)
joypad.set({ ["P1 Start"] = true})
-- to intro credits

waitframes(2000)
joypad.set({ ["P1 Start"] = true})
-- to game select

waitframes(1000)
joypad.set({ ["P1 Start"] = true})
-- to game analysis
--

g_score_home = 0
g_score_away = 0
game_over = 0
i = 0
while game_over==0 do
    emu.frameadvance()
    i = i + 1
    if i > 2000 then
        score_home = 0
        score_away = 0
        score_home = memory.read_u8(0xE886) + score_home
        score_away = memory.read_u8(0xE896) + score_away
        score_home = memory.read_u8(0xE887) + score_home
        score_away = memory.read_u8(0xE897) + score_away
        score_home = memory.read_u8(0xE888) + score_home
        score_away = memory.read_u8(0xE898) + score_away
        score_home = memory.read_u8(0xE889) + score_home
        score_away = memory.read_u8(0xE899) + score_away
        score_home = memory.read_u8(0xE88A) + score_home
        score_away = memory.read_u8(0xE89A) + score_away
        g_score_home = score_home
        g_score_away = score_away
        down = memory.read_u16_be(0xDC08)
        quarter = memory.read_u16_be(0xE880)
        quarter = quarter + 1

        message=team_home.." "..score_home.." "..team_away.." "..score_away.." Quarter: "..quarter.." Down: "..down
        gui.addmessage(message)
        handle = io.popen(sqlcmd.." \"INSERT INTO match_messages (match_id, game_id, message) VALUES ("..match_id..", 1, '"..message.."');\"")
        handle:close()


        gameclock = memory.read_u16_be(0xDBFE)
        playclock = memory.read_u16_be(0xDBFC)

        if(quarter >= 5 and g_score_home ~= g_score_away) then
            game_over = 1
        end

        i = 0
    end
end

handle = io.popen("date \"+%Y-%m-%d %H:%M\"")
local result = handle:read("*a")
date = all_trim(result)
handle:close()

message=team_home.." at "..team_away.." has completed. Final Score: "..team_home..": "..g_score_home.." "..team_away..": "..g_score_away.."."
gui.addmessage(message)
handle = io.popen(sqlcmd.." \"INSERT INTO match_messages (match_id, game_id, message) VALUES ("..match_id..", 1, '"..message.."');\"")
handle:close()
handle = io.popen(sqlcmd.." \"UPDATE airetro_matches SET score_home="..g_score_home..", score_away="..g_score_away.." WHERE match_id="..match_id..";\"")
handle:close()
handle = io.popen(sqlcmd.." \"UPDATE airetro_matches SET is_finished=1 WHERE match_id="..match_id..";\"")
handle:close()
handle = io.popen(sqlcmd.." \"UPDATE airetro_matches SET finished_at='"..date.."' WHERE match_id="..match_id..";\"")
handle:close()


matches = tonumber(getmatches())

waitframes(2000)

client.reboot_core()

if(matches == 0) then
    client.closerom()
    handle = io.popen("pkill -15 mono")
    handle = io.popen("pkill -9 ffmpeg")
    handle:close()
end

goto madden92