USE [VIGIL-VRM]
GO


-- DROP VIEWS

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_subjects]'))
DROP VIEW [dbo].[vw_subjects]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_subjects]'))
DROP VIEW [dbo].[vw_investigators]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_subjects]'))
DROP VIEW [dbo].[vw_transcribers]
GO


-- DROP TABLES

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit]') AND type in (N'U'))
DROP TABLE [dbo].[audit]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[casefile_investigators]') AND type in (N'U'))
DROP TABLE [dbo].[casefile_investigators]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[casefiles]') AND type in (N'U'))
DROP TABLE [dbo].[casefiles]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[countries]') AND type in (N'U'))
DROP TABLE [dbo].[countries]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[export_files]') AND type in (N'U'))
DROP TABLE [dbo].[export_files]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[export_types]') AND type in (N'U'))
DROP TABLE [dbo].[export_types]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exports]') AND type in (N'U'))
DROP TABLE [dbo].[exports]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[footage_types]') AND type in (N'U'))
DROP TABLE [dbo].[footage_types]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[interview_attendees]') AND type in (N'U'))
DROP TABLE [dbo].[interview_attendees]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[interview_footage]') AND type in (N'U'))
DROP TABLE [dbo].[interview_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[interview_subjects]') AND type in (N'U'))
DROP TABLE [dbo].[interview_subjects]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[interview_transcribers]') AND type in (N'U'))
DROP TABLE [dbo].[interview_transcribers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[interview_types]') AND type in (N'U'))
DROP TABLE [dbo].[interview_types]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[interviews]') AND type in (N'U'))
DROP TABLE [dbo].[interviews]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[locations]') AND type in (N'U'))
DROP TABLE [dbo].[locations]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[offenses]') AND type in (N'U'))
DROP TABLE [dbo].[offenses]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[personnel]') AND type in (N'U'))
DROP TABLE [dbo].[personnel]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[personnel_type]') AND type in (N'U'))
DROP TABLE [dbo].[personnel_type]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[provinces]') AND type in (N'U'))
DROP TABLE [dbo].[provinces]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[recorders]') AND type in (N'U'))
DROP TABLE [dbo].[recorders]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sections]') AND type in (N'U'))
DROP TABLE [dbo].[sections]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[subjects]') AND type in (N'U'))
DROP TABLE [dbo].[subjects]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_version]') AND type in (N'U'))
DROP TABLE [dbo].[table_version]
GO


-- CREATE TABLES

