/*billing_data (replaces billing_data_archive. The original billing_data table will no longer be needed as a temp table.)*/
/*cpc data precision for all tables will now be (7,6) because there is never a non-zero digit before the decimal in current data. By
	lowering the first number in the declaration, only 7 digits total will be allowed, with the most following the decimal being 6. This
	will prevent the insertion of very erroneous data such as 9999.999999 as there is already is > 10 records.*/
/*Tables billing_report_todo and billing_report_completed were combined, as discussed, with the additional column as a flag to mark
	whether billing_report_completed or not. billing_completed CHAR(1). */

CREATE TABLE organizations
(org_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_name VARCHAR(50),
org_short_name VARCHAR(15),
org_type_id INT(2) COMMENT "FK to organization_type.id",
website VARCHAR(100),
last_update DATE,
org_comments TEXT,
client_since DATE,
client_status CHAR(1) COMMENT "Is the client an active client? A= Active P= Prospective I=Inactive N=NonActive",
print_mgmt_software_installed CHAR(1) COMMENT "Is the client active on stardoc? Y=Yes N=No",
lenp_contract_signed CHAR(1) COMMENT "Y=Yes N=No",
fmaudit_client_id INT(11),
commencement_date DATE,
display_meter_data CHAR(1) COMMENT "Y=Yes N=No",
meter_data_feed VARCHAR(10),
client_logo VARCHAR(30), /*Current max length = 17*/
gm_notes VARCHAR(300), /*Current max length = 108*/
org_message VARCHAR(300), /*Current max length = 106*/
bank_buyout DECIMAL(11,2),
spc_service_fee DECIMAL(11,2),
trade_out_deletion INT(5), /*Current max length = 2*/
new_upgrade_date DATE,
temp_inactive CHAR(1) DEFAULT "N" COMMENT "Y means temporarily inactive during upgrade. Defaults to N for No. Used in reports.",
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=2000; /*Max 1874 currently*/ 


/*This data is all from organization table*/
CREATE TABLE client_acquisition_data
(cad_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_id INT(11),
survey_date DATE COMMENT "This is when the org machines and locations were surveyed to become a client or have an upgrade",
down_payment DECIMAL(9,2),
prior_volume INT(15) COMMENT "This is the volume that the org used BEFORE coming to SPC",
prior_cpc DECIMAL(7,6) COMMENT "This is the cpc that the org paid BEFORE coming to SPC",
end_contract_date DATE COMMENT "This relates to the end of the Service/Supply Contracts and NOT the lease. This date is ALWAYS 6/30/20xx.",
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11)); /*NEW TABLE, SO NO NEED FOR SETTING THE AUTO_INCREMENT*/


CREATE TABLE addresses /*Last create and migrate 1-11-2020 */
(address_id INT(11) AUTO_INCREMENT PRIMARY KEY,
address_type CHAR(1) COMMENT "M=Mailing L=Legal B=Both (meaning this address is the only one so it is both Legal and Mailing). ",
owner_type CHAR(1) COMMENT "The owner of the address can be an org or person O=org, P=person, B=Building",
owner_id INT(11) COMMENT "This will be the id of the org or the id of the person who is located at this address.",
address1 VARCHAR(50) COMMENT "This will be the street address or a po box.",
address2 VARCHAR(50) COMMENT "A second address line for when it is needed.",
city VARCHAR(50),
state CHAR(2) COMMENT "The 2 letter abbrev for the state.",
zip_code VARCHAR(10) COMMENT "This will be a 5 digit zip with possibly the +4 added.",
county VARCHAR(25), /*Current max is 12*/
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11)); /*NEW TABLE, SO NO NEED FOR SETTING THE AUTO_INCREMENT*/


/*Dropped account_number from orig because there were no values for this varchar(50) col. If needed at a later date, we can
add it back in at that time. */
CREATE TABLE departments
(dept_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_id INT(11),
dept_name VARCHAR(45),
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11)) #max length currently = 41
AUTO_INCREMENT=250; /*Current max = 233  Set this to 1 > the highest value currently existing in this column*/

