------------------------------------------------------------------------
-- OCI Enterprise AI Demo
-- Procurement RAG + Structured Data
--
-- Database:
--   Oracle Autonomous AI Database
--
-- Purpose:
--   Demonstrate an AI Agent combining:
--     1. RAG / semantic search against procurement policies
--     2. Structured SQL queries against procurement data
------------------------------------------------------------------------


------------------------------------------------------------------------
-- 1. VENDORS
------------------------------------------------------------------------

CREATE TABLE acme.vendors (
    vendor_id              NUMBER GENERATED ALWAYS AS IDENTITY
                           CONSTRAINT pk_vendors PRIMARY KEY,

    vendor_code            VARCHAR2(20)  NOT NULL
                           CONSTRAINT uk_vendors_code UNIQUE,

    vendor_name            VARCHAR2(200) NOT NULL,

    vendor_category        VARCHAR2(100),

    procurement_status     VARCHAR2(30) NOT NULL,

    risk_level             VARCHAR2(20),

    registration_status    VARCHAR2(30),

    approved_date          DATE,

    annual_spend           NUMBER(15,2) DEFAULT 0,

    contact_name           VARCHAR2(100),

    contact_email          VARCHAR2(200),

    created_date           DATE DEFAULT SYSDATE,

    CONSTRAINT ck_vendor_status
        CHECK (procurement_status IN
               ('APPROVED', 'PENDING', 'REJECTED', 'SUSPENDED')),

    CONSTRAINT ck_vendor_risk
        CHECK (risk_level IN
               ('LOW', 'MEDIUM', 'HIGH')),

    CONSTRAINT ck_registration
        CHECK (registration_status IN
               ('COMPLETE', 'PENDING', 'INCOMPLETE'))
);


------------------------------------------------------------------------
-- 2. PURCHASE ORDERS
------------------------------------------------------------------------

CREATE TABLE acme.purchase_orders (
    po_id                  NUMBER GENERATED ALWAYS AS IDENTITY
                           CONSTRAINT pk_purchase_orders PRIMARY KEY,

    po_number              VARCHAR2(30) NOT NULL
                           CONSTRAINT uk_po_number UNIQUE,

    vendor_id              NUMBER NOT NULL,

    po_date                DATE NOT NULL,

    department             VARCHAR2(100),

    buyer_name             VARCHAR2(100),

    description            VARCHAR2(500),

    po_amount              NUMBER(15,2) NOT NULL,

    currency_code          VARCHAR2(3) DEFAULT 'USD',

    po_status              VARCHAR2(30) NOT NULL,

    approval_status        VARCHAR2(30),

    CONSTRAINT fk_po_vendor
        FOREIGN KEY (vendor_id)
        REFERENCES acme.vendors(vendor_id),

    CONSTRAINT ck_po_status
        CHECK (po_status IN
               ('DRAFT', 'OPEN', 'PARTIALLY_RECEIVED',
                'RECEIVED', 'CLOSED', 'CANCELLED')),

    CONSTRAINT ck_po_approval
        CHECK (approval_status IN
               ('PENDING', 'APPROVED', 'REJECTED'))
);


------------------------------------------------------------------------
-- 3. RECEIPTS
------------------------------------------------------------------------

CREATE TABLE acme.receipts (
    receipt_id             NUMBER GENERATED ALWAYS AS IDENTITY
                           CONSTRAINT pk_receipts PRIMARY KEY,

    receipt_number         VARCHAR2(30) NOT NULL
                           CONSTRAINT uk_receipt_number UNIQUE,

    po_id                  NUMBER NOT NULL,

    receipt_date           DATE NOT NULL,

    received_by            VARCHAR2(100),

    quantity_received      NUMBER(12,2),

    receipt_status         VARCHAR2(30),

    CONSTRAINT fk_receipt_po
        FOREIGN KEY (po_id)
        REFERENCES acme.purchase_orders(po_id),

    CONSTRAINT ck_receipt_status
        CHECK (receipt_status IN
               ('RECEIVED', 'PARTIAL', 'REJECTED'))
);


------------------------------------------------------------------------
-- 4. INVOICES
------------------------------------------------------------------------