CREATE TABLE [dbo].[audit] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[source] [varchar] (50) NOT NULL,
	[message] [varchar] (200) NOT NULL,
	[audit_time] [datetime] NOT NULL,
	[error_level] [int] NOT NULL,
	[personnel_id_fk] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[casefile_investigators] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[casefile_id_fk] [int] NOT NULL,
	[personnel_id_fk] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[casefiles] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[file_no] [varchar] (20) NOT NULL,
	[offense_id_fk] [int] NOT NULL,
	[section_id_fk] [int] NOT NULL,
	[notes] [varchar] (1000) NOT NULL,
	[last_updated] [datetime] NOT NULL,
	[private] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[countries] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[name] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[export_files] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[export_id_fk] [int] NOT NULL,
	[filename] [varchar] (256) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[export_types] (
	[id] [int] NOT NULL,
	[type] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[exports] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[interview_id_fk] [int] NOT NULL,
	[personnel_id_fk] [int] NOT NULL,
	[export_time] [datetime] NOT NULL,
	[export_type_fk] [int] NOT NULL,
	[export_path] [nvarchar] (256) NOT NULL,
	[reason] [nvarchar] (300) NOT NULL,
	[copies][int] NOT NULL DEFAULT 1
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[footage_types] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[description] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[interview_attendees] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[interview_id_fk] [int] NOT NULL,
	[personnel_id_fk] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[interview_footage] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[interview_id_fk] [int] NOT NULL,
	[footage_type_id_fk] [int] NOT NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NOT NULL,
	[footage_folder] [varchar] (250) NOT NULL,
	[filename] [varchar] (250) NOT NULL,
	[notes] [varchar] (1000) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[interview_subjects] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[interview_id_fk] [int] NOT NULL,
	[subject_id_fk] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[interview_transcribers] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[interview_id_fk] [int] NOT NULL,
	[personnel_id_fk] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[interview_types] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[description] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[interviews] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[casefile_id_fk] [int] NOT NULL,
	[interview_type_id_fk] [int] NOT NULL,
	[location_id_fk] [int] NOT NULL,
	[interview_date] [datetime] NOT NULL,
	[notes] [varchar] (1000) NOT NULL,
	[private] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[locations] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[description] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[offenses] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[description] [varchar] (100) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[personnel] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[lastname] [varchar] (50) NOT NULL,
	[firstname] [varchar] (100) NOT NULL,
	[type_id_fk] [int] NOT NULL,
	[section_id_fk] [int] NOT NULL,
	[phone] [varchar] (20) NOT NULL,
	[country_id_fk] [int] NOT NULL,
	[province_id_fk] [int] NOT NULL,
	[notes] [varchar] (1000) NOT NULL,
	[username] [varchar] (20) NOT NULL,
	[password] [varchar] (50) NOT NULL,
	[admin] [bit] NOT NULL,
	[department] [varchar] (200) NOT NULL,
	[regimental] [varchar] (20) NOT NULL,
	[password_version][int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[personnel_type] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[description] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[provinces] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[name] [varchar] (50) NOT NULL,
	[code] [varchar] (5) NOT NULL,
	[country_id_fk] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[recorders] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[location_id_fk] [int] NOT NULL,
	[IP] [varchar] (50) NOT NULL,
	[port] [int] NOT NULL,
	[description] [varchar] (50) NULL,
	[dvr_type] [int] NOT NULL,
	[username] [varchar] (50) NULL,
	[password] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sections] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[description] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[subjects] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[lastname] [varchar] (50) NOT NULL,
	[firstname] [varchar] (100) NOT NULL,
	[dob] [datetime] NULL,
	[gender] [char] (1) NOT NULL,
	[address] [varchar] (100) NOT NULL,
	[city] [varchar] (50) NOT NULL,
	[country_id_fk] [int] NOT NULL,
	[province_id_fk] [int] NOT NULL,
	[postal] [varchar] (20) NOT NULL,
	[phone1] [varchar] (20) NOT NULL,
	[phone2] [varchar] (20) NOT NULL,
	[notes] [varchar] (1000) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[table_version] (
	[entity] [nvarchar] (50) NOT NULL,
	[version] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[audit] WITH NOCHECK ADD
	CONSTRAINT [PK_audit] PRIMARY KEY  CLUSTERED
	(
		[id]
	)  ON [PRIMARY],
	
	CONSTRAINT [DF_audit_personnel_id_fk] DEFAULT (0) FOR [personnel_id_fk]
GO

ALTER TABLE [dbo].[casefile_investigators] WITH NOCHECK ADD
	CONSTRAINT [PK_interview_investigators] PRIMARY KEY  CLUSTERED
	(
		[casefile_id_fk],
		[personnel_id_fk]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[countries] WITH NOCHECK ADD
	CONSTRAINT [PK_countries] PRIMARY KEY  CLUSTERED
	(
		[name]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[export_files] WITH NOCHECK ADD
	CONSTRAINT [PK_export_files] PRIMARY KEY  CLUSTERED
	(
		[export_id_fk],
		[filename]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[export_types] WITH NOCHECK ADD
	CONSTRAINT [PK_export_types] PRIMARY KEY  CLUSTERED
	(
		[type]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[exports] WITH NOCHECK ADD
	CONSTRAINT [PK_exports] PRIMARY KEY  CLUSTERED
	(
		[interview_id_fk],
		[personnel_id_fk],
		[export_time]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[footage_types] WITH NOCHECK ADD
	CONSTRAINT [PK_footage_types] PRIMARY KEY  CLUSTERED
	(
		[description]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[interview_attendees] WITH NOCHECK ADD
	CONSTRAINT [PK_interview_attendees] PRIMARY KEY  CLUSTERED
	(
		[interview_id_fk],
		[personnel_id_fk]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[interview_footage] WITH NOCHECK ADD
	CONSTRAINT [PK_interview_footage] PRIMARY KEY  CLUSTERED
	(
		[interview_id_fk],
		[footage_folder],
		[filename]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[interview_transcribers] WITH NOCHECK ADD
	CONSTRAINT [PK_interview_transcribers] PRIMARY KEY  CLUSTERED
	(
		[interview_id_fk],
		[personnel_id_fk]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[interview_types] WITH NOCHECK ADD
	CONSTRAINT [PK_statement_types] PRIMARY KEY  CLUSTERED
	(
		[description]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[interviews] WITH NOCHECK ADD
	CONSTRAINT [PK_interviews] PRIMARY KEY  CLUSTERED
	(
		[id]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[locations] WITH NOCHECK ADD
	CONSTRAINT [PK_locations] PRIMARY KEY  CLUSTERED
	(
		[description]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[offenses] WITH NOCHECK ADD
	CONSTRAINT [PK_complaints] PRIMARY KEY  CLUSTERED
	(
		[description]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[personnel_type] WITH NOCHECK ADD
	CONSTRAINT [PK_personnel_type] PRIMARY KEY  CLUSTERED
	(
		[description]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[provinces] WITH NOCHECK ADD
	CONSTRAINT [PK_provinces] PRIMARY KEY  CLUSTERED
	(
		[name],
		[code]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[recorders] WITH NOCHECK ADD
	CONSTRAINT [PK_DVR] PRIMARY KEY  CLUSTERED
	(
		[IP]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[sections] WITH NOCHECK ADD
	CONSTRAINT [PK_sections] PRIMARY KEY  CLUSTERED
	(
		[description]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[subjects] WITH NOCHECK ADD
	CONSTRAINT [PK_subjects] PRIMARY KEY  CLUSTERED
	(
		[lastname],
		[firstname],
		[gender]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[casefiles] WITH NOCHECK ADD
	CONSTRAINT [DF_file_file_no] DEFAULT ('') FOR [file_no],
	CONSTRAINT [DF_file_notes] DEFAULT ('') FOR [notes]
GO

ALTER TABLE [dbo].[exports] WITH NOCHECK ADD
	CONSTRAINT [DF_exports_reason] DEFAULT ('') FOR [reason]
GO

ALTER TABLE [dbo].[interview_footage] WITH NOCHECK ADD
	CONSTRAINT [DF_interview_video_footage_type_id_fk] DEFAULT (0) FOR [footage_type_id_fk]
GO

ALTER TABLE [dbo].[interviews] WITH NOCHECK ADD
	CONSTRAINT [DF_interviews_notes] DEFAULT ('') FOR [notes]
GO

ALTER TABLE [dbo].[personnel] WITH NOCHECK ADD
	CONSTRAINT [DF_personnel_section_id_fk] DEFAULT (0) FOR [section_id_fk],
	CONSTRAINT [DF_personnel_country_id_fk] DEFAULT (0) FOR [country_id_fk],
	CONSTRAINT [DF_personnel_province_id_fk] DEFAULT (0) FOR [province_id_fk],
	CONSTRAINT [DF_personnel_username] DEFAULT ('') FOR [username],
	CONSTRAINT [DF_personnel_password] DEFAULT ('') FOR [password],
	CONSTRAINT [DF_personnel_admin] DEFAULT (0) FOR [admin],
	CONSTRAINT [DF_personnel_department] DEFAULT ('') FOR [department],
	CONSTRAINT [DF_personnel_regimental] DEFAULT ('') FOR [regimental],
	CONSTRAINT [DF_personnel_password_version] DEFAULT (0) FOR [password_version]
GO

ALTER TABLE [dbo].[recorders] WITH NOCHECK ADD
	CONSTRAINT [DF_recorders_port] DEFAULT (0) FOR [port]
GO

ALTER TABLE [dbo].[subjects] WITH NOCHECK ADD
	CONSTRAINT [DF_subject_firstname] DEFAULT ('') FOR [firstname],
	CONSTRAINT [DF_subject_country_id_fk] DEFAULT (0) FOR [country_id_fk],
	CONSTRAINT [DF_subject_province_id_fk] DEFAULT (0) FOR [province_id_fk],
	CONSTRAINT [DF_subject_postal] DEFAULT ('') FOR [postal],
	CONSTRAINT [DF_subject_phone1] DEFAULT ('') FOR [phone1],
	CONSTRAINT [DF_subject_phone2] DEFAULT ('') FOR [phone2],
	CONSTRAINT [DF_subject_notes] DEFAULT ('') FOR [notes]
GO


-- CREATE VIEWS

CREATE VIEW dbo.vw_subjects
AS

	SELECT
		s.id,
		s.lastname AS [Last Name],
		s.firstname AS [First Name],
		s.dob AS [Date of Birth],
		s.gender AS Gender,
		s.address AS [Street Address],
		s.city AS City,
		p.name AS [State/Province],
		s.postal AS [Zip/Postal Code],
		c.name AS Country,
		s.phone1 AS [Phone Number 1],
		s.phone2 AS [Phone Number 2],
		s.notes AS Notes
	FROM subjects s LEFT OUTER JOIN
	countries c ON s.country_id_fk = c.id LEFT OUTER JOIN
	provinces p ON s.province_id_fk = p.id
GO

CREATE VIEW dbo.vw_investigators
AS
SELECT     dbo.personnel.id, dbo.personnel.lastname AS [Last Name], dbo.personnel.firstname AS [First Name], dbo.personnel_type.description AS Type,
					  COALESCE (dbo.sections.description, '') AS [Section], dbo.personnel.phone AS Phone, COALESCE (dbo.countries.name, '') AS Country,
					  dbo.personnel.department AS [Department], dbo.personnel.regimental AS [Regimental #],
					  COALESCE (dbo.provinces.name, '') AS [State/Province], dbo.personnel.notes AS Notes
FROM         dbo.personnel INNER JOIN
					  dbo.personnel_type ON dbo.personnel.type_id_fk = dbo.personnel_type.id LEFT OUTER JOIN
					  dbo.sections ON dbo.personnel.section_id_fk = dbo.sections.id LEFT OUTER JOIN
					  dbo.countries ON dbo.personnel.country_id_fk = dbo.countries.id LEFT OUTER JOIN
					  dbo.provinces ON dbo.personnel.province_id_fk = dbo.provinces.id
WHERE     (dbo.personnel_type.description = N'investigator')
GO

CREATE VIEW dbo.vw_transcribers
AS
SELECT     dbo.personnel.id, dbo.personnel.lastname AS [Last Name], dbo.personnel.firstname AS [First Name], dbo.personnel_type.description AS Type,
					  COALESCE (dbo.sections.description, '') AS [Section], dbo.personnel.phone AS Phone, COALESCE (dbo.countries.name, '') AS Country,
					  dbo.personnel.department AS [Department], dbo.personnel.regimental AS [Regimental #],
					  COALESCE (dbo.provinces.name, '') AS [State/Province], dbo.personnel.notes AS Notes
FROM         dbo.personnel INNER JOIN
					  dbo.personnel_type ON dbo.personnel.type_id_fk = dbo.personnel_type.id LEFT OUTER JOIN
					  dbo.sections ON dbo.personnel.section_id_fk = dbo.sections.id LEFT OUTER JOIN
					  dbo.countries ON dbo.personnel.country_id_fk = dbo.countries.id LEFT OUTER JOIN
					  dbo.provinces ON dbo.personnel.province_id_fk = dbo.provinces.id
WHERE     (dbo.personnel_type.description = N'transcriber')
GO


-- POPULATE DATA

INSERT INTO countries (name) VALUES('Afghanistan')
INSERT INTO countries (name) VALUES('Akrotiri')
INSERT INTO countries (name) VALUES('Albania')
INSERT INTO countries (name) VALUES('Algeria')
INSERT INTO countries (name) VALUES('American Samoa')
INSERT INTO countries (name) VALUES('Andorra')
INSERT INTO countries (name) VALUES('Angola')
INSERT INTO countries (name) VALUES('Anguilla')
INSERT INTO countries (name) VALUES('Antarctica')
INSERT INTO countries (name) VALUES('Antigua and Barbuda')
INSERT INTO countries (name) VALUES('Argentina')
INSERT INTO countries (name) VALUES('Armenia')
INSERT INTO countries (name) VALUES('Aruba')
INSERT INTO countries (name) VALUES('Ashmore and Cartier Islands')
INSERT INTO countries (name) VALUES('Australia')
INSERT INTO countries (name) VALUES('Austria')
INSERT INTO countries (name) VALUES('Azerbaijan')
INSERT INTO countries (name) VALUES('Bahrain')
INSERT INTO countries (name) VALUES('Baker Island')
INSERT INTO countries (name) VALUES('Bangladesh')
INSERT INTO countries (name) VALUES('Barbados')
INSERT INTO countries (name) VALUES('Bassas da India')
INSERT INTO countries (name) VALUES('Belarus')
INSERT INTO countries (name) VALUES('Belgium')
INSERT INTO countries (name) VALUES('Belize')
INSERT INTO countries (name) VALUES('Benin')
INSERT INTO countries (name) VALUES('Bermuda')
INSERT INTO countries (name) VALUES('Bhutan')
INSERT INTO countries (name) VALUES('Bolivia')
INSERT INTO countries (name) VALUES('Bosnia and Herzegovina')
INSERT INTO countries (name) VALUES('Botswana')
INSERT INTO countries (name) VALUES('Bouvet Island')
INSERT INTO countries (name) VALUES('Brazil')
INSERT INTO countries (name) VALUES('British Indian Ocean Territory')
INSERT INTO countries (name) VALUES('British Virgin Islands')
INSERT INTO countries (name) VALUES('Brunei')
INSERT INTO countries (name) VALUES('Bulgaria')
INSERT INTO countries (name) VALUES('Burkina Faso')
INSERT INTO countries (name) VALUES('Burma')
INSERT INTO countries (name) VALUES('Burundi')
INSERT INTO countries (name) VALUES('Cambodia')
INSERT INTO countries (name) VALUES('Cameroon')
INSERT INTO countries (name) VALUES('Canada')
INSERT INTO countries (name) VALUES('Cape Verde')
INSERT INTO countries (name) VALUES('Cayman Islands')
INSERT INTO countries (name) VALUES('Central African Republic')
INSERT INTO countries (name) VALUES('Chad')
INSERT INTO countries (name) VALUES('Chile')
INSERT INTO countries (name) VALUES('China')
INSERT INTO countries (name) VALUES('Christmas Island')
INSERT INTO countries (name) VALUES('Clipperton Island')
INSERT INTO countries (name) VALUES('Cocos (Keeling Islands')
INSERT INTO countries (name) VALUES('Colombia')
INSERT INTO countries (name) VALUES('Comoros')
INSERT INTO countries (name) VALUES('Cook Islands')
INSERT INTO countries (name) VALUES('Coral Sea Islands')
INSERT INTO countries (name) VALUES('Costa Rica')
INSERT INTO countries (name) VALUES('Cote d Ivoire')
INSERT INTO countries (name) VALUES('Croatia')
INSERT INTO countries (name) VALUES('Cuba')
INSERT INTO countries (name) VALUES('Cyprus')
INSERT INTO countries (name) VALUES('Czech Republic')
INSERT INTO countries (name) VALUES('Democratic Republic of the Congo')
INSERT INTO countries (name) VALUES('Denmark')
INSERT INTO countries (name) VALUES('Dhekelia')
INSERT INTO countries (name) VALUES('Djibouti')
INSERT INTO countries (name) VALUES('Dominica')
INSERT INTO countries (name) VALUES('Dominican Republic')
INSERT INTO countries (name) VALUES('East Timor')
INSERT INTO countries (name) VALUES('Ecuador')
INSERT INTO countries (name) VALUES('Egypt')
INSERT INTO countries (name) VALUES('El Salvador')
INSERT INTO countries (name) VALUES('Equatorial Guinea')
INSERT INTO countries (name) VALUES('Eritrea')
INSERT INTO countries (name) VALUES('Estonia')
INSERT INTO countries (name) VALUES('Ethiopia')
INSERT INTO countries (name) VALUES('Europa Island')
INSERT INTO countries (name) VALUES('European Union')
INSERT INTO countries (name) VALUES('Falkland Islands')
INSERT INTO countries (name) VALUES('Faroe Islands')
INSERT INTO countries (name) VALUES('Fiji')
INSERT INTO countries (name) VALUES('Finland')
INSERT INTO countries (name) VALUES('France')
INSERT INTO countries (name) VALUES('French Guiana')
INSERT INTO countries (name) VALUES('French Polynesia')
INSERT INTO countries (name) VALUES('French Southern and Antarctic Lands')
INSERT INTO countries (name) VALUES('Gabon')
INSERT INTO countries (name) VALUES('Gaza Strip')
INSERT INTO countries (name) VALUES('Georgia')
INSERT INTO countries (name) VALUES('Germany')
INSERT INTO countries (name) VALUES('Ghana')
INSERT INTO countries (name) VALUES('Gibraltar')
INSERT INTO countries (name) VALUES('Glorioso Islands')
INSERT INTO countries (name) VALUES('Greece')
INSERT INTO countries (name) VALUES('Greenland')
INSERT INTO countries (name) VALUES('Grenada')
INSERT INTO countries (name) VALUES('Guadeloupe')
INSERT INTO countries (name) VALUES('Guam')
INSERT INTO countries (name) VALUES('Guatemala')
INSERT INTO countries (name) VALUES('Guernsey')
INSERT INTO countries (name) VALUES('Guinea')
INSERT INTO countries (name) VALUES('Guinea-Bissau')
INSERT INTO countries (name) VALUES('Guyana')
INSERT INTO countries (name) VALUES('Haiti')
INSERT INTO countries (name) VALUES('Heard Island and McDonald Islands')
INSERT INTO countries (name) VALUES('Holy See')
INSERT INTO countries (name) VALUES('Honduras')
INSERT INTO countries (name) VALUES('Hong Kong')
INSERT INTO countries (name) VALUES('Howland Island')
INSERT INTO countries (name) VALUES('Hungary')
INSERT INTO countries (name) VALUES('Iceland')
INSERT INTO countries (name) VALUES('India')
INSERT INTO countries (name) VALUES('Indonesia')
INSERT INTO countries (name) VALUES('Iran')
INSERT INTO countries (name) VALUES('Iraq')
INSERT INTO countries (name) VALUES('Ireland')
INSERT INTO countries (name) VALUES('Isle of Man')
INSERT INTO countries (name) VALUES('Israel')
INSERT INTO countries (name) VALUES('Italy')
INSERT INTO countries (name) VALUES('Jamaica')
INSERT INTO countries (name) VALUES('Jan Mayen')
INSERT INTO countries (name) VALUES('Japan')
INSERT INTO countries (name) VALUES('Jarvis Island')
INSERT INTO countries (name) VALUES('Jersey')
INSERT INTO countries (name) VALUES('Johnston Atoll')
INSERT INTO countries (name) VALUES('Jordan')
INSERT INTO countries (name) VALUES('Juan de Nova Island')
INSERT INTO countries (name) VALUES('Kazakhstan')
INSERT INTO countries (name) VALUES('Kenya')
INSERT INTO countries (name) VALUES('Kingman Reef')
INSERT INTO countries (name) VALUES('Kiribati')
INSERT INTO countries (name) VALUES('Korea, North')
INSERT INTO countries (name) VALUES('Korea, South')
INSERT INTO countries (name) VALUES('Kuwait')
INSERT INTO countries (name) VALUES('Kyrgyzstan')
INSERT INTO countries (name) VALUES('Laos')
INSERT INTO countries (name) VALUES('Latvia')
INSERT INTO countries (name) VALUES('Lebanon')
INSERT INTO countries (name) VALUES('Lesotho')
INSERT INTO countries (name) VALUES('Liberia')
INSERT INTO countries (name) VALUES('Libya')
INSERT INTO countries (name) VALUES('Liechtenstein')
INSERT INTO countries (name) VALUES('Lithuania')
INSERT INTO countries (name) VALUES('Luxembourg')
INSERT INTO countries (name) VALUES('Macau')
INSERT INTO countries (name) VALUES('Macedonia')
INSERT INTO countries (name) VALUES('Madagascar')
INSERT INTO countries (name) VALUES('Malawi')
INSERT INTO countries (name) VALUES('Malaysia')
INSERT INTO countries (name) VALUES('Maldives')
INSERT INTO countries (name) VALUES('Mali')
INSERT INTO countries (name) VALUES('Malta')
INSERT INTO countries (name) VALUES('Marshall Islands')
INSERT INTO countries (name) VALUES('Martinique')
INSERT INTO countries (name) VALUES('Mauritania')
INSERT INTO countries (name) VALUES('Mauritius')
INSERT INTO countries (name) VALUES('Mayotte')
INSERT INTO countries (name) VALUES('Mexico')
INSERT INTO countries (name) VALUES('Micronesia')
INSERT INTO countries (name) VALUES('Midway Islands')
INSERT INTO countries (name) VALUES('Moldova')
INSERT INTO countries (name) VALUES('Monaco')
INSERT INTO countries (name) VALUES('Mongolia')
INSERT INTO countries (name) VALUES('Montserrat')
INSERT INTO countries (name) VALUES('Morocco')
INSERT INTO countries (name) VALUES('Mozambique')
INSERT INTO countries (name) VALUES('Namibia')
INSERT INTO countries (name) VALUES('Nauru')
INSERT INTO countries (name) VALUES('Navassa Island')
INSERT INTO countries (name) VALUES('Nepal')
INSERT INTO countries (name) VALUES('Netherlands')
INSERT INTO countries (name) VALUES('Netherlands Antilles')
INSERT INTO countries (name) VALUES('New Caledonia')
INSERT INTO countries (name) VALUES('New Zealand')
INSERT INTO countries (name) VALUES('Nicaragua')
INSERT INTO countries (name) VALUES('Niger')
INSERT INTO countries (name) VALUES('Nigeria')
INSERT INTO countries (name) VALUES('Niue')
INSERT INTO countries (name) VALUES('Norfolk Island')
INSERT INTO countries (name) VALUES('Northern Mariana Islands')
INSERT INTO countries (name) VALUES('Norway')
INSERT INTO countries (name) VALUES('Oman')
INSERT INTO countries (name) VALUES('Pakistan')
INSERT INTO countries (name) VALUES('Palau')
INSERT INTO countries (name) VALUES('Palmyra Atoll')
INSERT INTO countries (name) VALUES('Panama')
INSERT INTO countries (name) VALUES('Papua New Guinea')
INSERT INTO countries (name) VALUES('Paracel Islands')
INSERT INTO countries (name) VALUES('Paraguay')
INSERT INTO countries (name) VALUES('Peru')
INSERT INTO countries (name) VALUES('Philippines')
INSERT INTO countries (name) VALUES('Pitcairn Islands')
INSERT INTO countries (name) VALUES('Poland')
INSERT INTO countries (name) VALUES('Portugal')
INSERT INTO countries (name) VALUES('Puerto Rico')
INSERT INTO countries (name) VALUES('Qatar')
INSERT INTO countries (name) VALUES('Republic of the Congo')
INSERT INTO countries (name) VALUES('Reunion')
INSERT INTO countries (name) VALUES('Romania')
INSERT INTO countries (name) VALUES('Russia')
INSERT INTO countries (name) VALUES('Rwanda')
INSERT INTO countries (name) VALUES('Saint Helena')
INSERT INTO countries (name) VALUES('Saint Kitts and Nevis')
INSERT INTO countries (name) VALUES('Saint Lucia')
INSERT INTO countries (name) VALUES('Saint Pierre and Miquelon')
INSERT INTO countries (name) VALUES('Saint Vincent and the Grenadines')
INSERT INTO countries (name) VALUES('Samoa')
INSERT INTO countries (name) VALUES('San Marino')
INSERT INTO countries (name) VALUES('Sao Tome and Principe')
INSERT INTO countries (name) VALUES('Saudi Arabia')
INSERT INTO countries (name) VALUES('Senegal')
INSERT INTO countries (name) VALUES('Serbia and Montenegro')
INSERT INTO countries (name) VALUES('Seychelles')
INSERT INTO countries (name) VALUES('Sierra Leone')
INSERT INTO countries (name) VALUES('Singapore')
INSERT INTO countries (name) VALUES('Slovakia')
INSERT INTO countries (name) VALUES('Slovenia')
INSERT INTO countries (name) VALUES('Solomon Islands')
INSERT INTO countries (name) VALUES('Somalia')
INSERT INTO countries (name) VALUES('South Africa')
INSERT INTO countries (name) VALUES('South Georgia')
INSERT INTO countries (name) VALUES('Spain')
INSERT INTO countries (name) VALUES('Spratly Islands')
INSERT INTO countries (name) VALUES('Sri Lanka')
INSERT INTO countries (name) VALUES('Sudan')
INSERT INTO countries (name) VALUES('Suriname')
INSERT INTO countries (name) VALUES('Svalbard')
INSERT INTO countries (name) VALUES('Swaziland')
INSERT INTO countries (name) VALUES('Sweden')
INSERT INTO countries (name) VALUES('Switzerland')
INSERT INTO countries (name) VALUES('Syria')
INSERT INTO countries (name) VALUES('Taiwan')
INSERT INTO countries (name) VALUES('Tajikistan')
INSERT INTO countries (name) VALUES('Tanzania')
INSERT INTO countries (name) VALUES('Thailand')
INSERT INTO countries (name) VALUES('The Bahamas')
INSERT INTO countries (name) VALUES('The Gambia')
INSERT INTO countries (name) VALUES('Togo')
INSERT INTO countries (name) VALUES('Tokelau')
INSERT INTO countries (name) VALUES('Tonga')
INSERT INTO countries (name) VALUES('Trinidad and Tobago')
INSERT INTO countries (name) VALUES('Tromelin Island')
INSERT INTO countries (name) VALUES('Tunisia')
INSERT INTO countries (name) VALUES('Turkey')
INSERT INTO countries (name) VALUES('Turkmenistan')
INSERT INTO countries (name) VALUES('Turks and Caicos Islands')
INSERT INTO countries (name) VALUES('Tuvalu')
INSERT INTO countries (name) VALUES('Uganda')
INSERT INTO countries (name) VALUES('Ukraine')
INSERT INTO countries (name) VALUES('United Arab Emirates')
INSERT INTO countries (name) VALUES('United Kingdom')
INSERT INTO countries (name) VALUES('United States of America')
INSERT INTO countries (name) VALUES('Uruguay')
INSERT INTO countries (name) VALUES('Uzbekistan')
INSERT INTO countries (name) VALUES('Vanuatu')
INSERT INTO countries (name) VALUES('Venezuela')
INSERT INTO countries (name) VALUES('Vietnam')
INSERT INTO countries (name) VALUES('Virgin Islands')
INSERT INTO countries (name) VALUES('Wake Island')
INSERT INTO countries (name) VALUES('Wallis and Futuna')
INSERT INTO countries (name) VALUES('West Bank')
INSERT INTO countries (name) VALUES('Western Sahara')
INSERT INTO countries (name) VALUES('Yemen')
INSERT INTO countries (name) VALUES('Zambia')
INSERT INTO countries (name) VALUES('Zimbabwe')
GO

INSERT INTO provinces (name, code, country_id_fk) VALUES('Alberta', 'AB', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('British Columbia', 'BC', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Manitoba', 'MB', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('New Brunswick', 'NB', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Newfoundland and Labrador', 'NL', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Northwest Territories', 'NT', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Nova Scotia', 'NS', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Nunavut', 'NU', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Ontario', 'ON', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Prince Edward Island', 'PE', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Quebec', 'QC', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Saskatchewan', 'SK', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Yukon', 'YT', 43)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Alabama', 'AL', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Alaska', 'AK', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Arizona', 'AZ', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Arkansas', 'AR', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('California', 'CA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Colorado', 'CO', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Connecticut', 'CT', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Delaware', 'DE', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Dist. of Columbia', 'DC', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Florida', 'FL', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Georgia', 'GA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Guam', 'GU', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Hawaii', 'HI', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Idaho', 'ID', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Illinois', 'IL', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Indiana', 'IN', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Iowa', 'IA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Kansas', 'KS', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Kentucky', 'KY', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Louisiana', 'LA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Maine', 'ME', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Maryland', 'MD', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Massachusetts', 'MA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Michigan', 'MI', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Minnesota', 'MN', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Mississippi', 'MS', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Missouri', 'MO', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Montana', 'MT', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Nebraska', 'NE', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Nevada', 'NV', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('New Hampshire', 'NH', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('New Jersey', 'NJ', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('New Mexico', 'NM', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('New York', 'NY', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('North Carolina', 'NC', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('North Dakota', 'ND', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Ohio', 'OH', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Oklahoma', 'OK', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Oregon', 'OR', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Pennsylvania', 'PA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Puerto Rico', 'PR', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Rhode Island', 'RI', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('South Carolina', 'SC', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('South Dakota', 'SD', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Tennessee', 'TN', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Texas', 'TX', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Utah', 'UT', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Vermont', 'VT', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Virginia', 'VA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Virgin Islands', 'VI', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Washington', 'WA', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('West Virginia', 'WV', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Wisconsin', 'WI', 252)
INSERT INTO provinces (name, code, country_id_fk) VALUES('Wyoming', 'WY', 252)
GO

INSERT INTO export_types (id, type) VALUES (1, 'DVD')
INSERT INTO export_types (id, type) VALUES (2, 'Drive')
GO

INSERT INTO footage_types (description) VALUES ('dss')
INSERT INTO footage_types (description) VALUES ('mpeg2')
INSERT INTO footage_types (description) VALUES ('wav')
GO

INSERT INTO personnel_type (description) VALUES ('Investigator')
INSERT INTO personnel_type (description) VALUES ('Transcriber')
GO
