CREATE USER lending IDENTIFIED BY "<change-me-password>" QUOTA UNLIMITED ON USERS;
GRANT CREATE SESSION, CREATE TABLE TO lending;

CREATE TABLE lending.loan_applications (
  application_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  applicant_name VARCHAR2(100) NOT NULL,
  assigned_officer VARCHAR2(100) NOT NULL,
  in_underwriting_queue CHAR(1) DEFAULT 'N' NOT NULL,
  status VARCHAR2(30) NOT NULL,
  requested_amount NUMBER(12,2),
  annual_income NUMBER(12,2),
  customer_ssn VARCHAR2(11),
  contact_email VARCHAR2(255),
  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

INSERT INTO lending.loan_applications (
  applicant_name,
  assigned_officer,
  in_underwriting_queue,
  status,
  requested_amount,
  annual_income,
  customer_ssn,
  contact_email
) VALUES (
  'Ana Perez',
  'loan.officer1',
  'N',
  'INTAKE',
  250000,
  145000,
  '123-45-6789',
  'ana.perez@example.com'
);

INSERT INTO lending.loan_applications (
  applicant_name,
  assigned_officer,
  in_underwriting_queue,
  status,
  requested_amount,
  annual_income,
  customer_ssn,
  contact_email
) VALUES (
  'Ben Carter',
  'loan.officer1',
  'Y',
  'UNDERWRITING',
  180000,
  120000,
  '234-56-7890',
  'ben.carter@example.com'
);

INSERT INTO lending.loan_applications (
  applicant_name,
  assigned_officer,
  in_underwriting_queue,
  status,
  requested_amount,
  annual_income,
  customer_ssn,
  contact_email
) VALUES (
  'Clara Singh',
  'loan.officer2',
  'N',
  'INTAKE',
  310000,
  210000,
  '345-67-8901',
  'clara.singh@example.com'
);

COMMIT;
