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

alter table people drop column "ID";

-- insert some missing people
insert into people values
('fowlebu99', 1858, 3, 16, 'USA', 'NY', 'Fort Plain', 1913, 2, 26, 'USA', 'NY', 'Frankfort', 'Bud', 'Fowler', 'John W. Jackson', 155, 67, 'R', 'R', null, null, null, 'fowlebu99'),
('thompan01', null, null, null, null, null, null, null, null, null, null, null, null, null, 'Thompson', null, null, null, null, null, '1875-04-26', '1875-05-17', null, 'thompan01');

delete from people
where playerID = 'kellyho99';

update people
set
	birthcountry = nullif(birthcountry, ''),
	birthstate = nullif(birthstate, ''),
	birthcity = nullif(birthcity, ''),
	birthYear = nullif(birthYear, ''),
	birthMonth = nullif(birthMonth, ''),
	birthDay = nullif(birthDay, ''),
	deathYear = nullif(deathYear, ''),
	deathMonth = nullif(deathMonth, ''),
	deathDay = nullif(deathDay, ''),
	deathcountry = nullif(deathcountry, ''),
	deathstate = nullif(deathstate, ''),
	deathcity = nullif(deathcity, ''),
	weight = nullif(weight, ''),
	height = nullif(height, ''),
	bats = nullif(bats, ''),
	throws = nullif(throws, ''),
	debut = nullif(debut, ''),
	finalGame = nullif(finalGame, ''),
	bbrefID = nullif(bbrefid, ''),
	retroID = nullif(retroid, '');

update people
set throws = 'B'
where throws = 'S';

CREATE TABLE main."people" (
	"ID"	text,
	"birthYear"	NUMERIC,
	"birthMonth"	NUMERIC	check ("birthMonth" in (1,2,3,4,5,6,7,8,9,10,11,12)),
	"birthDay"	NUMERIC	check ("birthDay" between 1 and 31),
	"birthCountry"	text,
	"birthState"	text,
	"birthCity"	text,
	"deathYear"	numeric,
	"deathMonth"	numeric check ("deathMonth" in (1,2,3,4,5,6,7,8,9,10,11,12)),
	"deathDay"	numeric check ("deathDay" between 1 and 31),
	"deathCountry"	text,
	"deathState"	text,
	"deathCity"	text,
	"nameFirst"	text,
	"nameLast"	text,
	"nameGiven"	text,
	"weight"	NUMERIC	check ("weight" > 0),
	"height"	NUMERIC	check ("height" > 0),
	"bats"	text	check ("bats" in ('L','R','B')),
	"throws"	text	check ("throws" in ('L','R','B')),
	"debut"	text	check (unixepoch("debut") <= unixepoch("finalGame")),
	"bbrefID"	text	unique,
	"finalGame"	text,
	"retroID"	text	unique,
	primary key("ID")
);

INSERT INTO main."people" SELECT DISTINCT * FROM temp."people";
commit;