CREATE TABLE buyer
(cust_id NUMBER(5) CONSTRAINT buy_cust_id_pk PRIMARY KEY,
last_name VARCHAR2(20) CONSTRAINT buy_last_name_nn NOT NULL,
first_name VARCHAR2(20) CONSTRAINT buy_first_name_nn NOT NULL,
street VARCHAR2(20) CONSTRAINT buy_street_nn NOT NULL,
city VARCHAR2(20) CONSTRAINT buy_city_nn NOT NULL,
state VARCHAR2(2) CONSTRAINT buy_state_nn NOT NULL,
ZIP VARCHAR2(5) CONSTRAINT buy_zip_nn NOT NULL,
phone_number VARCHAR(10) CONSTRAINT buy_phone_nn NOT NULL,
email VARCHAR2(20) CONSTRAINT buy_email_uk UNIQUE)
;

CREATE TABLE potential_customers
(cust_id NUMBER(5) CONSTRAINT pot_cust_id_pk PRIMARY KEY,
last_name VARCHAR2(20) CONSTRAINT pot_last_name_nn NOT NULL,
first_name VARCHAR2(20) CONSTRAINT pot_first_name_nn NOT NULL,
street VARCHAR2(20) CONSTRAINT pot_street_nn NOT NULL,
city VARCHAR2(20) CONSTRAINT pot_city_nn NOT NULL,
state VARCHAR2(2) CONSTRAINT pot_state_nn NOT NULL,
ZIP VARCHAR2(5) CONSTRAINT pot_zip_nn NOT NULL,
phone_number VARCHAR(10) CONSTRAINT pot_phone_nn NOT NULL,
email VARCHAR2(20) CONSTRAINT pot_email_uk UNIQUE)
;

CREATE TABLE service_customers
(cust_id NUMBER(5) CONSTRAINT serv_cust_id_pk PRIMARY KEY,
last_name VARCHAR2(20) CONSTRAINT serv_last_name_nn NOT NULL,
first_name VARCHAR2(20) CONSTRAINT serv_first_name_nn NOT NULL,
street VARCHAR2(20) CONSTRAINT serv_street_nn NOT NULL,
city VARCHAR2(20) CONSTRAINT serv_city_nn NOT NULL,
state VARCHAR2(2) CONSTRAINT serv_state_nn NOT NULL,
ZIP VARCHAR2(5) CONSTRAINT serv_zip_nn NOT NULL,
phone_number VARCHAR(10) CONSTRAINT serv_phone_nn NOT NULL,
email VARCHAR2(20) CONSTRAINT serv_email_uk UNIQUE)
;

CREATE TABLE sales_employee
(
empl_id NUMBER(2) CONSTRAINT sales_employeeid_pk PRIMARY KEY,
last_name VARCHAR2(20) CONSTRAINT sales_lastname_nn NOT NULL,
first_name VARCHAR2(20) CONSTRAINT sales_fn_nn NOT NULL,
phone_number NUMBER(10) CONSTRAINT sales_pn_nn NOT NULL,
hire_date DATE CONSTRAINT sales_hd_nn NOT NULL,
monthly_salary NUMBER(10) CONSTRAINT sales_ms_ck
            CHECK ( monthly_salary > 0 ),
commission_pct NUMBER(3) CONSTRAINT sales_cpct_nn NOT NULL
            CHECK (commission_pct >= 0)
)
;

CREATE TABLE service_employee (
empl_id NUMBER(2) CONSTRAINT serv_employeeid_pk PRIMARY KEY,
last_name VARCHAR2(20) CONSTRAINT serv_lastname_nn NOT NULL,
first_name VARCHAR2(20) CONSTRAINT serv_fn_nn NOT NULL,
phone_number NUMBER(10) CONSTRAINT serv_pn_nn NOT NULL,
hire_date DATE CONSTRAINT serv_hd_nn NOT NULL,
monthly_salary NUMBER(10) CONSTRAINT serv_ms_ck
            CHECK ( monthly_salary > 0 )
)
;

CREATE TABLE sale_vehicles
(
vin_number NUMBER(17) CONSTRAINT veh_vin_pk PRIMARY KEY,
vehicle_make VARCHAR2(20) CONSTRAINT veh_make_nn NOT NULL,
vehicle_model VARCHAR2(20) CONSTRAINT veh_model_nn NOT NULL,
vehicle_year NUMBER(4) CONSTRAINT veh_year_nn NOT NULL,
vehicle_color VARCHAR2(10) CONSTRAINT veh_color_nn NOT NULL,
list_price NUMBER(10) CONSTRAINT veh_price_nn NOT NULL,
vehicle_mileage NUMBER(10) CONSTRAINT veh_mileage_nn NOT NULL,
used_y_n VARCHAR2(1) CHECK (used_y_n in ('Y', 'N'))
)
;

