/* Reproduced from my post here: http://e-e.com/Q_26487697.html#a33724604
   (c) 2012 RichardTheKiwi
   License to use freely */

-- the most basic component, the Square.
if object_id('Square') is not null
drop table Square
GO
create table Square(
	row int,
	col int,
	mine bit default(0), -- this square is a mine
	neighbouring_mines tinyint, -- how many neighbours are mines
	opened bit default(0), -- 1 for opened
	flagged bit default(0),
	suspected bit default(0),
	primary key clustered(row,col) -- good for column travel given row
);
create index ix_Square_colrow on Square(col,row); -- travel by row given column
create index ix_Square_opened on Square(opened,mine); -- game ending test

-- very basic procedure for showing the mine field
-- @what is very flexible (and open to SQL injection, but who cares)
if object_id('ShowMineField') is not null
drop proc ShowMineField
GO
create proc ShowMineField
@what varchar(1000)
as
declare @sql nvarchar(max)
declare @cols nvarchar(max)
select @cols = coalesce(@cols+',','') + '['+convert(varchar(10),col)+']'
from (select distinct col from Square) M
set @sql = '
select ' + @cols + '
from (select row,col,what=' + @what + ' from Square) p
pivot (max(what) for col in (' + @cols + ')) pv
order by row'
--print (@sql)
exec (@sql)
GO

