CREATE TABLE calendar(
	calendar_id BIGINT NOT NULL PRIMARY KEY,
	calendar_date date NULL,
	holiday_name Varchar(50) NULL
);


CREATE TABLE colors(
	color_id BIGINT NOT NULL PRIMARY KEY,
	color_name Varchar(100) NULL
);


CREATE TABLE department(
	department_id BIGINT NOT NULL PRIMARY KEY,
	department_name Varchar(100) NULL
);


CREATE TABLE employee(
	employee_id BIGINT NOT NULL PRIMARY KEY,
	employee_name Varchar(100) NULL,
	department_id BIGINT NULL,
	project_id BIGINT NULL
);


CREATE TABLE employees(
	employee_id BIGINT NOT NULL PRIMARY KEY,
	employee_ssn Varchar(9) NOT NULL,
	employee_first_name Varchar(50) NOT NULL,
	employee_last_name Varchar(50) NOT NULL,
	employee_address Varchar(50) NOT NULL,
	city Varchar(50) NULL,
	state Varchar(50) NULL,
	zip Varchar(50) NULL
);


CREATE TABLE payroll(
	payroll_id BIGINT NOT NULL PRIMARY KEY,
	paycheck_status Varchar(1) NULL,
	account_number Varchar(20) NULL,
	employee_id BIGINT NULL
);

CREATE TABLE paycheck_status(
	paycheck_status_id BIGINT NOT NULL PRIMARY KEY,
	employee_id BIGINT NULL,
	date_sent date NULL,
	status Varchar(100) NULL
);


CREATE TABLE PlasticProducts(
	sku BIGINT NOT NULL,
	product_desc Varchar(100) NULL,
	product_color Varchar(100) NULL,
	material_type Varchar(50) NULL
);



CREATE TABLE products(
	sku BIGINT NOT NULL PRIMARY KEY,
	product_desc Varchar(100) NULL,
	product_color Varchar(100) NULL,
	material_type Varchar(50) NULL
);


CREATE TABLE project(
	project_id BIGINT NOT NULL PRIMARY KEY,
	project_name Varchar(100) NULL
);


CREATE TABLE project_department(
	department_id BIGINT NOT NULL,
	project_id BIGINT NOT NULL,
	 PRIMARY KEY (department_id, project_id)
);


CREATE TABLE sales(
	sales_id BIGINT NOT NULL PRIMARY KEY,
	sales_date date NULL,
	sale_amount Decimal(10, 2) NULL
);



CREATE TABLE task(
	task_id BIGINT NOT NULL PRIMARY KEY,
	task_name Varchar(100) NULL,
	project_id BIGINT NULL
);







INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (1, '2009-01-01', 'New Years');
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (2, '2009-01-02', NULL);
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (3, '2009-01-03', NULL);
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (4, '2009-01-04', NULL);
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (5, '2009-01-05', NULL);
 
INSERT INTO colors (color_id, color_name) VALUES (1, 'black');
INSERT INTO colors (color_id, color_name) VALUES (2, 'blue');
INSERT INTO colors (color_id, color_name) VALUES (3, 'green');
INSERT INTO colors (color_id, color_name) VALUES (4, 'red');
 

INSERT INTO department (department_id, department_name) VALUES (1, 'Sales');
INSERT INTO department (department_id, department_name) VALUES (2, 'Marketing');

INSERT INTO employee (employee_id, employee_name, department_id, project_id) VALUES (1, 'John Doe', 1, 1);
INSERT INTO employee (employee_id, employee_name, department_id, project_id) VALUES (2, 'Jane Doe', 2, 1);

INSERT INTO employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) VALUES (1, '123456789', 'MARTINEZ', 'RODRIGUEZ', '110 WEATHERSFIELD DR', 'chester', 'va', '21111');
INSERT INTO employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) VALUES (2, '223466781', 'TOM', 'BRADY', '124 NEW CASTLE DR', 'chester', 'va', '21111');
INSERT INTO employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) VALUES (3, '323476782', 'WHITE', 'LOPEZ', '364 LEIGH FARM RD', 'chester', 'va', '21111');
INSERT INTO employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) VALUES (4, '423486783', 'HARRIS', 'WILLIAMS', '413 NOTTINGHAM DR', 'chester', 'va', '21111');
INSERT INTO employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) VALUES (5, '523496784', 'jason', 'LEWIS', '5006 MILL HILL LN', 'chester', 'va', '21111');
 
INSERT INTO payroll (payroll_id, paycheck_status, account_number, employee_id) VALUES (6, 'S', '00001234', 1);
INSERT INTO payroll (payroll_id, paycheck_status, account_number, employee_id) VALUES (7, 'S', '00001234', 2);
INSERT INTO payroll (payroll_id, paycheck_status, account_number, employee_id) VALUES (8, 'S', '00001234', 3);
INSERT INTO payroll (payroll_id, paycheck_status, account_number, employee_id) VALUES (9, 'S', '00001234', 4);
INSERT INTO payroll (payroll_id, paycheck_status, account_number, employee_id) VALUES (10, 'S', '00001234', 5);
 
INSERT INTO products (sku, product_desc, product_color, material_type) VALUES (1, 'Ball', 'black', 'cloth');
INSERT INTO products (sku, product_desc, product_color, material_type) VALUES (2, 'Bike', 'blue', 'metal');
INSERT INTO products (sku, product_desc, product_color, material_type) VALUES (3, 'Tent', NULL, 'plastic');
 

INSERT INTO project (project_id, project_name) VALUES (1, 'Project A');
 

INSERT INTO project_department (department_id, project_id) VALUES (1, 1);
INSERT INTO project_department (department_id, project_id) VALUES (2, 1);
 
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (1, '2009-01-01', 120.50);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (2, '2009-01-02', 115.00);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (3, '2009-01-03', 140.80);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (4, '2009-01-04', 100.50);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (144, '2023-06-09', 6988.00 );
 

INSERT INTO task (task_id, task_name, project_id) VALUES (1, 'Task A', 1);
INSERT INTO task (task_id, task_name, project_id) VALUES (2, 'Task B', 1);



INSERT INTO paycheck_status (paycheck_status_id, employee_id, date_sent, status) VALUES (1,1, '2024-03-22', 'Returned to sender');

ALTER TABLE employee ADD  CONSTRAINT employee_fk_dept_proj FOREIGN KEY(department_id, project_id)
REFERENCES project_department (department_id, project_id);
 


ALTER TABLE payroll ADD FOREIGN KEY(employee_id)
REFERENCES employees (employee_id);
 
ALTER TABLE project_department ADD  CONSTRAINT project_dept_fk_dept FOREIGN KEY(department_id)
REFERENCES department (department_id);
 

 
ALTER TABLE project_department ADD  CONSTRAINT project_dept_fk_proj FOREIGN KEY(project_id)
REFERENCES project (project_id);
 

 
ALTER TABLE task ADD  CONSTRAINT task_fk_proj FOREIGN KEY(project_id)
REFERENCES project (project_id);