/*
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 pitching as
select * from "raw".pitching;

alter table pitching drop column "lgID";
alter table pitching drop column "teamID";

-- it seems like Doc White was improperly credited with a win and save for game
-- 2 of 1902-09-02 when he should only have been credited with a save
update pitching
set W = 15
where playerID = 'whitedo01' and yearid = 1902 and stint = 1;

-- Give his win to Chick Fraser
update pitching
set W = 13
where playerID = 'frasech01' and yearid = 1902 and stint = 1;

update pitching
set
	W = nullif(W, ''),
	L = nullif(L, ''),
	G = nullif(G, ''),
	GS = nullif(GS, ''),
	CG = nullif(CG, ''),
	SHO = nullif(SHO, ''),
	SV = nullif(SV, ''),
	IPouts = nullif(IPouts, ''),
	H = nullif(H, ''),
	ER = nullif(ER, ''),
	HR = nullif(HR, ''),
	BB = nullif(BB, ''),
	SO = nullif(SO, ''),
	BAOpp = nullif(BAOpp, ''),
	IBB = nullif(IBB, ''),
	HBP = nullif(HBP, ''),
	BFP = nullif(BFP, ''),
	GF = nullif(GF, ''),
	R = nullif(R, ''),
	SH = nullif(SH, ''),
	SF = nullif(SF, ''),
	GIDP = nullif(GIDP, '');

CREATE TABLE IF NOT EXISTS main."pitching" (
	"player"	TEXT,
	"year"	NUMERIC,
	"stint"	NUMERIC,
	"W"	NUMERIC	check (W <= G),
	"L"	NUMERIC	check (L <= G),
	"G"	NUMERIC	check (W + L + SV <= G),
	"GS"	NUMERIC	check (GS <= G),
	"CG"	NUMERIC	check (CG <= GS),
	"SHO"	NUMERIC check (SHO <= G),
	"SV"	NUMERIC	check (SV <= G),
	"IPouts"	NUMERIC,
	"H"	NUMERIC,
	"ER"	NUMERIC	check (ER <= R),
	"HR"	NUMERIC check (HR <= H),
	"BB"	NUMERIC,
	"SO"	NUMERIC	check (SO <= IPouts),
	"BAOpp"	NUMERIC,
	"ERA"	NUMERIC,
	"IBB"	NUMERIC,
	"WP"	NUMERIC,
	"HBP"	NUMERIC,
	"BK"	NUMERIC,
	"BFP"	NUMERIC,
	"GF"	NUMERIC	check (GF <= G),
	"R"	NUMERIC,
	"SH"	NUMERIC,
	"SF"	NUMERIC,
	"GIDP"	NUMERIC	check (2 * GIDP <= IPouts),
	PRIMARY KEY("player","year","stint"),
	foreign key("year","player","stint") references "playerstints"("year","player","stint")
);

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