CREATE TABLE supplier (
supplier_code number(6) CONSTRAINT supplier_sc_pk PRIMARY KEY,
supplier_name varchar2(20) CONSTRAINT supplier_sn_nn NOT NULL,
supplier_phone_num number(10) CONSTRAINT supplier_ph_nn NOT NULL,
supplier_street varchar2(15) CONSTRAINT supplier_st_nn NOT NULL,
supplier_city varchar2(10) CONSTRAINT supplier_cy_nn NOT NULL,
supplier_state varchar2(2) CONSTRAINT supplier_street_nn NOT NULL,
supplier_zip varchar2(5) CONSTRAINT supplier_zp_nn NOT NULL
)
;

CREATE TABLE options (
option_code NUMBER(5) CONSTRAINT opt_oc_pk PRIMARY KEY,
option_name VARCHAR2(30) CONSTRAINT opt_on_nn NOT NULL,
option_price NUMBER(8) CONSTRAINT opt_op_nn NOT NULL
CHECK (option_price > 0),
supplier_code NUMBER(6) CONSTRAINT opt_sc_fk
            REFERENCES supplier(supplier_code)
)
;

CREATE TABLE parts
(part_code NUMBER(2) CONSTRAINT parts_pc_pk PRIMARY KEY,
part_name VARCHAR2(20) CONSTRAINT parts_pn_nn NOT NULL,
part_quantity NUMBER(3) CONSTRAINT parts_pq_ch CHECK (part_quantity > 0),
part_price NUMBER(6) CONSTRAINT parts_pp_ch CHECK (part_price > 0),
supplier_code NUMBER(6) CONSTRAINT parts_sc_fk REFERENCES supplier(supplier_code)
)
;

CREATE TABLE Service_Vehicles (
vin_number NUMBER(17) CONSTRAINT sv_vn_pk PRIMARY KEY,
vehicle_make VARCHAR2(20) CONSTRAINT vn_make_nn NOT NULL,
vehicle_model VARCHAR2(20) CONSTRAINT vn_model_nn NOT NULL,
vehicle_year NUMBER(4) CONSTRAINT vn_year_nn NOT NULL,
vehicle_color VARCHAR2(10) CONSTRAINT vn_color_nn NOT NULL,
vehicle_mileage NUMBER(10) CONSTRAINT vn_mileage_nn NOT NULL
)
;

CREATE TABLE Sales_Invoice
(
sales_invoice_number NUMBER(3) CONSTRAINT si_sin_pk PRIMARY KEY,
empl_id NUMBER(5) CONSTRAINT si_empl_id_FK
            REFERENCES sales_employee(empl_id),
cust_id NUMBER(5) CONSTRAINT si_cust_id_FK
            REFERENCES buyer(cust_id),
vin_number NUMBER(17) CONSTRAINT si_vin_FK
            REFERENCES sale_vehicles(vin_number),
option_code NUMBER(5) CONSTRAINT si_oc_FK
            REFERENCES options(option_code),
theft_fire_insurance NUMBER(10) CONSTRAINT si_tfi_nn NOT NULL,
liability_insurance NUMBER(10) CONSTRAINT si_lia_nn NOT NULL,
collision_insurance NUMBER(10) CONSTRAINT si_col_nn NOT NULL,
prop_damage_insurance NUMBER(10) CONSTRAINT si_pdi_nn NOT NULL,
trade_in NUMBER(10) CONSTRAINT si_trade_nn NOT NULL,
discount_pct NUMBER(3) CONSTRAINT si_disc_nn NOT NULL,
sales_date DATE DEFAULT SYSDATE
)
;

ALTER TABLE sales_invoice
DROP COLUMN option_code;

CREATE TABLE trade_in_receipt (
ti_vin_number NUMBER(17) CONSTRAINT ti_vin_fk REFERENCES sale_vehicles(vin_number),
ti_sin NUMBER(3) CONSTRAINT ti_sin_fk REFERENCES sales_invoice(sales_invoice_number),
ti_price NUMBER(10) CONSTRAINT ti_p_nn NOT NULL,
ti_sales_date DATE DEFAULT SYSDATE
)
;

