/* accession */
create table accession (
	accession_number     char(10)     not null,
	agent_id             int          not null,
	accession_date       datetime     null,
	dead_date            datetime     null,
	dead_flag            tinyint      not null,
	material_type        varchar(20)  null,
	plant_status         char(1)      null,
	data_quality         tinyint      not null,
	material_source      varchar(100) null,
	collector_number     char(10)     null,
	collection_date      varchar(60)  null,
	begin_date           julianday    null,
	end_date             julianday    null,
	accession_notes      varchar(255) null,
	date_entered         datetime     null,
	last_change_date     datetime     null,
	last_change_agent_id int          null,
	collector_id         int          not null,
	lexicon_id           int          not null,
	requested_by         int          not null,
	system_use           varchar(10)  null,
	fhp_lexicon_id       int          not null,
	mhp_lexicon_id       int          not null,
	aff_lexicon_id       int          not null,
	hybrid_flag          tinyint      not null,
	cbd_restrict_flag    boolean      not null,
	more_notes_flag      tinyint      not null
)
go

sp_bindefault zero_default, 'accession.dead_flag'
go

sp_bindefault zero_default, 'accession.agent_id'
go

sp_bindefault true_default, 'accession.data_quality'
go

sp_bindefault zero_default, 'accession.last_change_agent_id'
go

sp_bindefault date_default, 'accession.date_entered'
go

sp_bindefault date_default, 'accession.last_change_date'
go

sp_bindefault zero_default, 'accession.requested_by'
go

sp_bindefault zero_default, 'accession.collector_id'
go

sp_bindefault napp_default, 'accession.fhp_lexicon_id'
go

sp_bindefault napp_default, 'accession.mhp_lexicon_id'
go

sp_bindefault napp_default, 'accession.aff_lexicon_id'
go

sp_bindefault zero_default, 'accession.hybrid_flag'
go

sp_bindefault zero_default, 'accession.cbd_restrict_flag'
go

sp_bindefault zero_default, 'accession.more_notes_flag'
go

sp_bindrule boolean_rule, 'accession.dead_flag'
go

sp_bindrule boolean_rule, 'accession.hybrid_flag'
go

sp_bindrule boolean_rule, 'accession.cbd_restrict_flag'
go

sp_bindrule boolean_rule, 'accession.more_notes_flag'
go

sp_bindrule data_quality_rule, 'accession.data_quality'
go

sp_foreignkey plant_identity, accession, accession_number
go

sp_foreignkey accession_activity, accession, accession_number
go

sp_foreignkey voucher, accession, accession_number
go

sp_foreignkey plant_location, accession, accession_number
go

sp_foreignkey accession, staff, agent_id
go

sp_foreignkey accession, lexicon, lexicon_id
go

sp_foreignkey accession, lexicon, fhp_lexicon_id
go

sp_foreignkey accession, lexicon, mhp_lexicon_id
go

sp_foreignkey accession, lexicon, aff_lexicon_id
go

sp_foreignkey accession, person, requested_by
go

sp_foreignkey accession, collector_code, collector_id
go

sp_foreignkey accession, accession_notes, accession_number
go

create unique clustered index PKaccession on accession (accession_number)
go

create index IF1accession on accession (agent_id)
go

create index IF2accession on accession (lexicon_id)
go

create index IF3accession on accession (requested_by)
go

create index IF4accession on accession (fhp_lexicon_id)
go

create index IF5accession on accession (mhp_lexicon_id)
go

create index IF6accession on accession (aff_lexicon_id)
go

create index IF7accession on accession (
	accession_number,
	lexicon_id,
	fhp_lexicon_id,
	mhp_lexicon_id,
	aff_lexicon_id
)
go

create index IF11accession on accession (collector_id)
go


/* garden_location */
create table garden_location (
	garden_loc_id           key_t           not null,
	loc_type                varchar(10)     not null,
	loc_name                varchar(50)     not null,
	loc_descr               varchar(255)    null,
	loc_abbrev              varchar(4)      null,
	parent_id               key_t           not null,
	loc_hier                varchar(255)    null,
	full_loc_name           varchar(255)    null,
	entered_staff_id        int             not null,
	date_entered            datetime        not null,
	last_change_staff_id    int             not null,
	last_change_date        datetime        not null
)
go

