SQL> SET SERVEROUTPUT ON;
SQL> 
SQL> DECLARE
  2        l_tab DBMS_UTILITY.uncl_array;
  3        l_tablen number;
  4      BEGIN
  5        dbms_utility.comma_to_table('CONTACT,PROFILE,ROLE,USERROLE,STAFF,ZIP,ADDRESS,DOCUMENT,PLAN,EXHIBIT,SUBSCRIPTION,AUTHORIZATION,INVENTORY,ORDER_ITEMS,INVOICE,TASK_HISTORY'
  6        , l_tablen, l_tab);
  7        for i in 1..l_tablen
  8        LOOP
  9  	   BEGIN
 10  	   EXECUTE IMMEDIATE 'DROP TABLE ' || l_tab(i) || ' CASCADE CONSTRAINTS';
 11  	   DBMS_OUTPUT.put_line('DROPPED '  || l_tab(i) );
 12  	   EXCEPTION
 13              WHEN OTHERS THEN NULL;
 14         END;
 15       END LOOP;
 16  
 17       DBMS_UTILITY.comma_to_table('ISEQ_CONTACT,ISEQ_ROLE,ISEQ_PROFILE,ISEQ_DOCUMENT', l_tablen, l_tab);
 18       for i in 1..l_tablen
 19       LOOP
 20  	   BEGIN
 21         EXECUTE IMMEDIATE 'DROP SEQUENCE '|| l_tab(i);
 22         DBMS_OUTPUT.put_line('DROPPED '  || l_tab(i) );
 23  	   EXCEPTION
 24              WHEN OTHERS THEN NULL;
 25         END;
 26       END LOOP;
 27  
 28       COMMIT;
 29     END;
 30  /
DROPPED CONTACT
DROPPED PROFILE
DROPPED ROLE
DROPPED USERROLE
DROPPED STAFF
DROPPED ZIP
DROPPED ADDRESS
DROPPED DOCUMENT
DROPPED PLAN
DROPPED EXHIBIT
DROPPED SUBSCRIPTION
DROPPED AUTHORIZATION
DROPPED INVENTORY
DROPPED ORDER_ITEMS
DROPPED INVOICE
DROPPED TASK_HISTORY
DROPPED ISEQ_CONTACT
DROPPED ISEQ_ROLE
DROPPED ISEQ_PROFILE
DROPPED ISEQ_DOCUMENT


PL/SQL procedure successfully completed.

SQL> 
SQL> CREATE SEQUENCE ISEQ_CONTACT START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Sequence ISEQ_CONTACT created.

SQL> 
SQL> CREATE TABLE CONTACT (
  2  	CONTACT_ID NUMBER DEFAULT ISEQ_CONTACT.nextval NOT NULL,
  3  	FIRST_NAME VARCHAR2(16) NOT NULL,
  4  	LAST_NAME VARCHAR2(16) NOT NULL,
  5  	PHONE_NO VARCHAR2(12),
  6  	EMAIL VARCHAR2(64) NOT NULL,
  7  	MODIFIED_BY NUMBER,
  8  	CREATED_DT TIMESTAMP DEFAULT SYSDATE,
  9  	MODIFIED_DT TIMESTAMP DEFAULT SYSDATE,
 10  	CONSTRAINT PK_CONTACT_ID PRIMARY KEY (CONTACT_ID),
 11  	FOREIGN KEY (MODIFIED_BY) REFERENCES CONTACT(CONTACT_ID)
 12  );

Table CONTACT created.

SQL> 
SQL> CREATE TABLE ZIP (
  2  	ZIP VARCHAR2(5),
  3  	CITY VARCHAR2(16),
  4  	STATE VARCHAR2(2),
  5  	CONSTRAINT PK_ZIP PRIMARY KEY (ZIP),
  6  	CONSTRAINT ZIP_CHECK CHECK ( REGEXP_LIKE(ZIP, '\d{5}') )
  7  );

Table ZIP created.

SQL> 
SQL> CREATE TABLE ADDRESS (
  2  	ZIP VARCHAR2(5) NOT NULL,
  3  	FK_CONTACT_ID NUMBER NOT NULL,
  4  	STREET VARCHAR2(32) NOT NULL,
  5  	CONSTRAINT PK_ADDRESS PRIMARY KEY (ZIP, FK_CONTACT_ID),
  6  	FOREIGN KEY (FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID) ON DELETE CASCADE,
  7  	FOREIGN KEY (ZIP) REFERENCES ZIP(ZIP)
  8  );

Table ADDRESS created.

SQL> 
SQL> CREATE SEQUENCE "ISEQ_ROLE" START WITH 100 INCREMENT BY 100 NOCACHE NOCYCLE;