CREATE TABLE acme.invoices (
    invoice_id             NUMBER GENERATED ALWAYS AS IDENTITY
                           CONSTRAINT pk_invoices PRIMARY KEY,

    invoice_number         VARCHAR2(40) NOT NULL
                           CONSTRAINT uk_invoice_number UNIQUE,

    vendor_id              NUMBER NOT NULL,

    po_id                  NUMBER,

    invoice_date           DATE NOT NULL,

    invoice_amount         NUMBER(15,2) NOT NULL,

    due_date               DATE,

    payment_status         VARCHAR2(30),

    matching_status        VARCHAR2(30),

    exception_flag         VARCHAR2(1) DEFAULT 'N',

    exception_reason       VARCHAR2(500),

    CONSTRAINT fk_invoice_vendor
        FOREIGN KEY (vendor_id)
        REFERENCES acme.vendors(vendor_id),

    CONSTRAINT fk_invoice_po
        FOREIGN KEY (po_id)
        REFERENCES acme.purchase_orders(po_id),

    CONSTRAINT ck_invoice_payment
        CHECK (payment_status IN
               ('PENDING', 'APPROVED', 'PAID', 'ON_HOLD')),

    CONSTRAINT ck_invoice_matching
        CHECK (matching_status IN
               ('MATCHED', 'UNMATCHED', 'EXCEPTION')),

    CONSTRAINT ck_invoice_exception
        CHECK (exception_flag IN ('Y', 'N'))
);


------------------------------------------------------------------------
-- 5. SAMPLE VENDORS
------------------------------------------------------------------------

INSERT INTO acme.vendors (
    vendor_code,
    vendor_name,
    vendor_category,
    procurement_status,
    risk_level,
    registration_status,
    approved_date,
    annual_spend,
    contact_name,
    contact_email
)
VALUES (
    'V1001',
    'Global Industrial Supply',
    'Industrial Equipment',
    'PENDING',
    'MEDIUM',
    'PENDING',
    NULL,
    0,
    'John Miller',
    'john.miller@globalindustrial.example'
);


INSERT INTO acme.vendors (
    vendor_code,
    vendor_name,
    vendor_category,
    procurement_status,
    risk_level,
    registration_status,
    approved_date,
    annual_spend,
    contact_name,
    contact_email
)
VALUES (
    'V1002',
    'OfficePro Solutions',
    'Office Supplies',
    'APPROVED',
    'LOW',
    'COMPLETE',
    DATE '2023-04-15',
    125000,
    'Sarah Johnson',
    'sarah.johnson@officepro.example'
);


INSERT INTO acme.vendors (
    vendor_code,
    vendor_name,
    vendor_category,
    procurement_status,
    risk_level,
    registration_status,
    approved_date,
    annual_spend,
    contact_name,
    contact_email
)
VALUES (
    'V1003',
    'TechSource Systems',
    'IT Hardware',
    'APPROVED',
    'LOW',
    'COMPLETE',
    DATE '2022-09-10',
    485000,
    'Michael Chen',
    'michael.chen@techsource.example'
);


INSERT INTO acme.vendors (
    vendor_code,
    vendor_name,
    vendor_category,
    procurement_status,
    risk_level,
    registration_status,
    approved_date,
    annual_spend,
    contact_name,
    contact_email
)
VALUES (
    'V1004',
    'Emergency Equipment Services',
    'Emergency Equipment',
    'APPROVED',
    'MEDIUM',
    'COMPLETE',
    DATE '2021-06-20',
    210000,
    'David Brown',
    'david.brown@ees.example'
);


INSERT INTO acme.vendors (
    vendor_code,
    vendor_name,
    vendor_category,
    procurement_status,
    risk_level,
    registration_status,
    approved_date,
    annual_spend,
    contact_name,
    contact_email
)
VALUES (
    'V1005',
    'Precision Manufacturing',
    'Manufacturing Equipment',
    'PENDING',
    'HIGH',
    'INCOMPLETE',
    NULL,
    0,
    'Lisa Wilson',
    'lisa.wilson@precision.example'
);


------------------------------------------------------------------------
-- 6. SAMPLE PURCHASE ORDERS
------------------------------------------------------------------------

INSERT INTO acme.purchase_orders (
    po_number,
    vendor_id,
    po_date,
    department,
    buyer_name,
    description,
    po_amount,
    currency_code,
    po_status,
    approval_status
)
SELECT
    'PO-2026-1001',
    vendor_id,
    DATE '2026-09-01',
    'Information Technology',
    'Robert Smith',
    'Network infrastructure equipment',
    18500,
    'USD',
    'RECEIVED',
    'APPROVED'
FROM acme.vendors
WHERE vendor_code = 'V1003';


INSERT INTO acme.purchase_orders (
    po_number,
    vendor_id,
    po_date,
    department,
    buyer_name,
    description,
    po_amount,
    currency_code,
    po_status,
    approval_status
)
SELECT
    'PO-2026-1002',
    vendor_id,
    DATE '2026-09-03',
    'Facilities',
    'Karen Davis',
    'Office furniture and equipment',
    7500,
    'USD',
    'RECEIVED',
    'APPROVED'