CREATE TABLE buildings
(bldg_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_id INT(11),
bldg_name VARCHAR(40),
building_name_short VARCHAR(25) COMMENT "This is for use in graphs so the name will fit",
bldg_contact_id INT(11) COMMENT "Building contact person link to contact table id.",
tech_contact_id INT(11)COMMENT "IT contact person link to contact table id.",
meter_contact_id INT(11)COMMENT "Meter reader contact person link to contact table id.",
student_pop INT(11) COMMENT "The student population for this building",
bldg_equip_cost DECIMAL(11, 2) COMMENT "A field where Pam manually enters data.",
notes TEXT,
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=1500; /*Current max = 1456*/


CREATE TABLE rooms
(room_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_id INT(11),
bldg_id INT(11),
floor_num INT(11) DEFAULT NULL,
room_name VARCHAR(250),
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11)); /*NEW TABLE, SO NO NEED FOR SETTING THE AUTO_INCREMENT*/


CREATE TABLE phones
(phone_id INT(11)  AUTO_INCREMENT PRIMARY KEY,
phone_type CHAR(1) COMMENT "Type of number M=Main, C=Cell, F=Fax, A=Additional",
owner_type CHAR(1) COMMENT "The owner of the number can be an org, person, or building O=org, P=person, B=building",
owner_id INT(11) COMMENT "The id of the org or the id of the person to whom the number belongs.",
area_code CHAR(3) COMMENT "The 3 digit area code with no hyphens () or special chars",
exchange CHAR(3) COMMENT "The 3 digit phone exchange with no hyphens () or special chars",
phone_line CHAR(4) COMMENT "The last 4 digits of the phone number are the actual phone line",
extension CHAR(8) COMMENT "If there is a specific extension, it will be here."
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11)); /*NEW TABLE, SO NO NEED FOR SETTING THE AUTO_INCREMENT*/