Sequence "ISEQ_ROLE" created.

SQL> 
SQL> CREATE TABLE ROLE (
  2  	ROLE_ID NUMBER DEFAULT ISEQ_ROLE.nextval NOT NULL,
  3  	ROLENAME VARCHAR2(16),
  4  	CONSTRAINT PK_ROLE_ID PRIMARY KEY (ROLE_ID),
  5  	CONSTRAINT check_role CHECK (ROLENAME IN ('SYSTEM', 'CONTACT', 'CUSTOMER', 'STAFF', 'SUPPLIER'))
  6  );

Table ROLE created.

SQL> 
SQL> CREATE SEQUENCE "ISEQ_PROFILE" START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Sequence "ISEQ_PROFILE" created.

SQL> 
SQL> CREATE TABLE PROFILE (
  2  	PROFILE_ID NUMBER DEFAULT ISEQ_PROFILE.nextval NOT NULL,
  3  	FK_CONTACT_ID NUMBER NOT NULL,
  4  	ACTIVE BOOLEAN DEFAULT FALSE,
  5  	MODIFIED_BY NUMBER NOT NULL,
  6  	CREATED_DT TIMESTAMP DEFAULT SYSDATE,
  7  	MODIFIED_DT TIMESTAMP DEFAULT SYSDATE,
  8  	CONSTRAINT PK_PROFILE_ID PRIMARY KEY(PROFILE_ID),
  9  	FOREIGN KEY (FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID) ON DELETE CASCADE,
 10  	FOREIGN KEY(MODIFIED_BY) REFERENCES PROFILE(PROFILE_ID)
 11  );

Table PROFILE created.

SQL> 
SQL> CREATE TABLE USERROLE (
  2  	FK_PROFILE_ID NUMBER NOT NULL,
  3  	FK_ROLE_ID NUMBER NOT NULL,
  4  	PASSWORDHASH RAW(256) DEFAULT NULL,
  5  	CONSTRAINT PK_USERROLE PRIMARY KEY (FK_ROLE_ID,FK_PROFILE_ID),
  6  	FOREIGN KEY(FK_PROFILE_ID) REFERENCES PROFILE(PROFILE_ID) ON DELETE CASCADE,
  7  	FOREIGN KEY(FK_ROLE_ID) REFERENCES ROLE(ROLE_ID) ON DELETE CASCADE
  8  );

Table USERROLE created.

SQL> 
SQL> CREATE TABLE STAFF(
  2  	FK_PROFILE_ID NUMBER NOT NULL,
  3  	POSITION VARCHAR2(14) NOT NULL,
  4  	CONSTRAINT check_position CHECK (POSITION IN ('Director', 'Curator',
  5  	'Art Handler', 'Registrar', 'Associate')),
  6  	CONSTRAINT PK_STAFF PRIMARY KEY (FK_PROFILE_ID),
  7      FOREIGN KEY(FK_PROFILE_ID) REFERENCES PROFILE(PROFILE_ID)
  8  );

Table STAFF created.

SQL> 
SQL> CREATE SEQUENCE ISEQ_DOCUMENT START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Sequence ISEQ_DOCUMENT created.

SQL> CREATE TABLE DOCUMENT (
  2  	DOCUMENT_ID NUMBER DEFAULT ISEQ_DOCUMENT.nextval NOT NULL,
  3  	DOCUMENT_URL VARCHAR2(254) NOT NULL,
  4      CONSTRAINT PK_DOCUMENT PRIMARY KEY (DOCUMENT_ID)
  5  );

Table DOCUMENT created.

SQL> 
SQL> CREATE TABLE PLAN (
  2  	PLAN_ID NUMBER GENERATED ALWAYS AS IDENTITY,
  3  	DESCRIPTION VARCHAR2(32),
  4  	FK_DOCUMENT_ID NUMBER NOT NULL,
  5  	Duration NUMBER, --in hours
  6  	price NUMBER,
  7  	MODIFIED_BY NUMBER,
  8  	CREATED_DT TIMESTAMP DEFAULT SYSDATE,
  9  	MODIFIED_DT TIMESTAMP DEFAULT SYSDATE,
 10  	CONSTRAINT PK_PLAN PRIMARY KEY(PLAN_ID),
 11  	FOREIGN KEY(FK_DOCUMENT_ID) REFERENCES DOCUMENT(DOCUMENT_ID)
 12  );

Table PLAN created.

