CREATE TABLE calendar(
	calendar_id Number(19) NOT NULL,
	calendar_date date NULL,
	holiday_name Varchar2(50) NULL,
	PRIMARY KEY (calendar_id) 
);


CREATE TABLE colors(
	color_id Number(19) NOT NULL,
	color_name Varchar2(100) NULL,
	PRIMARY KEY (color_id) 
);


CREATE TABLE department(
	department_id Number(19) NOT NULL,
	department_name Varchar2(100) NULL,
	PRIMARY KEY (department_id) 
);


CREATE TABLE employee(
	employee_id Number(19) NOT NULL,
	employee_name Varchar2(100) NULL,
	department_id Number(19) NULL,
	project_id Number(19) NULL,
	PRIMARY KEY (employee_id) 
);


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


CREATE TABLE payroll(
	payroll_id Number(10) NOT NULL,
	paycheck_status Varchar2(1) NULL,
	account_number Varchar2(20) NULL,
	employee_id Number(10) NULL,
PRIMARY KEY (payroll_id) 
);

CREATE TABLE paycheck_status(
	paycheck_status_id Number(10) NOT NULL,
	employee_id Number(10) NULL,
	date_sent date NULL,
	status Varchar2(100) NULL,
PRIMARY KEY (paycheck_status_id) 
);


CREATE TABLE PlasticProducts(
	sku Number(19) NOT NULL,
	product_desc Varchar2(100) NULL,
	product_color Varchar2(100) NULL,
	material_type Varchar2(50) NULL
);



CREATE TABLE products(
	sku Number(19) NOT NULL,
	product_desc Varchar2(100) NULL,
	product_color Varchar2(100) NULL,
	material_type Varchar2(50) NULL,
	PRIMARY KEY (sku) 
);


CREATE TABLE project(
	project_id Number(19) NOT NULL,
	project_name Varchar2(100) NULL,
	PRIMARY KEY (project_id) 
);


CREATE TABLE project_department(
	department_id Number(19) NOT NULL,
	project_id Number(19) NOT NULL,
 CONSTRAINT project_dept_pk PRIMARY KEY 
(
	department_id,
	project_id
) 
);


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



CREATE TABLE task(
	task_id Number(19) NOT NULL,
	task_name Varchar2(100) NULL,
	project_id Number(19) NULL,
PRIMARY KEY (task_id) 
);





INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (1, to_date('2009-01-01','yyyy-mm-dd'), 'New Years');
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (2, to_date('2009-01-02' ,'yyyy-mm-dd'), NULL);
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (3, to_date('2009-01-03' ,'yyyy-mm-dd'), NULL);
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (4, to_date('2009-01-04' ,'yyyy-mm-dd'), NULL);
INSERT INTO calendar (calendar_id, calendar_date, holiday_name) VALUES (5, to_date('2009-01-05' ,'yyyy-mm-dd'), 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, to_date('2009-01-01','yyyy-mm-dd'), 120.50);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (2, to_date('2009-01-02','yyyy-mm-dd'), 115.00);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (3, to_date('2009-01-03','yyyy-mm-dd'), 140.80);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (4, to_date('2009-01-04','yyyy-mm-dd'), 100.50);
INSERT INTO sales (sales_id, sales_date, sale_amount) VALUES (1443577835, to_date('2023-06-09','yyyy-mm-dd'), 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, to_date('2024-03-22','yyyy-mm-dd'), '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 employee ENABLE CONSTRAINT employee_fk_dept_proj;

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 ENABLE CONSTRAINT project_dept_fk_dept;
 
ALTER TABLE project_department ADD  CONSTRAINT project_dept_fk_proj FOREIGN KEY(project_id)
REFERENCES project (project_id);
 
ALTER TABLE project_department ENABLE CONSTRAINT project_dept_fk_proj;
 
ALTER TABLE task ADD  CONSTRAINT task_fk_proj FOREIGN KEY(project_id)
REFERENCES project (project_id);
 
ALTER TABLE task ENABLE CONSTRAINT task_fk_proj;