/*
** Mark R. McCumber; 10-Aug-2022
** All Rights Reserved.
*/

set nocount    on
set dateformat mdy

USE master

declare @dttm varchar(55)
select  @dttm=convert(varchar,getdate(),113)
raiserror('Beginning InstUS_Oligarchs.SQL at %s ....',1,1,@dttm) with nowait

GO

if exists (select * from sysdatabases where name='US_Oligarchs')
	begin
  		raiserror('Dropping existing US_Oligarchs database ....',0,1)
  		DROP database US_Oligarchs
	end

CHECKPOINT
go

raiserror('Creating US_Oligarchs database....',0,1)
go
/*
   Use default size with autogrow
*/

CREATE DATABASE US_Oligarchs
GO

CHECKPOINT

GO

USE US_Oligarchs
GO

if db_name() <> 'US_Oligarchs'
   raiserror('Error in InstUS_Oligarchs.SQL, ''USE US_Oligarchs'' failed!  Killing the SPID now.'
            ,22,127) with log

GO

CHECKPOINT
GO

/*if CAST(SERVERPROPERTY('ProductMajorVersion') AS INT)<12 
	BEGIN
		exec sp_dboption 'US_Oligarchs','trunc. log on chkpt.','true'
		exec sp_dboption 'US_Oligarchs','select into/bulkcopy','true'
	END
ELSE ALTER DATABASE [US_Oligarchs] SET RECOVERY SIMPLE WITH NO_WAIT
GO*/

execute sp_addtype id      ,'varchar(11)' ,'NOT NULL'
execute sp_addtype tid     ,'varchar(6)'  ,'NOT NULL'
execute sp_addtype empid   ,'char(9)'     ,'NOT NULL'

raiserror('Now at the create table section ....',0,1)

GO

/*  CREATE Supporting database tables
    tblStatus, tblOffice, 
    tblParty, tblStates
*/

if exists (select * from sysobjects where id = object_id('dbo.tblStatus') and sysstat & 0xf = 3)
	drop table "dbo"."tblStatus"
	
GO
if exists (select * from sysobjects where id = object_id('dbo.tblOffice') and sysstat & 0xf = 3)
	drop table "dbo"."tblOffice"
GO

if exists (select * from sysobjects where id = object_id('dbo.tblParty') and sysstat & 0xf = 3)
	drop table "dbo"."tblParty"
GO

if exists (select * from sysobjects where id = object_id('dbo.tblStates') and sysstat & 0xf = 3)
	drop table "dbo"."tblStates"
GO

if exists (select * from sysobjects where id = object_id('dbo.tblCands') and sysstat & 0xf = 3)
	drop table "dbo"."tblCands"

raiserror('Creating tblStatus ....',0,1)
CREATE TABLE tblStatus(
 [StatusID] [int] NOT NULL IDENTITY,
 [Status] [varchar](10) NOT NULL,
CONSTRAINT "tblStatus" PRIMARY KEY  CLUSTERED
	(
		"StatusID"
	))	 
 GO

raiserror('Inserting default data into tblCand_Status ....',0,1)
SET IDENTITY_INSERT dbo.tblStatus ON;
INSERT INTO dbo.tblStatus
	([StatusID], [Status])
VALUES
	(1,	'Challenger'),	
	(2,	'Incumbent'),	
	(3,	'Open');			
GO
SET IDENTITY_INSERT dbo.tblStatus OFF;

CHECKPOINT
GO

 /*Create Party affiliation table*/
 raiserror('Creating tblParty ....',0,1)
CREATE TABLE [dbo].[tblParty](
	[PartyID] int NOT NULL IDENTITY,
	[Party] varchar(3) NOT NULL,
	CONSTRAINT "PK_tblParty" PRIMARY KEY  CLUSTERED
	(
		"PartyID"
	))
GO

/* Populate tblParty with default values */
raiserror('Inserting Defaults data into tblParty ....',0,1)
SET IDENTITY_INSERT dbo.tblParty ON;
INSERT INTO tblParty
	(PartyID, Party)
VALUES
	(1, 'DEM'),
	(2, 'REP'),
	(3, 'LIB'),
	(4, 'GRE'),
	(5, 'OTH'),
	(6, 'SOC'),
	(7, 'CRV'),
	(8, 'UNK'),
	(9, 'IND'),
	(10, 'NPA'),
	(11, 'DFL'),
	(12, 'CST'),
	(13, 'LBU'),
	(14, 'PAF'),
	(15, 'NNE'),
	(16, 'REF'),
	(17, 'CON'),
	(18, 'AIP'),
	(19, 'TLP'),
	(20, 'JCN'),
	(21, 'SWP'),
	(22, 'N/A'),
	(23, 'IAP'),
	(24, 'CIT'),
	(25, 'AMP'),
	(26, 'NLP');