SQL> 
SQL> CREATE TABLE SUBSCRIPTION (
  2  	FK_PLAN_ID NUMBER,
  3  	FK_CONTACT_ID NUMBER,
  4  	MODIFIED_BY NUMBER,
  5  	CREATED_DT TIMESTAMP DEFAULT SYSDATE,
  6  	MODIFIED_DT TIMESTAMP DEFAULT SYSDATE,
  7  	CONSTRAINT PK_SUBSCRIPTION PRIMARY KEY (FK_PLAN_ID, FK_CONTACT_ID),
  8  	FOREIGN KEY(FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID),
  9  	FOREIGN KEY(FK_PLAN_ID) REFERENCES PLAN(PLAN_ID)
 10  );

Table SUBSCRIPTION created.

SQL> 
SQL> /* Once invoice is paid, external system inserts invoice uid. Insert/Update.*/
SQL> CREATE TABLE INVOICE (
  2      INVOICE_ID RAW(16) DEFAULT SYS_GUID() NOT NULL PRIMARY KEY,
  3      FK_PLAN_ID NUMBER,
  4      FK_CONTACT_ID NUMBER,
  5      InvoiceDate DATE,
  6      AmountPaid DECIMAL(10, 2) DEFAULT 0,
  7      FOREIGN KEY(FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID),
  8      FOREIGN KEY(FK_PLAN_ID) REFERENCES PLAN(PLAN_ID)
  9  );

Table INVOICE created.

SQL> 
SQL> CREATE TABLE AUTHORIZATION (
  2  	AUTHORIZATION_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  3  	FK_CONTACT_ID NUMBER,
  4  	AUTHORIZATION_STATUS VARCHAR2(16) NOT NULL,
  5  	AUTHORIZATION_DATE TIMESTAMP DEFAULT SYSDATE,
  6  	EXPIRATION_DATE TIMESTAMP DEFAULT SYSDATE,
  7  	FK_DOCUMENT_ID NUMBER,
  8  	MODIFIED_BY NUMBER,
  9  	CREATED_DT TIMESTAMP DEFAULT SYSDATE,
 10  	MODIFIED_DT TIMESTAMP DEFAULT SYSDATE,
 11  	FOREIGN KEY(FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID),
 12  	FOREIGN KEY(FK_DOCUMENT_ID) REFERENCES DOCUMENT(DOCUMENT_ID),
 13  	CONSTRAINT check_authorization_status CHECK (AUTHORIZATION_STATUS IN ('pending', 'approved', 'rejected', 'expired'))
 14  );

Table AUTHORIZATION created.

SQL> 
SQL> CREATE TABLE INVENTORY (
  2  	INVENTORY_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  3  	FK_CONTACT_ID NUMBER NOT NULL,
  4  	AUTHORIZATION_ID NUMBER,
  5  	SKU VARCHAR2(100) NOT NULL,
  6  	PRODUCT_DESCRIPTION VARCHAR2(100) NOT NULL,
  7  	UNIT_COST DECIMAL(10,2) NOT NULL,
  8  	LEAD_TIME_DAYS INTEGER NOT NULL,
  9  	PRODUCT_NAME VARCHAR2(100) NOT NULL,
 10  	UNIT_OF_MEASURE VARCHAR2(50) NOT NULL,
 11  	BASE_PRICE DECIMAL(10,2) NOT NULL,
 12  	QUANTITY_AVAILABLE INTEGER DEFAULT 0 NOT NULL,
 13  	CREATED_AT TIMESTAMP DEFAULT SYSDATE,
 14  	UPDATED_AT TIMESTAMP DEFAULT SYSDATE,
 15  	FK_DOCUMENT_ID NUMBER,
 16  	FOREIGN KEY(FK_CONTACT_ID) REFERENCES CONTACT(CONTACT_ID),
 17  	FOREIGN KEY(FK_DOCUMENT_ID) REFERENCES DOCUMENT(DOCUMENT_ID),
 18  	FOREIGN KEY(AUTHORIZATION_ID) REFERENCES AUTHORIZATION(AUTHORIZATION_ID),
 19  	CONSTRAINT SUPPLIER_UNIQUE_SKU UNIQUE(FK_CONTACT_ID, SKU)
 20  );

Table INVENTORY created.

SQL> 
SQL> CREATE TABLE EXHIBIT (
  2  	EXHIBIT_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  3  	FK_INVENTORY_ID NUMBER NOT NULL,
  4  	FK_PLAN_ID NUMBER,
  5  	FOREIGN KEY(FK_PLAN_ID) REFERENCES PLAN(PLAN_ID),
  6  	FOREIGN KEY(FK_INVENTORY_ID) REFERENCES INVENTORY(INVENTORY_ID)
  7  );

Table EXHIBIT created.

