/*
Copyright (C) 2024 filifa

This program is free software: you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation, either version 3 of the License, or (at your option) any later
version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY
WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with
this program. If not, see <https://www.gnu.org/licenses/>.
*/

pragma foreign_keys = 0;

begin;
attach database 'lahman-raw.db' as 'raw';

create temp table teamseasons as
select * from teams;

update teamseasons
set teamid = 'WS9'
where teamid = 'WAS' and franchid = 'WAS';

update teamseasons
set teamid = 'PHP'
where teamid = 'PH4' and franchid = 'PHQ';

-- set game totals from bbref
update teamseasons
set G = 160
where yearid = 1979 and teamid = 'CHA';

update teamseasons
set G = 161
where yearid = 1979 and teamid = 'DET';

update teamseasons
set G = 132
where yearid = 1897 and teamid = 'CL4';

update teamseasons
set G = 155
where yearid = 1892 and teamid = 'PIT';

update teamseasons
set G = 18
where yearid = 1884 and teamid = 'WIL';

update teamseasons
set G = 105
where yearid = 1884 and teamid = 'CNU';

update teamseasons
set G = 80
where yearid = 1882 and teamid = 'SL4';

update teamseasons
set G = 80
where yearid = 1882 and teamid = 'LS2';

update teamseasons
set
	divid = nullif(divid, ''),
	G = nullif(G, ''),
	Ghome = nullif(Ghome, ''),
	W = nullif(W, ''),
	L = nullif(L, ''),
	divwin = nullif(divwin,''),
	wcwin = nullif(wcwin,''),
	lgwin = nullif(lgwin,''),
	wswin = nullif(wswin,''),
	CG = nullif(CG, ''),
	SHO = nullif(SHO, ''),
	SV = nullif(SV, ''),
	IPouts = nullif(IPouts, ''),
	H = nullif(H, ''),
	"2B" = nullif("2B", ''),
	"3B" = nullif("3B", ''),
	HR = nullif(HR, ''),
	ER = nullif(ER, ''),
	HR = nullif(HR, ''),
	BB = nullif(BB, ''),
	SO = nullif(SO, ''),
	HBP = nullif(HBP, ''),
	R = nullif(R, ''),
	SF = nullif(SF, ''),
	attendance = nullif(attendance, '');

alter table "teamseasons" drop column "franchID";

create table if not exists main."teamseasons" (
	"year"	NUMERIC,
	"league"	TEXT,
	"team"	TEXT,
	"division"	TEXT,
	"Rank"	NUMERIC,
	"G"	NUMERIC	check (W + L <= G),
	"Ghome"	NUMERIC	check (GHome <= G),
	"W"	NUMERIC	check (W <= G),
	"L"	NUMERIC	check (L <= G),
	"DivWin"	NUMERIC	check (divwin in ('Y','N')),
	"WCWin"	NUMERIC	check (wcwin in ('Y','N')),
	"LgWin"	NUMERIC	check (lgwin in ('Y','N')),
	"WSWin"	NUMERIC	check (wswin in ('Y','N')),
	"R"	NUMERIC,
	"AB"	NUMERIC,
	"H"	NUMERIC	check (H <= AB),
	"2B"	NUMERIC	check ("2B" <= H),
	"3B"	NUMERIC	check ("3B" <= H),
	"HR"	NUMERIC	check (HR <= H),
	"BB"	NUMERIC,
	"SO"	NUMERIC	check (SO <= AB),
	"SB"	NUMERIC,
	"CS"	NUMERIC,
	"HBP"	NUMERIC,
	"SF"	NUMERIC,
	"RA"	NUMERIC,
	"ER"	NUMERIC	check (ER <= RA),
	"ERA"	NUMERIC,
	"CG"	NUMERIC	check (CG <= G),
	"SHO"	NUMERIC	check (SHO <= G),
	"SV"	NUMERIC	check (SV <= G),
	"IPouts"	NUMERIC,
	"HA"	NUMERIC,
	"HRA"	NUMERIC	check (HRA <= HA),
	"BBA"	NUMERIC,
	"SOA"	NUMERIC	check (SOA <= IPouts),
	"E"	NUMERIC,
	"DP"	NUMERIC	check (2 * DP <= IPouts),
	"FP"	NUMERIC,
	"name"	NUMERIC,
	"park"	NUMERIC,
	"attendance"	NUMERIC,
	"BPF"	NUMERIC,
	"PPF"	NUMERIC,
	"teamIDBR"	TEXT,
	"teamIDlahman45"	TEXT,
	"teamIDretro"	TEXT,
	PRIMARY KEY("year","team"),
	UNIQUE("year","teamIDBR"),
	UNIQUE("year","teamIDlahman45"),
	UNIQUE("year","teamIDretro"),
	foreign key("year") references "seasons"("year"),
	foreign key("league") references "leagues"("ID"),
	foreign key("team") references "teams"("ID")
);

insert into main.teamseasons
select distinct * from temp."teamseasons";
commit;