use invoices;

# need to add a field for setting the preferred labor_rate, perhaps for PC and server work, and emergency work, as before
# also perhaps a field for company notes (who added the company to the database, who is the designated tech, what their history is, etc.


CREATE TABLE company (
  company_id INT NOT NULL AUTO_INCREMENT,
  company_name VARCHAR(90),
  address1 VARCHAR(40),
  address2 VARCHAR(40),
  city VARCHAR(30),
  state_id CHAR(2),
  zip CHAR(5),
  contact_name VARCHAR(30),
  contact_title VARCHAR(30),
  contact_email VARCHAR(30),
  url VARCHAR(50),
  phone_area CHAR(3),
  phone_exchange CHAR(3),
  phone_digits CHAR(4),
  phone_extension CHAR(4),
  fax_area CHAR(3),
  fax_exchange CHAR(3),
  fax_digits CHAR(4),
  fax_extension CHAR(4),
  PRIMARY KEY(company_id)
); 


CREATE TABLE material (
  material_id INT NOT NULL AUTO_INCREMENT,
  transaction_id INT,
  quantity INT,
  item_type VARCHAR(30),
  item_description VARCHAR(60),
  unitprice REAL,
  PRIMARY KEY(material_id)
);


# what about service type, and related labor_rate?
CREATE TABLE transaction (
  transaction_id INT NOT NULL AUTO_INCREMENT,
  transaction_date DATE,
  type_id VARCHAR(20),
  company_id INT,
  purchase_order VARCHAR(20),
  project_reference VARCHAR(40),
  problem TEXT,
  solution TEXT,
  technician_id VARCHAR(12),
  labor_hours REAL,
  labor_rate REAL,
  tax_rate REAL,
  paid TINYINT,
  PRIMARY KEY(transaction_id)
);


CREATE TABLE type (
  type_id CHAR(2) NOT NULL,
  type_name VARCHAR(20),
  PRIMARY KEY(type_id)
);


CREATE TABLE technician (
  technician_id VARCHAR(12) NOT NULL,
  technician_name VARCHAR(30),
  PRIMARY KEY(technician_id)
);


CREATE TABLE state (
  state_id CHAR(2) NOT NULL,
  state_name VARCHAR(20),
  PRIMARY KEY(state_id)
);


INSERT INTO state VALUES 
('AL','Alabama'), ('AK','Alaska'), ('AZ','Arizona'), ('AR','Arkansas'), ('CA','California'), ('CO','Colorado'), ('CT','Connecticut'), ('DE','Delaware'), ('DC','District of Columbia'), ('FL','Florida'), ('GA','Georgia'), ('HI','Hawaii'), ('ID','Idaho'), ('IL','Illinois'), ('IN','Indiana'), ('IA','Iowa'), ('KS','Kansas'), ('KY','Kentucky'), ('LA','Louisiana'), ('ME','Maine'), ('MD','Maryland'), ('MA','Massachusetts'), ('MI','Michigan'), ('MN','Minnesota'), ('MS','Mississippi'), ('MO','Missouri'), ('MT','Montana'), ('NE','Nebraska'), ('NV','Nevada'), ('NH','New Hampshire'), ('NJ','New Jersey'), ('NM','New Mexico'), ('NY','New York'), ('NC','North Carolina'), ('ND','North Dakota'), ('OH','Ohio'), ('OK','Oklahoma'), ('OR','Oregon'), ('PA','Pennsylvania'), ('RI','Rhode Island'), ('SC','South Carolina'), ('SD','South Dakota'), ('TN','Tennessee'), ('TX','Texas'), ('UT','Utah'), ('VT','Vermont'), ('VA','Virginia'), ('WA','Washington'), ('WV','West Virginia'), ('WI','Wisconsin'), ('WY','Wyoming');

INSERT INTO type VALUES
('IN','Invoice'), ('ES','Estimate'),('PR','Proposal');

INSERT INTO technician VALUES
('jkelly','Joey Kelly');