SQL> 
SQL> CREATE TABLE ORDER_ITEMS ( -- 'product line items customers placed in their carts.'
  2      ORDER_ITEM_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  3      FK_PROFILE_ID NUMBER NOT NULL,
  4  	TRANSACTION_ID VARCHAR2(32) NULL,
  5  	CREATED_AT TIMESTAMP NOT NULL,
  6  	QUANTITY NUMBER DEFAULT(1) NOT NULL,
  7  	UNIT_PRICE DECIMAL(10,2) NOT NULL,
  8  	LINE_TOTAL DECIMAL(10,2) NOT NULL,
  9      FK_INVENTORY_ID NUMBER NOT NULL,
 10      FOREIGN KEY(FK_PROFILE_ID) REFERENCES PROFILE(PROFILE_ID),
 11      FOREIGN KEY(FK_INVENTORY_ID) REFERENCES INVENTORY(INVENTORY_ID)
 12  );

Table ORDER_ITEMS created.

SQL> 
SQL> CREATE TABLE TASK_HISTORY(
  2  	TASK_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  3  	ASSIGNED_TO_PROFILE_ID NUMBER NOT NULL,
  4  	CREATED_BY_PROFILE_ID  NUMBER NOT NULL,
  5  	RELATED_ENTITY_TYPE VARCHAR2(32), -- 'order', 'supplier', etc.
  6  	RELATED_ENTITY_ID NUMBER,
  7  	TASK_TYPE VARCHAR2(32) NOT NULL,
  8  	TASK_TITLE VARCHAR2(64) NOT NULL,
  9  	DESCRIPTION VARCHAR2(128),
 10  	FK_DOCUMENT_ID NUMBER,
 11  	STATUS VARCHAR2(16) DEFAULT 'pending',
 12  	DUE_DATE TIMESTAMP,
 13  	COMPLETED_AT TIMESTAMP,
 14  	CREATED_AT TIMESTAMP DEFAULT SYSDATE,
 15  	UPDATED_AT TIMESTAMP DEFAULT SYSDATE,
 16  	FOREIGN KEY(FK_DOCUMENT_ID) REFERENCES DOCUMENT(DOCUMENT_ID),
 17  	CONSTRAINT chk_status CHECK (STATUS IN ('pending', 'in_progress', 'completed', 'cancelled'))
 18  );

Table TASK_HISTORY created.

SQL> 
SQL> /* VIEW of available exhibit inventory */
SQL> CREATE OR REPLACE VIEW VIEW_AVAILABLE_INVENTORY AS
  2      SELECT
  3      INVENTORY_ID,FK_CONTACT_ID,AUTHORIZATION_ID,SKU,PRODUCT_DESCRIPTION,UNIT_COST,LEAD_TIME_DAYS,
  4      PRODUCT_NAME,UNIT_OF_MEASURE,BASE_PRICE,QUANTITY_AVAILABLE,CREATED_AT,UPDATED_AT,FK_DOCUMENT_ID
  5  	FROM
  6  	INVENTORY WHERE QUANTITY_AVAILABLE > 0
  7  INTERSECT
  8      SELECT
  9      FK_INVENTORY_ID, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
 10      FROM
 11      EXHIBIT;

View VIEW_AVAILABLE_INVENTORY created.

SQL> 
SQL> CREATE OR REPLACE VIEW VIEW_SYSTEMROLE AS
  2  	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  r.ROLENAME, p.ACTIVE  FROM
  3  	USERROLE u
  4  	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID
  5  	JOIN ROLE r ON r.ROLE_ID = u.FK_ROLE_ID AND r.ROLE_ID = 100;

View VIEW_SYSTEMROLE created.

SQL> 
SQL> CREATE OR REPLACE VIEW VIEW_STAFF AS
  2  	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  r.ROLENAME, p.ACTIVE, c.FIRST_NAME, c.LAST_NAME, s.POSITION FROM
  3  	USERROLE u
  4  	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID
  5  	JOIN CONTACT c ON c.CONTACT_ID = p.FK_CONTACT_ID
  6  	JOIN ROLE r ON r.ROLE_ID = u.FK_ROLE_ID AND r.ROLE_ID = 300
  7      JOIN STAFF s on s.FK_PROFILE_ID = p.PROFILE_ID;

View VIEW_STAFF created.

SQL> 
SQL> CREATE OR REPLACE VIEW VIEW_SUPPLIER AS
  2  	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  r.ROLENAME, p.ACTIVE, c.FIRST_NAME, c.LAST_NAME FROM
  3  	USERROLE u
  4  	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID
  5  	JOIN CONTACT c ON c.CONTACT_ID = p.FK_CONTACT_ID
  6  	JOIN ROLE r ON r.ROLE_ID = u.FK_ROLE_ID AND r.ROLE_ID = 200;

View VIEW_SUPPLIER created.