sp_primarykey garden_location, garden_loc_id
go

/*append dummy record for locations with no parent */
insert garden_location (
	garden_loc_id,
	loc_type,
	loc_name, 
	loc_abbrev,
	parent_id,
	full_loc_name)
values (0,
	'collection',
	'none',
	'',
	-1,
	'none')
go

sp_bindefault zero_default, 'garden_location.entered_staff_id'
go

sp_bindefault zero_default, 'garden_location.last_change_staff_id'
go

sp_bindefault date_default, 'garden_location.date_entered'
go

sp_bindefault date_default, 'garden_location.last_change_date'
go

sp_bindefault null_par_default, 'garden_location.loc_name'
go

sp_bindrule loc_type_rule, 'garden_location.loc_type'
go

sp_foreignkey accession_activity, garden_location, garden_loc_id
go

sp_foreignkey plant_location, garden_location, garden_loc_id
go

sp_foreignkey prop_destination, garden_location, garden_loc_id
go

sp_foreignkey garden_location, staff, entered_staff_id
go

sp_foreignkey garden_location, staff, last_change_staff_id
go

create unique clustered index PKgarden_location on garden_location (garden_loc_id)
go

create index AK1garden_location on garden_location (parent_id)
go

create index IF1garden_location on garden_location (entered_staff_id)
go

create index IF2garden_location on garden_location (last_change_staff_id)
go


/* plant_location */
create table plant_location (
	accession_number        char(10)        not null,
	garden_loc_id           int             not null,
	action_date             datetime        not null,
	action_code             int             not null,
	label_req_flag          int             not null,
	comments                varchar(255)    null,
	label_size              char(5)         null,
	stand_type              char(5)         null,
	label_count             tinyint         null,
	entered_staff_id        int             not null,
	date_entered            datetime        not null,
	last_change_staff_id    int             not null,
	last_change_date        datetime        not null
)
go

create unique clustered index XPKplant_location on plant_location (
	accession_number, garden_loc_id, action_date, action_code
)
go

create index IF1plant_location on plant_location (entered_staff_id)
go

create index IF2plant_location on plant_location (last_change_staff_id)
go

create index IF3plant_location on plant_location (accession_number)
go

create index IF4plant_location on plant_location (garden_loc_id)
go

sp_primarykey plant_location, 
	accession_number,
	garden_loc_id,
	action_date,
	action_code
go

sp_bindrule action_code_rule, 'plant_location.action_code'
go

sp_bindrule label_size_rule, 'plant_location.label_size'
go

sp_bindrule stand_type_rule, 'plant_location.stand_type'
go

sp_bindefault zero_default, 'plant_location.entered_staff_id'
go

sp_bindefault zero_default, 'plant_location.last_change_staff_id'
go

sp_bindefault date_default, 'plant_location.date_entered'
go

sp_bindefault date_default, 'plant_location.last_change_date'
go

sp_bindefault zero_default, 'plant_location.label_req_flag'
go

sp_bindefault zero_default, 'plant_location.label_count'
go

sp_bindefault true_default, 'plant_location.garden_loc_id'
go

sp_foreignkey plant_location, garden_location, garden_loc_id
go

sp_foreignkey plant_location, accession, accession_number
go

sp_foreignkey plant_location, staff, entered_staff_id
go

sp_foreignkey plant_location, staff, last_change_staff_id
go
		