CREATE TABLE Service_work_Order
(service_invoice_number NUMBER(3) CONSTRAINT swo_sin_pk PRIMARY KEY,
empl_id NUMBER(5) CONSTRAINT swo_empl_id_FK
            REFERENCES service_employee(empl_id),
cust_id NUMBER(5) CONSTRAINT swo_cust_id_FK
            REFERENCES service_customers(cust_id),
vin_number NUMBER(17) CONSTRAINT swo_vin_FK
            REFERENCES service_vehicles(vin_number),
part_code NUMBER(6) CONSTRAINT swo_pc_FK
            REFERENCES parts(part_code),
service VARCHAR2(50) CONSTRAINT swo_service_nn NOT NULL,
service_date DATE DEFAULT SYSDATE,
service_cost NUMBER(8) CONSTRAINT swo_cost_nn NOT NULL
            CHECK (service_cost > 0)
)
;

CREATE TABLE purchase_order
(
po_number NUMBER(4) CONSTRAINT po_pon_pk PRIMARY KEY,
dealer_id NUMBER(4) CONSTRAINT po_did_fk REFERENCES dealership(dealer_id),
vin_number NUMBER(17) CONSTRAINT po_vin_fk REFERENCES sale_vehicles(vin_number),
purchase_price NUMBER(10) CONSTRAINT po_pp_nn NOT NULL
            CHECK (purchase_price > 0)
)
;

CREATE TABLE preferences (
cust_id NUMBER(5) CONSTRAINT pref_custid_fk
            REFERENCES potential_customers(cust_id),
vehicle_make VARCHAR2(20) CONSTRAINT pref_make_nn NOT NULL,
vehicle_model VARCHAR2(20) CONSTRAINT pref_model_nn NOT NULL,
vehicle_year NUMBER(4) CONSTRAINT pref_year_nn NOT NULL,
vehicle_color VARCHAR2(10) CONSTRAINT pref_color_nn NOT NULL,
price_max NUMBER(10) CONSTRAINT pref_pm_ck CHECK (price_max > 0),
CONSTRAINT pref_custid_pk PRIMARY KEY(cust_id)
)
;

CREATE TABLE option_subtotal (
os_si_number NUMBER(3) CONSTRAINT os_si_fk
            REFERENCES sales_invoice(sales_invoice_number),
os_option_code NUMBER(5) CONSTRAINT os_oc_fk
            REFERENCES options(option_code),
os_selling_price NUMBER(10) CHECK (os_selling_price > 0)
)
;

CREATE OR REPLACE VIEW options_view
AS SELECT SUM(os_selling_price) AS "Price", os_si_number
FROM option_subtotal
GROUP BY os_si_number
;

CREATE OR REPLACE VIEW sales_invoice_view
AS SELECT s.Sales_invoice_number, se.empl_id, b.cust_id, v.vin_number, s.sales_date, os.os_option_code "Option Code", b.first_name || ' ' || b.last_name AS "Full Name", b.street, b.city, b.state, b.zip, b.phone_number, o.option_name, os.os_selling_price AS "Options Subtotal",
((os.os_selling_price + s.theft_fire_insurance + s.liability_insurance + s.prop_damage_insurance + s.collision_insurance + v.list_price - s.trade_in) * (1 + (1/100) * (s.discount_pct))) AS "Sales Total" 
FROM sales_invoice s
FULL OUTER JOIN option_subtotal os
ON s.sales_invoice_number = os.os_si_number
JOIN sales_employee se
ON s.empl_id = se.empl_id
JOIN sale_vehicles v
on s.vin_number = v.vin_number
JOIN buyer b
on s.cust_id = b.cust_id
LEFT OUTER JOIN options o
on os.os_option_code = o.option_code
;

CREATE OR REPLACE VIEW service_wo_view
AS SELECT w.service_invoice_number, w.empl_id, w.cust_id, w.vin_number, w.part_code, w.service, w.service_date, w.service_cost, v.vehicle_make, v.vehicle_model, v.vehicle_year, v.vehicle_color, v.vehicle_mileage, p.part_price, m.monthly_salary, (p.part_price + w.service_cost) * 1.0825 AS "Total Cost"
FROM service_work_order w
JOIN service_vehicles v
ON w.vin_number = v.vin_number
JOIN service_employee m
ON w.empl_id = m.empl_id
JOIN parts p
ON w.part_code = p.part_code
;