SQL> 
SQL> CREATE OR REPLACE VIEW VIEW_CUSTOMER AS
  2  	SELECT u.FK_PROFILE_ID, p.FK_CONTACT_ID,  r.ROLENAME, p.ACTIVE, c.FIRST_NAME, c.LAST_NAME FROM
  3  	USERROLE u
  4  	JOIN PROFILE p ON p.PROFILE_ID=u.FK_PROFILE_ID
  5  	JOIN CONTACT c ON c.CONTACT_ID = p.FK_CONTACT_ID
  6  	JOIN ROLE r ON r.ROLE_ID = u.FK_ROLE_ID AND r.ROLE_ID = 400;

View VIEW_CUSTOMER created.

SQL> 
SQL> -- Trigger customer ordered items reservation
SQL> CREATE OR REPLACE TRIGGER ORDER_ITEMS_reserved
  2      AFTER INSERT ON ORDER_ITEMS FOR EACH ROW
  3      BEGIN
  4          UPDATE INVENTORY SET QUANTITY_AVAILABLE = QUANTITY_AVAILABLE - :NEW.QUANTITY WHERE INVENTORY_ID= :NEW.FK_INVENTORY_ID;
  5          DBMS_OUTPUT.put_line(' ORDER transaction. ' );
  6      END;
  7  /

Trigger ORDER_ITEMS_RESERVED compiled

SQL> 
SQL> -- Trigger Paid Invoice, generates subscription
SQL> CREATE OR REPLACE TRIGGER INVOICE_paid_subscription
  2      AFTER INSERT OR UPDATE ON INVOICE FOR EACH ROW
  3      BEGIN
  4          DBMS_OUTPUT.put_line(' SUBSCRIPTION transaction. ' );
  5          INSERT INTO SUBSCRIPTION (FK_PLAN_ID, FK_CONTACT_ID) VALUES (:NEW.FK_PLAN_ID, :NEW.FK_CONTACT_ID);
  6  		EXCEPTION
  7      	WHEN DUP_VAL_ON_INDEX THEN
  8  	  		DBMS_OUTPUT.put_line('Subscription exists for plan and contact. No new subscription created.');
  9  			UPDATE SUBSCRIPTION SET FK_PLAN_ID = :NEW.FK_PLAN_ID, FK_CONTACT_ID = :NEW.FK_CONTACT_ID, MODIFIED_BY = 1, MODIFIED_DT = SYSDATE
 10  			WHERE FK_PLAN_ID = :NEW.FK_PLAN_ID AND FK_CONTACT_ID = :NEW.FK_CONTACT_ID;
 11  		WHEN OTHERS THEN
 12  	  		DBMS_OUTPUT.put_line('Error in INVOICE_paid_subscription creating/updating subscription: ' || SQLERRM);
 13      END;
 14  /

Trigger INVOICE_PAID_SUBSCRIPTION compiled

SQL> 
SQL> CREATE OR REPLACE PROCEDURE sp_auth_supplier(
  2      p_status IN VARCHAR,
  3      p_supplier IN NUMBER,
  4      p_document_url IN VARCHAR
  5  ) IS
  6      l_id NUMBER;
  7  BEGIN
  8      BEGIN
  9        INSERT INTO DOCUMENT(DOCUMENT_ID, DOCUMENT_URL) VALUES (ISEQ_DOCUMENT.nextval, p_document_url)
 10        RETURNING DOCUMENT_ID INTO l_id; -- Captures the generated ID
 11      END;
 12      BEGIN
 13        INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, MODIFIED_BY, FK_DOCUMENT_ID) VALUES(p_status, p_supplier, 1, l_id);
 14        DBMS_OUTPUT.put_line('Reviewed Document ' || p_status );
 15      EXCEPTION
 16        WHEN OTHERS
 17        THEN
 18           DBMS_OUTPUT.put_line (SQLERRM);
 19           raise_application_error (-20500, 'failed while setting auth_supplier_status. ');
 20      END;
 21  END;
 22  
 23  /

Procedure SP_AUTH_SUPPLIER compiled

SQL> 
SQL> INSERT INTO ROLE (ROLE_ID, ROLENAME) VALUES (100, 'SYSTEM');

1 row inserted.

SQL> INSERT INTO ROLE (ROLE_ID, ROLENAME) VALUES (200, 'SUPPLIER');

1 row inserted.

SQL> INSERT INTO ROLE (ROLE_ID, ROLENAME) VALUES (300, 'STAFF');

1 row inserted.

SQL> INSERT INTO ROLE (ROLE_ID, ROLENAME) VALUES (400, 'CUSTOMER');

1 row inserted.

SQL> 
SQL> -- SYSTEM ROLE
SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('ART', 'SYSTEM', '000-1234', 'arts@art.com', 1);

1 row inserted.