GO
SET IDENTITY_INSERT dbo.tblParty OFF;

/*  Populate tblOffice with default values*/
SET IDENTITY_INSERT dbo.tblOffice ON;
raiserror('Inserting default data into tblOffice ....',0,1)
INSERT INTO tblOffice
	([OfficeID], [Office])
VALUES
	(1, 'H'),
	(2, 'P'),
	(3, 'S');
GO
SET IDENTITY_INSERT dbo.tblOffice OFF;

GO
/*Create Candidates table*/
 raiserror('Creating tblCands table ....',0,1)
 
 /* Create US_Oligarchs tblCands */
CREATE TABLE [dbo].[tblCands](
	[Cand_ID] varchar(12) NOT NULL IDENTITY,
	[Cand_Last] varchar(50) NULL,
	[Cand_First] varchar(50) NOT NULL,
	[Cand_Middle] varchar(25) NULL,
	[Cand_Street_1] varchar(255) NOT NULL,
 	[Cand_Street_2] varchar(255) NULL,
	[Cand_City] varchar(50) NOT NULL,
	[Cand_St] INT NULL REFERENCES tblStates(StateID),
	[Cand_Zip] varchar(9) NOT NULL,
	[PartyID] INT NULL REFERENCES tblParty(PartyID),
	[Election_YR] INT NULL,
	[In_Office] INT NULL,
	CONSTRAINT PK_tblCands PRIMARY KEY  CLUSTERED 
	(
		"Cand_ID"
	))
GO

/*Create States table*/
 raiserror('Creating tblStates table ....',0,1)
 CREATE TABLE [dbo].[tblStates](
	[State_ID] INT NOT NULL IDENTITY,
	[State] varchar(15) NOT NULL,
	[StateAbbr] varchar(2) NOT NULL,
	CONSTRAINT PK_tblStates PRIMARY KEY  CLUSTERED 
	(
		"StateID"
	))
GO

/* Populate tblStates with default values */
raiserror('Inserting Defaults data into tblStates ....',0,1)
SET IDENTITY_INSERT dbo.tblStates ON;
INSERT INTO tblStates
	(StateID, State, StateAbbr)
VALUES
(1,	'ALABAMA', 'AL'),
(2,	'ALASKA',  'AK'),
(3,	'ARIZONA', 'AZ'),
(4,	'ARKANSAS', 'AR'),
(5,	'CALIFORNIA', 'CA'),
(6,	'COLORADO',	'CO'),
(7,	'CONNECTICUT', 'CT'),
(8,	'DELAWARE', 'DE'),
(9,	'DISTRICT OF COL', 'DC'),
(10, 'FLORIDA', 'FL'),
(11, 'GEORGIA',	'GA'),
(12, 'HAWAII', 'HI'),
(13, 'IDAHO', 'ID'),
(14, 'ILLINOIS', 'IL'),
(15, 'INDIANA',	'IN'),
(16, 'IOWA', 'IA'),
(17, 'KANSAS', 'KS'),
(18, 'KENTUCKY', 'KY'),
(19, 'LOUISIANA', 'LA'),
(20, 'MAINE', 'ME'),
(21, 'MARYLAND', 'MD'),
(22, 'MASSACHUSETTS', 'MA'),
(23, 'MICHIGAN', 'MI'),
(24, 'MINNESOTA', 'MN'),
(25, 'MISSISSIPPI',	'MS'),
(26, 'MISSOURI', 'MO'), 
(27, 'MONTANA',	'MT'),
(28, 'NEBRASKA', 'NE'),
(29, 'NEVADA',	'NV'),
(30, 'NEW HAMPSHIRE', 'NH'),
(31, 'NEW JERSEY', 'NJ'),
(32, 'NEW MEXICO', 'NM'),
(33, 'NEW YORK', 'NY'),
(34, 'NORTH CAROLINA', 'NC'),
(35, 'NORTH DAKOTA', 'ND'),
(36, 'OHIO', 'OH'),
(37, 'OKLAHOMA', 'OK'),
(38, 'OREGON', 'OR'),
(39, 'PENNSYLVANIA', 'PA'),
(40, 'RHODE ISLAND', 'RI'),
(41, 'SOUTH CAROLINA', 'SC'),
(42, 'SOUTH DAKOTA', 'SD'),
(43, 'TENNESSEE', 'TN'),
(44, 'TEXAS', 'TX'),
(45, 'UTAH', 'UT'),
(46, 'VERMONT',	'VT'),
(47, 'VIRGINIA', 'VA'),
(48, 'WASHINGTON', 'WA'),
(49, 'WEST VIRGINIA', 'WV'),
(50, 'WISCONSIN', 'WI'),
(51, 'WYOMING', 'WY'),
(52, 'United States', 'US');
SET IDENTITY_INSERT dbo.tblStates OFF;
GO