CREATE OR REPLACE VIEW purchase_order_view
AS SELECT p.po_number, p.dealer_id, p.vin_number, v.vehicle_make, v.vehicle_model, v.vehicle_year, v.vehicle_color, p.purchase_price
FROM purchase_order p
JOIN sale_vehicles v
ON p.vin_number = v.vin_number
;

CREATE OR REPLACE VIEW for_sale_view
AS SELECT     *
FROM sale_vehicles
WHERE vin_number NOT IN
            (select vin_number
            FROM sales_invoice)
;

CREATE OR REPLACE VIEW cust_make_pref_view
AS SELECT c.cust_id, c.last_name, c.first_name, p.vehicle_make
            FROM potential_customers c JOIN preferences p
            ON       (c.cust_id = p.cust_id)
;

CREATE OR REPLACE VIEW need_service
AS SELECT cust_id, vin_number, (SYSDATE - service_date) AS SERVICE_NEEDED, service
FROM service_work_order
WHERE (SYSDATE - service_date) >= 180
;

CREATE OR REPLACE VIEW view_h
AS SELECT s.vin_number, s.sales_date, TO_DATE (s.sales_date, 'MM') AS Month, v.list_price - o.purchase_price AS "Profit", (v.list_price  - o.purchase_price) * e.commission_pct AS "Commission Paid", v.vehicle_make, v.vehicle_model, v.vehicle_year, v.vehicle_color, v.vehicle_mileage
FROM sales_invoice s
JOIN sale_vehicles v
ON s.vin_number = v.vin_number
JOIN sales_employee e
ON s.empl_id = e.empl_id
JOIN purchase_order o
ON s.vin_number = o.vin_number
;

CREATE OR REPLACE VIEW new_veh_prof_view
AS SELECT v.vehicle_make, (v.list_price - p.purchase_price) AS PROFITABILITY
FROM sale_vehicles v JOIN purchase_order p
ON v.vin_number = p.vin_number
WHERE v.used_y_n = 'N'
ORDER BY PROFITABILITY DESC
;

CREATE OR REPLACE VIEW used_veh_prof_view
AS SELECT v.vehicle_make, (v.list_price - t.ti_price) AS PROFITABILITY
FROM sale_vehicles v JOIN trade_in_receipt t
ON v.vin_number = t.ti_vin_number
WHERE v.used_y_n = 'Y'
ORDER BY PROFITABILITY DESC
;

CREATE OR REPLACE VIEW service_profit_view
AS SELECT s.service, SUM(s.service_cost - p.part_price) AS "Total Service Profit"
FROM service_work_order s LEFT OUTER JOIN parts p
ON s.part_code = p.part_code
GROUP BY s.service
;

CREATE OR REPLACE VIEW highest_comm_view
AS SELECT e.first_name || ' ' || e.last_name AS "Employee Name", (v.list_price * (1/100 * e.commission_pct)) AS "Commission"
FROM sales_invoice s
JOIN sale_vehicles v
ON s.vin_number = v.vin_number
JOIN sales_employee e
ON s.empl_id = e.empl_id
ORDER BY (v.list_price * (1 + 1/100 * e.commission_pct)) DESC
;

CREATE OR REPLACE VIEW largest_num_vehicles_view
AS SELECT e.first_name || ' ' || e.last_name AS "Employee Name",
            COUNT(s.empl_id) AS "Number of Vehicles Sold"
FROM sales_invoice s JOIN sales_employee e
ON s.empl_id = e.empl_id
GROUP BY e.first_name || ' ' || e.last_name
oRDER BY COUNT(s.empl_id) DESC
;

CREATE OR REPLACE VIEW purchase_no_service_view
AS SELECT cust_id
FROM sales_invoice
WHERE vin_number NOT IN
            (SELECT vin_number
            FROM service_vehicles)
;

CREATE OR REPLACE VIEW buyer_view
AS SELECT     *
FROM buyer
;

CREATE OR REPLACE VIEW personal_view_1
AS SELECT COUNT(cust_id) AS "Number of Customers", city || ' , ' || state AS Location
FROM buyer
GROUP BY city || ' , ' || state
;

CREATE OR REPLACE VIEW personal_view_2
AS SELECT '$' || SUM(l.monthly_salary * 12) + SUM(r.monthly_salary * 12) AS "YEARLY EMPLOYEE SALARY EXPENSE"
FROM sales_employee l JOIN service_employee r
ON l.empl_id = r.empl_id
;

CREATE OR REPLACE VIEW last_view
AS SELECT
AVG(price_max) as "Average Price Pref"
FROM
preferences
;