SQL> -- STAFF ROLE
SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('Alice', 'Johnson', '650-555-9012', 'alice.johnson@art.com', 1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ( 'Karen',  'Park',     '206-555-0110', 'karen.park@art.com',    1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ( 'Jeese',  'Taylor',   '713-555-0106', 'grace.taylor@art.com',  1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ( 'James',  'Lee',      '206-555-0109', 'james.lee@art.com',     1);

1 row inserted.

SQL> -- SUPPLIER ROLE
SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('Stephen', 'Alec', '424-546-9312', 'salec@eizel.com', 1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('Henry',  'Martinez', '602-555-0107', 'henry@artsupply.com',   1);

1 row inserted.

SQL> -- CUSTOMER ROLE
SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('Carol',  'White',    '212-555-0102', 'carol.white@mail.com',  1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('Bob',    'Smith',    '212-555-0101', 'bob.smith@mail.com',    1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('David',  'Brown',    '312-555-0103', 'david.brown@mail.com',  1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('Emma',   'Davis',    '312-555-0104', 'emma.davis@mail.com',   1);

1 row inserted.

SQL> INSERT INTO CONTACT ( FIRST_NAME, LAST_NAME, PHONE_NO, EMAIL, MODIFIED_BY) VALUES ('Frank',  'Wilson',   '713-555-0105', 'frank.wilson@mail.com', 1);

1 row inserted.

SQL> 
SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('10001', 'New York',      'NY');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('90210', 'Beverly Hills', 'CA');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('94027', 'Atherton',      'CA');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('90211', 'Los Angeles',   'CA');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('60601', 'Chicago',       'IL');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('77001', 'Houston',       'TX');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('85001', 'Phoenix',       'AZ');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('98101', 'Seattle',       'WA');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('02101', 'Boston',        'MA');

1 row inserted.

SQL> INSERT INTO ZIP (ZIP, CITY, STATE) VALUES ('33101', 'Miami',         'FL');

1 row inserted.

SQL> 
SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (1, '789 Oak St, suite 10', '94027');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (2, '789 Oak St, suite 15', '94027');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (3, '789 Oak St, suite 1', '94027');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (4, '789 Oak St, suite 5', '94027');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (5, '789 Oak St, suite 15', '94027');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (6, '890 Venue Rd', '90210');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (7,  '200 Sunset Blvd',  '90210');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (8,  '100 Broadway',     '10001');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (9,  '300 Michigan Ave',  '60601');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP) VALUES (10,  '400 Main St',       '77001');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP ) VALUES (11,  '500 Central Ave',   '85001');

1 row inserted.

SQL> INSERT INTO ADDRESS (FK_CONTACT_ID, STREET, ZIP ) VALUES (12,  '600 Pike St',       '98101');

1 row inserted.

SQL> 
SQL> -- SYSTEMPROFILE
SQL> INSERT INTO PROFILE (FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (1, TRUE, 1);

1 row inserted.

SQL> -- STAFF PROFILE
SQL> INSERT INTO PROFILE (FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (2, TRUE, 2);

1 row inserted.

SQL> -- SUPPLIER
SQL> INSERT INTO PROFILE (FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (3, TRUE, 3);

1 row inserted.

SQL> 
SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (4,  4,  TRUE,  1);

1 row inserted.

SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (5,  5,  TRUE,  1);

1 row inserted.

SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (6,  6,  TRUE,  1);

1 row inserted.

SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (7,  7,  TRUE,  1);

1 row inserted.

SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (8,  8,  FALSE, 1);

1 row inserted.

SQL>   -- inactive
SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (9,  9,  TRUE,  1);

1 row inserted.

SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (10, 10, TRUE,  1);

1 row inserted.

SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (11, 11, TRUE,  1);

1 row inserted.

SQL> INSERT INTO PROFILE (PROFILE_ID, FK_CONTACT_ID, ACTIVE, MODIFIED_BY) VALUES (12, 12, TRUE,  1);

1 row inserted.

SQL> 
SQL> 
SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID, PASSWORDHASH) VALUES (1, 100, NULL);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (2, 300);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (3, 300);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (4,  300);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (5,  300);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (6,  200);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (7,  200);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (8,  400);

1 row inserted.

SQL>   -- inactive customer
SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (9,  400);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (10, 400);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (11, 400);

1 row inserted.

SQL> INSERT INTO USERROLE (FK_PROFILE_ID, FK_ROLE_ID) VALUES (12, 400);

1 row inserted.

SQL> 
SQL> INSERT INTO STAFF (FK_PROFILE_ID, POSITION) VALUES (2,  'Director');

1 row inserted.

SQL> INSERT INTO STAFF (FK_PROFILE_ID, POSITION) VALUES (3, 'Registrar');

1 row inserted.

SQL> INSERT INTO STAFF (FK_PROFILE_ID, POSITION) VALUES (4,  'Curator');

1 row inserted.

SQL> INSERT INTO STAFF (FK_PROFILE_ID, POSITION) VALUES (5, 'Art Handler');

1 row inserted.

SQL> 
SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (2,  'https://art.com/docs/consignment-stephen-crimson-horizon.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (3,  'https://art.com/docs/consignment-stephen-misty-valley.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (4,  'https://art.com/docs/consignment-stephen-twisted-form.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (5,  'https://art.com/docs/consignment-stephen-blue-symmetry.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (6,  'https://art.com/docs/consignment-henry-urban-solitude.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (7,  'https://art.com/docs/consignment-henry-earth-vessel.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (8,  'https://art.com/docs/consignment-henry-quiet-reflection.pdf');

1 row inserted.

SQL> 
SQL> -- Stephen — approved
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('approved', 6, 2, 1, SYSDATE, SYSDATE + 365);

1 row inserted.

SQL> -- Stephen — approved
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('approved', 6, 3, 1, SYSDATE, SYSDATE + 365);

1 row inserted.

SQL> -- Stephen — pending ('pending' status path)
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('pending',  6, 4, 1, SYSDATE, SYSDATE + 365);

1 row inserted.

SQL> -- Stephen — rejected ('rejected' status path)
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('rejected', 6, 5, 1, SYSDATE, SYSDATE + 30);

1 row inserted.

SQL> -- Henry — approved
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('approved', 7, 6, 1, SYSDATE, SYSDATE + 365);

1 row inserted.

SQL> -- Henry — approved
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('approved', 7, 7, 1, SYSDATE, SYSDATE + 365);

1 row inserted.

SQL> -- Henry — approved
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('approved', 7, 8, 1, SYSDATE, SYSDATE + 365);

1 row inserted.

SQL> -- Henry — pending
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('pending',  7, 7, 1, SYSDATE, SYSDATE + 180);

1 row inserted.

SQL> -- Henry — expired (EXPIRATION_DATE in the past)
SQL> INSERT INTO AUTHORIZATION (AUTHORIZATION_STATUS, FK_CONTACT_ID, FK_DOCUMENT_ID, MODIFIED_BY, AUTHORIZATION_DATE, EXPIRATION_DATE)
  2  VALUES ('expired',  7, 8, 1, SYSDATE - 365, SYSDATE - 30);

1 row inserted.

SQL> 
SQL> -- PLAN Documents for exhibition
SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (9,  'https://art.com/docs/plan-spring-gala-2025.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (10, 'https://art.com/docs/plan-summer-exhibit-2025.pdf');

1 row inserted.

SQL> INSERT INTO DOCUMENT (DOCUMENT_ID, DOCUMENT_URL) VALUES (11, 'https://art.com/docs/plan-fall-exhibition-2025.pdf');

1 row inserted.

SQL> 
SQL> -- PLANS
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Spring Gala Preview',      9,   4,   75.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 1
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Spring Gala Standard',     9,   8,  120.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 2
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Spring Gala VIP',          9,  12,  250.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 3
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Summer Exhibit Basic',    10,   4,   50.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 4
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Summer Exhibit Plus',     10,   8,  100.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 5
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Summer Exhibit Premium',  10,  16,  200.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 6
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Fall Exhibition Access',  11,   4,   60.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 7
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Fall Exhibition Full Day',11,   8,  110.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 8
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Fall Exhibition Weekend', 11,  24,  180.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 9
SQL> INSERT INTO PLAN (DESCRIPTION, FK_DOCUMENT_ID, DURATION, PRICE,  MODIFIED_BY) VALUES ('Annual Patron Pass',      11, 200,  500.00,  1);

1 row inserted.

SQL>    -- PLAN_ID 10
SQL> 
SQL> -- IV 1 — Crimson Horizon
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU, PRODUCT_DESCRIPTION, UNIT_COST, LEAD_TIME_DAYS,
  2      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  3  VALUES (6, 1, 'ART-S-001',
  4      'Oil on canvas, abstract expressionism, 24x36',   800.00, 7,
  5      'Crimson Horizon', 'piece',        1200.00,  5,  2);

1 row inserted.

SQL> 
SQL> -- IV 2 — Misty Valley
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (6, 2, 'ART-S-002',
  5      'Watercolor on paper, landscape, 18x24',          450.00, 5,
  6      'Misty Valley',    'piece',         700.00,  5,  3);

1 row inserted.

SQL> 
SQL> -- IV 3 — Twisted Form
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (6, 3, 'ART-S-003',
  5      'Bronze sculpture, abstract, 12 in height',      1500.00, 14,
  6      'Twisted Form',    'piece',        2200.00,  5,  4);

1 row inserted.

SQL> 
SQL> -- IV 4 — Blue Symmetry
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (7, 1, 'ART-S-004',
  5      'Acrylic on canvas, geometric, 30x30',            600.00, 7,
  6      'Blue Symmetry',   'piece',         950.00,  5,  2);

1 row inserted.

SQL> 
SQL> -- IV 5 — Urban Collage
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (7, 2, 'ART-S-005',
  5      'Mixed media on board, 20x20',                    350.00, 5,
  6      'Urban Collage',   'piece',         550.00,  6,  3);

1 row inserted.

SQL> 
SQL> -- IV 6 — Urban Solitude
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (6, 6, 'ART-H-001',
  5      'Photography print, archival, limited ed 1/10',   900.00, 3,
  6      'Urban Solitude',  'piece',        1500.00, 10,  6);

1 row inserted.

SQL> 
SQL> -- IV 7 — Earth Vessel
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (7, 7, 'ART-H-002',
  5      'Ceramic vessel, hand-thrown and glazed',          250.00, 10,
  6      'Earth Vessel',    'piece',         420.00,  8,  7);

1 row inserted.

SQL> 
SQL> -- IV 8 — Quiet Reflection
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (7, 8, 'ART-H-003',
  5      'Pastel drawing, portrait, 16x20',                300.00, 7,
  6      'Quiet Reflection','piece',         500.00,  4,  8);

1 row inserted.

SQL> 
SQL> -- IV 9 — Neon Dreams
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (6, 6, 'ART-H-004',
  5      'Digital print on aluminum, 24x36',               700.00, 5,
  6      'Neon Dreams',     'piece',        1100.00,  6,  6);

1 row inserted.

SQL> 
SQL> -- IV 10 — Woven Stories
SQL> INSERT INTO INVENTORY (FK_CONTACT_ID, AUTHORIZATION_ID, SKU,
  2      PRODUCT_DESCRIPTION,                            UNIT_COST, LEAD_TIME_DAYS,
  3      PRODUCT_NAME,     UNIT_OF_MEASURE, BASE_PRICE, QUANTITY_AVAILABLE, FK_DOCUMENT_ID)
  4  VALUES (6, 7, 'ART-H-005',
  5      'Textile wall hanging, woven, 36x48',             550.00, 14,
  6      'Woven Stories',   'piece',         850.00,  5,  7);

1 row inserted.

SQL> 
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (1,  1);

1 row inserted.

SQL>   -- Crimson Horizon   → Spring Preview
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (2,  2);

1 row inserted.

SQL>   -- Misty Valley      → Spring Standard
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (3,  3);

1 row inserted.

SQL>   -- Twisted Form      → Spring VIP
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (4,  4);

1 row inserted.

SQL>   -- Blue Symmetry     → Summer Basic
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (5,  5);

1 row inserted.

SQL>   -- Urban Collage     → Summer Plus
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (6,  6);

1 row inserted.

SQL>   -- Urban Solitude    → Summer Premium
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (7,  7);

1 row inserted.

SQL>   -- Earth Vessel      → Fall Access
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (8,  8);

1 row inserted.

SQL>   -- Quiet Reflection  → Fall Full Day
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (9,  9);

1 row inserted.

SQL>   -- Neon Dreams       → Fall Weekend
SQL> INSERT INTO EXHIBIT (FK_INVENTORY_ID, FK_PLAN_ID) VALUES (10, 10);

1 row inserted.

SQL>  -- Woven Stories     → Annual Pass
SQL> 
SQL> 
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (9, 1, DATE'2025-01-01', 75.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>    -- Bob — Spring Preview
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (10, 2, DATE'2025-01-02', 120.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>  -- Carol — Spring Standard
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (11, 3, DATE'2025-01-03', 250.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>  -- David — Spring VIP
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (12, 4, DATE'2025-01-04', 50.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>   -- Emma — Summer Basic
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (9, 5, DATE'2025-01-05', 100.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>   -- Bob — Summer Plus
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (10, 6, DATE'2025-01-06', 150.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>  -- Carol — Summer Premium
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (11, 7, DATE'2025-01-07', 60.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>   -- David — Fall Access
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (12, 8, DATE'2025-01-08', 110.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>  -- Emma — Fall Full Day
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (9, 9, DATE'2025-01-09', 180.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>   -- Bob — Fall Weekend
SQL> INSERT INTO INVOICE (FK_CONTACT_ID, FK_PLAN_ID, InvoiceDate, AmountPaid) VALUES (10, 10, DATE'2025-01-10', 500.00);
SUBSCRIPTION transaction. 


1 row inserted.

SQL>  -- Carol — Annual Pass
SQL> 
SQL> /