/*Replaces tblContacts*/
CREATE TABLE contacts
(contact_id INT(15) AUTO_INCREMENT PRIMARY KEY,
building_id INT(11),
org_id INT(11),
last_name VARCHAR(25),
first_name VARCHAR(26),
email_address VARCHAR(100),
job_title VARCHAR(50),
decision_maker CHAR(1),
notes TEXT,
it_email_cont INT(2),
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=xx; /*Current max = 4448 Set this to 1 > the highest value currently existing in this column*/



CREATE TABLE current_devices
(device_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_id INT(11),
bldg_id INT(11),
dept_id INT(11),
room_id  INT(11),
school_year INT(2),
vendor_id INT(11),
vendor_mach_id VARCHAR(25),
model_id INT(11),
features_list TEXT,
serial_number VARCHAR(25),
mach_condition CHAR(1) COMMENT "N= New, R= Reconditioned", #orig condition
install_date datetime, #orig install
projected_vol_black INT(15), #orig Anticipated
cpc_black DECIMAL(7,6), #orig CostCopy
new_cpc_black DECIMAL(7,6), #orig NewCostCopy
meter_begin_date datetime, #orig BDate
meter_begin_read_black INT(15), #orig Begin
meter_end_date datetime, #orig EDate
meter_end_read_black INT(15), #orig End
meter_begin_read_color INT(15), #orig MstrBegin
meter_end_read_color INT(15), #orig MstrEnd
cpc_color DECIMAL(7,6), #orig Mstrcpc
new_cpc_color DECIMAL(7,6), #orig NewColorCPC
projected_vol_color INT(15), #orig ProjColorVol
device_comments TEXT,
lease_own CHAR(1) COMMENT "Type of possession. L= Leased, O= Owned, R= Rental, L= Loaner",
cpc_black_vendor DECIMAL(7,6), #orig Black_Vendor_cpc
cpc_color_vendor DECIMAL(7,6), #orig Color_Vendor_cpc
new_cpc_black_vendor DECIMAL(7,6), #orig New_Blk_Ven_cpc
new_cpc_color_vendor DECIMAL(7,6), #orig New_Color_Ven_cpc
replacement_for VARCHAR(25), #orig txtReplacedMachine
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=35000; /*Current max = 34619*/

CREATE TABLE machine_archive
(device_id INT(11) AUTO_INCREMENT PRIMARY KEY,
org_id INT(11),
bldg_id INT(11),
dept_id INT(11),
room_id  INT(11),
school_year INT(2),
vendor_id INT(11),
vendor_mach_id VARCHAR(25),
model_id INT(11),
features_list TEXT,
serial_number VARCHAR(25),
mach_condition CHAR(1) COMMENT "N= New, R= Reconditioned", #orig condition
install_date datetime, #orig install
projected_vol_black INT(15), #orig Anticipated
cpc_black DECIMAL(7,6), #orig CostCopy
new_cpc_black DECIMAL(7,6), #orig NewCostCopy
meter_begin_date datetime, #orig BDate
meter_begin_read_black INT(15), #orig Begin
meter_end_date datetime, #orig EDate
meter_end_read_black INT(15), #orig End
meter_begin_read_color INT(15), #orig MstrBegin
meter_end_read_color INT(15), #orig MstrEnd
cpc_color DECIMAL(7,6), #orig Mstrcpc
new_cpc_color DECIMAL(7,6), #orig NewColorCPC
projected_vol_color INT(15), #orig ProjColorVol
device_comments TEXT,
lease_own CHAR(1) COMMENT "Type of possession. L= Leased, O= Owned, R= Rental, L= Loaner",
cpc_black_vendor DECIMAL(7,6), #orig Black_Vendor_cpc
cpc_color_vendor DECIMAL(7,6), #orig Color_Vendor_cpc
new_cpc_black_vendor DECIMAL(7,6), #orig New_Blk_Ven_cpc
new_cpc_color_vendor DECIMAL(7,6), #orig New_Color_Ven_cpc
replacement_for VARCHAR(25), #orig txtReplacedMachine
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=35000; /*Current max = 34619*/


CREATE TABLE machines
(machine_id INT(11)  AUTO_INCREMENT PRIMARY KEY,
make VARCHAR(40), /*current max = 27*/
model VARCHAR(40), /*current max = 30*/
machine_type INT(3), /*current max = 2*/
features VARCHAR(200),
machine_image VARCHAR(55), /*current max = 50*/
intro_date datetime,
life INT(11), /*current max = 7*/
min_speed INT(3), /*current max = 7*/
max_speed INT(3), /*current max = 3*/
meter_read_directions TEXT,
is_color CHAR(1) COMMENT "Y=yes, N=no",
auto_created CHAR(1) COMMENT "Y=yes, N=no",
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=3300; /*Current max = 3116*/

CREATE TABLE machine_types
(machine_type_id INT(11) AUTO_INCREMENT PRIMARY KEY,
type_name VARCHAR(50),
machine_type VARCHAR(25),
icon_type VARCHAR(30) COMMENT "Type of image for devices on floorplan",
is_color CHAR(1) COMMENT "Is this a color device? Y= Yes N= No",
covered CHAR(1) COMMENT "Is this device covered by SPC? Y= Yes N= No",
black_cpc_markup DOUBLE,
color_cpc_markup DOUBLE,
printer_copier CHAR(1) COMMENT "Is this device a printer or copier? P= Printer C= Copier"),
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11)
AUTO_INCREMENT=35; /*Current max = 31  Set this to 1 > the highest value currently existing in this column*/

CREATE TABLE floorplan_machines
(fpm_id INT(11)  AUTO_INCREMENT PRIMARY KEY,
fm_audit_id INT(11) COMMENT "FK for machines in FM Audits records.",
model_id INT(11) COMMENT "FK machines.machines_id for Proposed Machines ",
present_model_id INT(11) COMMENT "FK machines.machines_id for machines currently being used.",
x_position INT(11) COMMENT "Proposed X Position of machine on floorplan map.",
y_position INT(11) COMMENT "Proposed Y Position of machine on floorplan map.",
floorplan_id INT(11) COMMENT "FK Floorplan table.",
dept_id INT(11),
room_name VARCHAR(40), #Kept as is for now. Look at moving to rooms and replacing this with a room_id later
serial_number VARCHAR(25) COMMENT "Proposed serial number.",
mac_address VARCHAR(30) COMMENT "Mac address from FM Audit.",#max length is currently 17
ip_address VARCHAR(40) COMMENT "IP address from FM Audit", #max length is currently 28
vendor_device_id VARCHAR(25) COMMENT "The id that the vendor generates for a specific machine.", #max length is currently 10
new_vendor_device_id VARCHAR(25) COMMENT "Proposed vendor machine id number generated by vendor.", #max length is currently 13
budgeted_black INT(20) COMMENT "Budgeted black volume",
budgeted_color INT(20) COMMENT "Budgeted color volume",
cpc_black DECIMAL(7,6), #Other tables have this as (8, 6), but maybe (10,6) is better.
cpc_color DECIMAL(7,6), #Other tables have this as (8, 6), but maybe (10,6) is better.
five_year_id INT(11),
proposed_type_id  INT(11) COMMENT "Proposed machine type. orig type_id - Kept for now, but may not need", #orig type_id - Kept for now, but may not need
present_type_id  INT(11) COMMENT "Present machine type. Kept for now, but may not need", #orig present_type_id
is_proposed CHAR(1) COMMENT "Y=yes, N=no, U=??",
under_contract CHAR(1) COMMENT "Is the device under contract with SPC. Y=yes, N=no, U=??",
commencement_date datetime COMMENT "Date device came on line.",
commencement_black_meter INT(11),
commencement_color_meter INT(11),
closeout_date datetime,
#estimated_replacement_cost,
present_floorplan_id INT(11) COMMENT "FK Floorplan table where machine is currently located ",
present_x_position INT(11) COMMENT "Current x position on floorplan map.",
present_y_position INT(11) COMMENT "Current y position on floorplan map.",
present_serial_number VARCHAR(25) COMMENT "Current serial number.",
save_name_id INT(11) COMMENT "(FK) FYER Group ID",
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=41500; /*Current max = 40991*/



/******************** BEGIN BILLING TABLES*************************/
CREATE TABLE billing_data
(bd_id INT(15) AUTO_INCREMENT PRIMARY KEY,
billing_grp_id INT(11),
org_id INT(11),
bldg_id INT(11),
room_id INT(11),
dept_id INT(11),
vendor_id INT(11),
serial_number VARCHAR(25),
model_id INT(11),
projected_vol_black INT(15),
projected_vol_color INT(15),
billed_projected_vol_black INT(15),
billed_projected_vol_color INT(15),
cpc_black DECIMAL(7, 6),
cpc_color DECIMAL(7, 6),
vendor_cpc_black DECIMAL(7, 6),
vendor_cpc_color DECIMAL(7, 6),
begin_date datetime,
end_date datetime,
comments VARCHAR(2000),
school_year INT(2),
replacement_for VARCHAR(25),
close_out_date DATE,
actual_end_meter_black INT(15),
actual_end_meter_color INT(15),
client_black_charge DECIMAL(8, 6),
client_color_charge DECIMAL(8, 6),
vendor_black_charge DECIMAL(8, 6),
vendor_color_charge DECIMAL(8, 6),
upgrade_mach CHAR(1),
billing_cycle INT(2),
billing_type CHAR(1),
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=xx; /*Set this to 1 > the highest value currently existing in this column*/;


/*There will no longer be a billing_reports_todo and a billing_reports_completed table.*/
CREATE TABLE billing_reports
(billing_grp_id INT(11) AUTO_INCREMENT PRIMARY KEY COMMENT 'This is the id of the bill for a group of machines',
org_id INT(11),
vendor_id INT(11),
school_year INT(2),
sort_by CHAR(1) COMMENT "B= building D=department",
billing_cycle INT(2),
projected CHAR(1) COMMENT "Y= Yes N= No",
archived_records CHAR(1) COMMENT "Y= Yes N= No",
upgrade_client CHAR(1) COMMENT "Y= Yes N= No",
start_date DATETIME,
billing_type CHAR(1) COMMENT "C=Client V=Vendor",
billing_report_completed CHAR(1) COMMENT "Y= Yes N= No",
notes TEXT,
created_date datetime,
created_by INT(11),
modified_date datetime,
modified_by INT(11))
AUTO_INCREMENT=1050; /*Max 1035 currently*/