# $Id$
#
# Authors:
#      Jeff Buchbinder <jeff@freemedsoftware.org>
#
# FreeMED Electronic Medical Record and Practice Management System
# Copyright (C) 1999-2012 FreeMED Software Foundation
#
# 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 2 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, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.

DROP TABLE IF EXISTS npi;

CREATE TABLE npi (
	  id CHAR (12) NOT NULL UNIQUE
	, entity_type_code ENUM ( '1', '2' ) NOT NULL
	, replacement_npi CHAR (12)
	, employer_identification_number VARCHAR (9)
	, provider_organization_name VARCHAR (200)
	, provider_last_name VARCHAR (100) 
	, provider_first_name VARCHAR (100)
	, provider_middle_name VARCHAR (100)
	, provider_name_prefix_text VARCHAR (7)
	, provider_name_suffix_text VARCHAR (7)
	, provider_credential_text VARCHAR (100)
	, provider_other_organization_name VARCHAR (200)
	, provider_other_organization_name_type_code ENUM ( '1', '2', '3', '4', '5' )
	, provider_other_last_name VARCHAR (100)
	, provider_other_first_name VARCHAR (100)
	, provider_other_middle_name VARCHAR (100)
	, provider_other_name_prefix_text VARCHAR (7)
	, provider_other_name_suffix_text VARCHAR (7)
	, provider_other_credential_text VARCHAR (100)
	, provider_other_last_name_type_code ENUM ( '1', '2', '3', '4', '5' )
	, provider_first_line_business_mailing_address VARCHAR (200)
	, provider_second_line_business_mailing_address VARCHAR (200)
	, provider_business_mailing_address_city_name VARCHAR (100)
	, provider_business_mailing_address_state_name CHAR (2)
	, provider_business_mailing_address_postal_code CHAR (6)
	, provider_business_mailing_address_country_code CHAR(2) NOT NULL DEFAULT 'US'
	, provider_business_mailing_address_telephone_number VARCHAR (30)
	, provider_business_mailing_address_fax_number VARCHAR (30)
	, provider_first_line_business_practice_location_address VARCHAR (200)
	, provider_second_line_business_practice_location_address VARCHAR (200)
	, provider_business_practice_location_address_city_name VARCHAR (100)
	, provider_business_practice_location_address_state_name CHAR (2)
	, provider_business_practice_location_address_postal_code CHAR (10)
	, provider_business_practice_location_address_country_code CHAR(2) NOT NULL DEFAULT 'US'
	, provider_business_practice_location_address_telephone_number VARCHAR (30)
	, provider_business_practice_location_address_fax_number VARCHAR (30)
	, provider_enumeration_date CHAR (10)
	, last_update_date CHAR (10)
	, npi_deactivation_reason_code ENUM ('DT', 'DB', 'FR', 'OT' )
	, npi_deactivation_date CHAR (10)
	, npi_reactivation_date CHAR (10)
	, provider_gender_code ENUM ( 'M', 'F' )
	, authorized_official_last_name VARCHAR (100)
	, authorized_official_first_name VARCHAR (100)
	, authorized_official_middle_name VARCHAR (100)
	, authorized_official_title_or_position VARCHAR (100)
	, authorized_official_telephone_number VARCHAR (30)
	, healthcare_provider_taxonomy_code_1 CHAR (10)
	, provider_license_number_1 CHAR (10)
	, provider_license_number_state_code_1 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_1 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_2 CHAR (10)
	, provider_license_number_2 CHAR (10)
	, provider_license_number_state_code_2 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_2 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_3 CHAR (10)
	, provider_license_number_3 CHAR (10)
	, provider_license_number_state_code_3 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_3 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_4 CHAR (10)
	, provider_license_number_4 CHAR (10)
	, provider_license_number_state_code_4 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_4 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_5 CHAR (10)
	, provider_license_number_5 CHAR (10)
	, provider_license_number_state_code_5 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_5 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_6 CHAR (10)
	, provider_license_number_6 CHAR (10)
	, provider_license_number_state_code_6 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_6 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_7 CHAR (10)
	, provider_license_number_7 CHAR (10)
	, provider_license_number_state_code_7 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_7 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_8 CHAR (10)
	, provider_license_number_8 CHAR (10)
	, provider_license_number_state_code_8 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_8 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_9 CHAR (10)
	, provider_license_number_9 CHAR (10)
	, provider_license_number_state_code_9 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_9 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_10 CHAR (10)
	, provider_license_number_10 CHAR (10)
	, provider_license_number_state_code_10 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_10 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_11 CHAR (10)
	, provider_license_number_11 CHAR (10)
	, provider_license_number_state_code_11 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_11 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_12 CHAR (10)
	, provider_license_number_12 CHAR (10)
	, provider_license_number_state_code_12 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_12 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_13 CHAR (10)
	, provider_license_number_13 CHAR (10)
	, provider_license_number_state_code_13 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_13 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_14 CHAR (10)
	, provider_license_number_14 CHAR (10)
	, provider_license_number_state_code_14 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_14 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, healthcare_provider_taxonomy_code_15 CHAR (10)
	, provider_license_number_15 CHAR (10)
	, provider_license_number_state_code_15 CHAR(2)
	, healthcare_provider_primary_taxonomy_switch_15 ENUM( 'X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, other_provider_identifier_1 VARCHAR (30)
	, other_provider_identifier_type_code_1 CHAR (2)
	, other_provider_identifier_state_1 CHAR (2)
	, other_provider_identifier_issuer_1 CHAR (2)
	, other_provider_identifier_2 VARCHAR (30)
	, other_provider_identifier_type_code_2 CHAR (2)
	, other_provider_identifier_state_2 CHAR (2)
	, other_provider_identifier_issuer_2 CHAR (2)
	, other_provider_identifier_3 VARCHAR (30)
	, other_provider_identifier_type_code_3 CHAR (2)
	, other_provider_identifier_state_3 CHAR (2)
	, other_provider_identifier_issuer_3 CHAR (2)
	, other_provider_identifier_4 VARCHAR (30)
	, other_provider_identifier_type_code_4 CHAR (2)
	, other_provider_identifier_state_4 CHAR (2)
	, other_provider_identifier_issuer_4 CHAR (2)
	, other_provider_identifier_5 VARCHAR (30)
	, other_provider_identifier_type_code_5 CHAR (2)
	, other_provider_identifier_state_5 CHAR (2)
	, other_provider_identifier_issuer_5 CHAR (2)
	, other_provider_identifier_6 VARCHAR (30)
	, other_provider_identifier_type_code_6 CHAR (2)
	, other_provider_identifier_state_6 CHAR (2)
	, other_provider_identifier_issuer_6 CHAR (2)
	, other_provider_identifier_7 VARCHAR (30)
	, other_provider_identifier_type_code_7 CHAR (2)
	, other_provider_identifier_state_7 CHAR (2)
	, other_provider_identifier_issuer_7 CHAR (2)
	, other_provider_identifier_8 VARCHAR (30)
	, other_provider_identifier_type_code_8 CHAR (2)
	, other_provider_identifier_state_8 CHAR (2)
	, other_provider_identifier_issuer_8 CHAR (2)
	, other_provider_identifier_9 VARCHAR (30)
	, other_provider_identifier_type_code_9 CHAR (2)
	, other_provider_identifier_state_9 CHAR (2)
	, other_provider_identifier_issuer_9 CHAR (2)
	, other_provider_identifier_10 VARCHAR (30)
	, other_provider_identifier_type_code_10 CHAR (2)
	, other_provider_identifier_state_10 CHAR (2)
	, other_provider_identifier_issuer_10 CHAR (2)
	, other_provider_identifier_11 VARCHAR (30)
	, other_provider_identifier_type_code_11 CHAR (2)
	, other_provider_identifier_state_11 CHAR (2)
	, other_provider_identifier_issuer_11 CHAR (2)
	, other_provider_identifier_12 VARCHAR (30)
	, other_provider_identifier_type_code_12 CHAR (2)
	, other_provider_identifier_state_12 CHAR (2)
	, other_provider_identifier_issuer_12 CHAR (2)
	, other_provider_identifier_13 VARCHAR (30)
	, other_provider_identifier_type_code_13 CHAR (2)
	, other_provider_identifier_state_13 CHAR (2)
	, other_provider_identifier_issuer_13 CHAR (2)
	, other_provider_identifier_14 VARCHAR (30)
	, other_provider_identifier_type_code_14 CHAR (2)
	, other_provider_identifier_state_14 CHAR (2)
	, other_provider_identifier_issuer_14 CHAR (2)
	, other_provider_identifier_15 VARCHAR (30)
	, other_provider_identifier_type_code_15 CHAR (2)
	, other_provider_identifier_state_15 CHAR (2)
	, other_provider_identifier_issuer_15 CHAR (2)
	, other_provider_identifier_16 VARCHAR (30)
	, other_provider_identifier_type_code_16 CHAR (2)
	, other_provider_identifier_state_16 CHAR (2)
	, other_provider_identifier_issuer_16 CHAR (2)
	, other_provider_identifier_17 VARCHAR (30)
	, other_provider_identifier_type_code_17 CHAR (2)
	, other_provider_identifier_state_17 CHAR (2)
	, other_provider_identifier_issuer_17 CHAR (2)
	, other_provider_identifier_18 VARCHAR (30)
	, other_provider_identifier_type_code_18 CHAR (2)
	, other_provider_identifier_state_18 CHAR (2)
	, other_provider_identifier_issuer_18 CHAR (2)
	, other_provider_identifier_19 VARCHAR (30)
	, other_provider_identifier_type_code_19 CHAR (2)
	, other_provider_identifier_state_19 CHAR (2)
	, other_provider_identifier_issuer_19 CHAR (2)
	, other_provider_identifier_20 VARCHAR (30)
	, other_provider_identifier_type_code_20 CHAR (2)
	, other_provider_identifier_state_20 CHAR (2)
	, other_provider_identifier_issuer_20 CHAR (2)
	, other_provider_identifier_21 VARCHAR (30)
	, other_provider_identifier_type_code_21 CHAR (2)
	, other_provider_identifier_state_21 CHAR (2)
	, other_provider_identifier_issuer_21 CHAR (2)
	, other_provider_identifier_22 VARCHAR (30)
	, other_provider_identifier_type_code_22 CHAR (2)
	, other_provider_identifier_state_22 CHAR (2)
	, other_provider_identifier_issuer_22 CHAR (2)
	, other_provider_identifier_23 VARCHAR (30)
	, other_provider_identifier_type_code_23 CHAR (2)
	, other_provider_identifier_state_23 CHAR (2)
	, other_provider_identifier_issuer_23 CHAR (2)
	, other_provider_identifier_24 VARCHAR (30)
	, other_provider_identifier_type_code_24 CHAR (2)
	, other_provider_identifier_state_24 CHAR (2)
	, other_provider_identifier_issuer_24 CHAR (2)
	, other_provider_identifier_25 VARCHAR (30)
	, other_provider_identifier_type_code_25 CHAR (2)
	, other_provider_identifier_state_25 CHAR (2)
	, other_provider_identifier_issuer_25 CHAR (2)
	, other_provider_identifier_26 VARCHAR (30)
	, other_provider_identifier_type_code_26 CHAR (2)
	, other_provider_identifier_state_26 CHAR (2)
	, other_provider_identifier_issuer_26 CHAR (2)
	, other_provider_identifier_27 VARCHAR (30)
	, other_provider_identifier_type_code_27 CHAR (2)
	, other_provider_identifier_state_27 CHAR (2)
	, other_provider_identifier_issuer_27 CHAR (2)
	, other_provider_identifier_28 VARCHAR (30)
	, other_provider_identifier_type_code_28 CHAR (2)
	, other_provider_identifier_state_28 CHAR (2)
	, other_provider_identifier_issuer_28 CHAR (2)
	, other_provider_identifier_29 VARCHAR (30)
	, other_provider_identifier_type_code_29 CHAR (2)
	, other_provider_identifier_state_29 CHAR (2)
	, other_provider_identifier_issuer_29 CHAR (2)
	, other_provider_identifier_30 VARCHAR (30)
	, other_provider_identifier_type_code_30 CHAR (2)
	, other_provider_identifier_state_30 CHAR (2)
	, other_provider_identifier_issuer_30 CHAR (2)
	, other_provider_identifier_31 VARCHAR (30)
	, other_provider_identifier_type_code_31 CHAR (2)
	, other_provider_identifier_state_31 CHAR (2)
	, other_provider_identifier_issuer_31 CHAR (2)
	, other_provider_identifier_32 VARCHAR (30)
	, other_provider_identifier_type_code_32 CHAR (2)
	, other_provider_identifier_state_32 CHAR (2)
	, other_provider_identifier_issuer_32 CHAR (2)
	, other_provider_identifier_33 VARCHAR (30)
	, other_provider_identifier_type_code_33 CHAR (2)
	, other_provider_identifier_state_33 CHAR (2)
	, other_provider_identifier_issuer_33 CHAR (2)
	, other_provider_identifier_34 VARCHAR (30)
	, other_provider_identifier_type_code_34 CHAR (2)
	, other_provider_identifier_state_34 CHAR (2)
	, other_provider_identifier_issuer_34 CHAR (2)
	, other_provider_identifier_35 VARCHAR (30)
	, other_provider_identifier_type_code_35 CHAR (2)
	, other_provider_identifier_state_35 CHAR (2)
	, other_provider_identifier_issuer_35 CHAR (2)
	, other_provider_identifier_36 VARCHAR (30)
	, other_provider_identifier_type_code_36 CHAR (2)
	, other_provider_identifier_state_36 CHAR (2)
	, other_provider_identifier_issuer_36 CHAR (2)
	, other_provider_identifier_37 VARCHAR (30)
	, other_provider_identifier_type_code_37 CHAR (2)
	, other_provider_identifier_state_37 CHAR (2)
	, other_provider_identifier_issuer_37 CHAR (2)
	, other_provider_identifier_38 VARCHAR (30)
	, other_provider_identifier_type_code_38 CHAR (2)
	, other_provider_identifier_state_38 CHAR (2)
	, other_provider_identifier_issuer_38 CHAR (2)
	, other_provider_identifier_39 VARCHAR (30)
	, other_provider_identifier_type_code_39 CHAR (2)
	, other_provider_identifier_state_39 CHAR (2)
	, other_provider_identifier_issuer_39 CHAR (2)
	, other_provider_identifier_40 VARCHAR (30)
	, other_provider_identifier_type_code_40 CHAR (2)
	, other_provider_identifier_state_40 CHAR (2)
	, other_provider_identifier_issuer_40 CHAR (2)
	, other_provider_identifier_41 VARCHAR (30)
	, other_provider_identifier_type_code_41 CHAR (2)
	, other_provider_identifier_state_41 CHAR (2)
	, other_provider_identifier_issuer_41 CHAR (2)
	, other_provider_identifier_42 VARCHAR (30)
	, other_provider_identifier_type_code_42 CHAR (2)
	, other_provider_identifier_state_42 CHAR (2)
	, other_provider_identifier_issuer_42 CHAR (2)
	, other_provider_identifier_43 VARCHAR (30)
	, other_provider_identifier_type_code_43 CHAR (2)
	, other_provider_identifier_state_43 CHAR (2)
	, other_provider_identifier_issuer_43 CHAR (2)
	, other_provider_identifier_44 VARCHAR (30)
	, other_provider_identifier_type_code_44 CHAR (2)
	, other_provider_identifier_state_44 CHAR (2)
	, other_provider_identifier_issuer_44 CHAR (2)
	, other_provider_identifier_45 VARCHAR (30)
	, other_provider_identifier_type_code_45 CHAR (2)
	, other_provider_identifier_state_45 CHAR (2)
	, other_provider_identifier_issuer_45 CHAR (2)
	, other_provider_identifier_46 VARCHAR (30)
	, other_provider_identifier_type_code_46 CHAR (2)
	, other_provider_identifier_state_46 CHAR (2)
	, other_provider_identifier_issuer_46 CHAR (2)
	, other_provider_identifier_47 VARCHAR (30)
	, other_provider_identifier_type_code_47 CHAR (2)
	, other_provider_identifier_state_47 CHAR (2)
	, other_provider_identifier_issuer_47 CHAR (2)
	, other_provider_identifier_48 VARCHAR (30)
	, other_provider_identifier_type_code_48 CHAR (2)
	, other_provider_identifier_state_48 CHAR (2)
	, other_provider_identifier_issuer_48 CHAR (2)
	, other_provider_identifier_49 VARCHAR (30)
	, other_provider_identifier_type_code_49 CHAR (2)
	, other_provider_identifier_state_49 CHAR (2)
	, other_provider_identifier_issuer_49 CHAR (2)
	, other_provider_identifier_50 VARCHAR (30)
	, other_provider_identifier_type_code_50 CHAR (2)
	, other_provider_identifier_state_50 CHAR (2)
	, other_provider_identifier_issuer_50 CHAR (2)
	, is_sole_proprietor ENUM ('X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, is_organization_subpart ENUM ('X', 'Y', 'N' ) NOT NULL DEFAULT 'X'
	, parent_organization_lbn VARCHAR (100)
	, parent_organization_tin CHAR (11)
	, authorized_official_name_prefix_text VARCHAR (7)
	, authorized_official_name_suffix_text VARCHAR (7)
	, authorized_official_credential_text VARCHAR (200)

	, PRIMARY KEY ( id )
	, INDEX ( provider_organization_name )
	, INDEX ( provider_last_name, provider_first_name )
);

LOAD DATA LOCAL INFILE "data/source/npi/npi.csv"
	INTO TABLE npi
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;