-- shows the game view of the mine field
if object_id('ShowMineField_Game') is not null
drop proc ShowMineField_Game
GO
create proc ShowMineField_Game
AS
exec ShowMineField 'case
	when opened&mine=1 then ''X''
	when opened=1 then coalesce(convert(char(1),nullif(neighbouring_mines,0)),'''')
	when flagged=1 then ''(B)''
	when suspected=1 then ''(?)''
	else ''<>'' end'
GO

-- shows where the mines are
if object_id('ShowMineField_Mines') is not null
drop proc ShowMineField_Mines
GO
create proc ShowMineField_Mines
AS
-- uncomment this line to CHEAT. or DEBUG
--exec ShowMineField 'convert(char(1),mine) + ''/'' + convert(char(1),neighbouring_mines)'
GO

-- shows all details about all square
if object_id('ShowMineField_Info') is not null
drop proc ShowMineField_Info
GO
create proc ShowMineField_Info
AS
-- uncomment this line to CHEAT. or DEBUG
--exec ShowMineField '
--	convert(char(1),mine) +
--	convert(char(1),neighbouring_mines) +
--	convert(char(1),opened) +
--	convert(char(1),flagged) +
--	convert(char(1),suspected)'
GO

-- check to see if the game has ended
if object_id('GameEnded') is not null
drop function GameEnded
GO
create function GameEnded() returns varchar(20) as
begin
if exists (select * from Square where opened&mine=1)
	return '** You lost'
if (select count(*)-sum(1*opened)-sum(1*mine) from Square) = 0
	return '** You won'
return null
end
GO

-- shows all details about all square
if object_id('ShowGameStatus') is not null
drop proc ShowGameStatus
GO
create proc ShowGameStatus
@message varchar(100) = ''
AS
if dbo.gameEnded()>'' set @message = dbo.gameEnded()
select
	Opened=sum(1*opened),
	Flagged=sum(1*flagged),
	[Total Mines]=sum(1*mine),
	[Total Squares]=count(*),
	[Message]=@message
from Square
exec ShowMineField_Game
exec ShowMineField_Mines
exec ShowMineField_Info
GO

-- function to generate a new mine field
if object_id('GenerateMineField') is not null
drop proc GenerateMineField
GO
create proc GenerateMineField
@rows int,
@cols int,
@mines int
AS
-- clear the current field
truncate table Square;
-- generate entire field including mines
;with generator as (
	select row=r.number, col=c.number, rn = row_number() over (order by newid())
	from master..spt_values r 
	inner join master..spt_values c on c.type='P' and c.number between 1 and @cols
	where r.type='P' and r.number between 1 and @rows
)
insert Square (row, col, mine)
select row, col, case when rn<=@mines then 1 else 0 end
from generator;

-- one-off counting of neighbouring mines for each mine
;with mines as (
	select a.row, a.col, c=count(*)
	from Square a
	inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
		and not (a.col=b.col and a.row=b.row)
		and b.mine=1
	group by a.row, a.col)
update m
set neighbouring_mines=coalesce(b.c,0)
from Square m
left join mines b on m.row=b.row and m.col=b.col

-- show game board
exec ShowGameStatus 'Ready';
GO

-- helper function to format a reference to a cell in RC notation
if object_id('formatStatus') is not null
drop function formatStatus
GO
create function formatStatus(@row int, @col int, @message varchar(100)) returns varchar(50) as
begin
return ('R' + convert(varchar(10),@row) + 'C' + convert(varchar(10),@col) + ': ' + @message)
end
GO

-- this toggles a cell between unmarked, safe or suspected
if object_id('MarkCell') is not null
drop proc MarkCell
GO
create proc MarkCell @row int, @col int AS
if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;

update Square set
	flagged = case when flagged|suspected=1 then 0 else 1 end,
	suspected = case when flagged=1 then 1 else 0 end
where row=@row and col=@col and opened=0
if @@rowcount=0
begin
	declare @status varchar(100)
	set @status = dbo.formatStatus(@row,@col,'is not valid for marking')
	exec ShowGameStatus @status
end
else
	exec ShowGameStatus;
GO
if exists (select * from sys.synonyms where name='m')
drop synonym m;
create synonym m for MarkCell
GO

-- this opens up a cells next to already opened cells, that are
-- next to opened cells with 0 neighbouring mines
if object_id('OpenZeroCounters') is not null
drop proc OpenZeroCounters
GO
create proc OpenZeroCounters AS
set nocount on;
select a=1 into #tmp;
while @@rowcount>0
	update a
	set opened=1, flagged=0, suspected=0
	from Square a
	inner join Square b on abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
		and b.opened=1 and b.neighbouring_mines=0
	where a.opened=0
exec ShowGameStatus;
GO

-- this opens up a cell to reveal the big question: mine or no mine
if object_id('OpenCell') is not null
drop proc OpenCell
GO
create proc OpenCell @row int, @col int AS
if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;

update Square
	set opened=1, flagged=0, suspected=0
where row=@row and col=@col and opened=0
if @@rowcount=0
begin
	declare @status varchar(100)
	set @status = dbo.formatStatus(@row,@col,'is not valid for opening')
	exec ShowGameStatus @status
end
else
	exec OpenZeroCounters
GO
if exists (select * from sys.synonyms where name='o')
drop synonym o;
create synonym o for OpenCell
GO

-- if there are as many neighbouring mines as flagged,
-- we take a big step and open up all unflagged neighbours
if object_id('QuickClear') is not null
drop proc QuickClear
GO
create proc QuickClear @row int, @col int AS
if dbo.gameEnded()>'' begin exec ShowGameStatus; return; end;

update c
set opened=1
from Square a
inner join Square c on abs(a.row-c.row)<=1 and abs(a.col-c.col)<=1 and c.flagged=0
where a.row=@row and a.col=@col
	and a.opened=1 -- only on opened cells, I think this is right(?)
	and a.neighbouring_mines = (
		select count(*)
		from Square b
		where abs(a.row-b.row)<=1 and abs(a.col-b.col)<=1
		and b.flagged=1)
if @@rowcount=0
begin
	declare @status varchar(100)
	set @status = dbo.formatStatus(@row,@col,'is not valid for clearing')
	exec ShowGameStatus @status
end
else
	exec OpenZeroCounters
GO
if exists (select * from sys.synonyms where name='c')
drop synonym c;
create synonym c for QuickClear
GO

exec GenerateMineField 50,50,10; -- this and open a single took 21s to clear
exec m 10, 5;
exec o 10, 1;
exec c 9, 4;