FROM acme.vendors
WHERE vendor_code = 'V1002';


INSERT INTO acme.purchase_orders (
    po_number,
    vendor_id,
    po_date,
    department,
    buyer_name,
    description,
    po_amount,
    currency_code,
    po_status,
    approval_status
)
SELECT
    'PO-2026-1003',
    vendor_id,
    DATE '2026-09-05',
    'Operations',
    'James Anderson',
    'Emergency replacement equipment',
    42000,
    'USD',
    'OPEN',
    'APPROVED'
FROM acme.vendors
WHERE vendor_code = 'V1004';


------------------------------------------------------------------------
-- 7. SAMPLE RECEIPTS
------------------------------------------------------------------------

INSERT INTO acme.receipts (
    receipt_number,
    po_id,
    receipt_date,
    received_by,
    quantity_received,
    receipt_status
)
SELECT
    'RCV-2026-5001',
    po_id,
    DATE '2026-09-08',
    'Tom Wilson',
    10,
    'RECEIVED'
FROM acme.purchase_orders
WHERE po_number = 'PO-2026-1001';


INSERT INTO acme.receipts (
    receipt_number,
    po_id,
    receipt_date,
    received_by,
    quantity_received,
    receipt_status
)
SELECT
    'RCV-2026-5002',
    po_id,
    DATE '2026-09-09',
    'Emily Davis',
    25,
    'RECEIVED'
FROM acme.purchase_orders
WHERE po_number = 'PO-2026-1002';


------------------------------------------------------------------------
-- 8. SAMPLE INVOICES
------------------------------------------------------------------------

-- Normal 3-way-match scenario
INSERT INTO acme.invoices (
    invoice_number,
    vendor_id,
    po_id,
    invoice_date,
    invoice_amount,
    due_date,
    payment_status,
    matching_status,
    exception_flag,
    exception_reason
)
SELECT
    'INV-2026-7001',
    vendor_id,
    po_id,
    DATE '2026-09-09',
    18500,
    DATE '2026-10-09',
    'APPROVED',
    'MATCHED',
    'N',
    NULL
FROM acme.purchase_orders po
JOIN acme.vendors v
    ON po.vendor_id = v.vendor_id
WHERE po.po_number = 'PO-2026-1001';


-- Invoice with a PO but no receipt
INSERT INTO acme.invoices (
    invoice_number,
    vendor_id,
    po_id,
    invoice_date,
    invoice_amount,
    due_date,
    payment_status,
    matching_status,
    exception_flag,
    exception_reason
)
SELECT
    'INV-2026-7002',
    vendor_id,
    po_id,
    DATE '2026-09-10',
    42000,
    DATE '2026-10-10',
    'ON_HOLD',
    'EXCEPTION',
    'Y',
    'Purchase order exists but receipt has not been recorded'
FROM acme.purchase_orders
WHERE po_number = 'PO-2026-1003';


-- Invoice without a purchase order
INSERT INTO acme.invoices (
    invoice_number,
    vendor_id,
    po_id,
    invoice_date,
    invoice_amount,
    due_date,
    payment_status,
    matching_status,
    exception_flag,
    exception_reason
)
SELECT
    'INV-2026-7003',
    vendor_id,
    NULL,
    DATE '2026-09-11',
    12500,
    DATE '2026-10-11',
    'ON_HOLD',
    'EXCEPTION',
    'Y',
    'Invoice received without a purchase order'
FROM acme.vendors
WHERE vendor_code = 'V1002';


------------------------------------------------------------------------
-- 9. COMMIT
------------------------------------------------------------------------

COMMIT;


------------------------------------------------------------------------
-- 10. VALIDATION QUERIES
------------------------------------------------------------------------

-- Vendors
SELECT
    vendor_code,
    vendor_name,
    procurement_status,
    registration_status,
    risk_level,
    annual_spend
FROM acme.vendors
ORDER BY vendor_code;


-- Purchase orders
SELECT
    po.po_number,
    v.vendor_name,
    po.department,
    po.description,
    po.po_amount,
    po.po_status,
    po.approval_status
FROM acme.purchase_orders po
JOIN acme.vendors v
    ON po.vendor_id = v.vendor_id
ORDER BY po.po_number;


-- Invoices and matching status
SELECT
    i.invoice_number,
    v.vendor_name,
    i.po_id,
    i.invoice_amount,
    i.matching_status,
    i.payment_status,
    i.exception_flag,
    i.exception_reason
FROM acme.invoices i
JOIN acme.vendors v
    ON i.vendor_id = v.vendor_id
ORDER BY i.invoice_number;