/* person */
CREATE TABLE person (
	person_id               key_t NOT NULL,
	prefix                  varchar(20) NULL,
	last_name               varchar(60) NOT NULL,
	first_name              varchar(80) NULL,
	middle_name             varchar(40) NULL,
	suffix                  varchar(20) NULL,
	birth_date              datetime NULL,
	death_date              datetime NULL,
	person_remark           varchar(255) NULL,
	data_source_id          int NOT NULL,
	entered_staff_id        int NOT NULL,
	date_entered            datetime NOT NULL,
	last_change_staff_id    int NOT NULL,
	last_change_date        datetime NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX XPKperson ON person (
	person_id,
	last_name,
	first_name,
	middle_name,
	birth_date
)
go

CREATE UNIQUE INDEX AK1person ON person (
        last_name,
	first_name,
	middle_name,
	birth_date,
	person_id
)
go

CREATE INDEX XIF1person ON person (entered_staff_id)
go

CREATE INDEX XIF2person ON person (last_change_staff_id)
go

exec sp_primarykey person, person_id
go

sp_bindefault zero_default, 'person.data_source_id'
go

sp_bindefault zero_default, 'person.entered_staff_id'
go

sp_bindefault zero_default, 'person.last_change_staff_id'
go

sp_bindefault date_default, 'person.date_entered'
go

sp_bindefault date_default, 'person.last_change_date'
go

exec sp_foreignkey person, agent, person_id
go

exec sp_foreignkey person, staff, entered_staff_id
go

exec sp_foreignkey person, staff, last_change_staff_id
go


/* organization */
CREATE TABLE organization (
	org_id                  key_t NOT NULL,
	org_name                varchar(220) NOT NULL,
	org_remark              varchar(255) NULL,
	data_source_id          int NOT NULL,
	dept_unit               varchar(120) NULL,
	entered_staff_id        int NOT NULL,
	date_entered            datetime NOT NULL,
	last_change_staff_id    int NOT NULL,
	last_change_date        datetime NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX PKorganization ON organization (org_id, org_name)
go

CREATE UNIQUE INDEX AK1organization ON organization (org_name, org_id)
go

CREATE INDEX F1organization ON organization (entered_staff_id)
go

CREATE INDEX F2organization ON organization (last_change_staff_id)
go

exec sp_primarykey organization, org_id
go

sp_bindefault zero_default, 'organization.data_source_id'
go

sp_bindefault zero_default, 'organization.entered_staff_id'
go

sp_bindefault zero_default, 'organization.last_change_staff_id'
go

sp_bindefault date_default, 'organization.date_entered'
go

sp_bindefault date_default, 'organization.last_change_date'
go

exec sp_foreignkey organization, agent, org_id
go

exec sp_foreignkey organization, staff, entered_staff_id
go

exec sp_foreignkey organization, staff, last_change_staff_id
go


/* lexicon */
CREATE TABLE lexicon (
	lexicon_id              key_t NOT NULL,
	term                    varchar(220) NOT NULL,
	node_type               varchar(10) NULL,
	differentiator          varchar(40) NULL,
	access_code             int NOT NULL,
	valid_catalog_term_flag bit,
	agent_id                int NOT NULL,
	date_entered            datetime NOT NULL,
	last_change_agent_id    int NOT NULL,
	last_change_date        datetime NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX pk_lexicon ON lexicon (lexicon_id, term, node_type)
go

CREATE UNIQUE INDEX nc1_lexicon ON lexicon (term, lexicon_id, node_type)
go

CREATE UNIQUE INDEX lexicon_unc ON lexicon (lexicon_id)
go

CREATE INDEX if39_lexicon ON lexicon (node_type)
go

exec sp_primarykey lexicon, lexicon_id
go

exec sp_bindefault true_default, 'lexicon.valid_catalog_term_flag'
go

exec sp_bindefault zero_default, 'lexicon.access_code'
go

sp_bindefault zero_default, 'lexicon.agent_id'
go

sp_bindefault date_default, 'lexicon.date_entered'
go

sp_bindefault zero_default, 'lexicon.last_change_agent_id'
go

sp_bindefault date_default, 'lexicon.last_change_date'
go

sp_bindrule access_code_rule, 'lexicon.access_code'
go

exec sp_foreignkey lexicon_term_relation, lexicon, related_lexicon_id
go

exec sp_foreignkey lexicon_term_relation, lexicon, lexicon_id
go

exec sp_foreignkey plant_identity, lexicon, lexicon_id
go

exec sp_foreignkey lexicon_sort_order, lexicon, lexicon_id
go

exec sp_foreignkey scope_notes, lexicon, lexicon_id
go

exec sp_foreignkey lexicon_term_tokens, lexicon, lexicon_id
go

