-- Create custom extension table for item-level attributes
CREATE TABLE cust_item_attr (
    item             VARCHAR2(25)  NOT NULL,
    fabric_type      VARCHAR2(50),
    wash_instruction VARCHAR2(100),
    country_of_design VARCHAR2(30),
    sustainability_score NUMBER(3),
    custom_category  VARCHAR2(50),
    create_datetime  DATE DEFAULT SYSDATE NOT NULL,
    last_update_datetime DATE DEFAULT SYSDATE NOT NULL,
    last_update_id   VARCHAR2(30) DEFAULT USER NOT NULL,
    --
    CONSTRAINT pk_cust_item_attr PRIMARY KEY (item),
    CONSTRAINT fk_cia_item FOREIGN KEY (item)
        REFERENCES item_master(item)
);

-- Create index for common query patterns
CREATE INDEX idx_cia_fabric ON cust_item_attr(fabric_type);
CREATE INDEX idx_cia_category ON cust_item_attr(custom_category);

-- Grant access to the RMS application schema
GRANT SELECT, INSERT, UPDATE, DELETE ON cust_item_attr TO rms_app_role;

-- ITEM_MASTER has pre-built flex columns:
-- ITEM_MASTER.UDA_VALUE_01 through UDA_VALUE_15 (VARCHAR2)
-- ITEM_MASTER.UDA_NUM_01 through UDA_NUM_05 (NUMBER)
-- ITEM_MASTER.UDA_DATE_01 through UDA_DATE_05 (DATE)

-- Use flex attributes for simple extensions
UPDATE item_master
SET    uda_value_01 = 'Organic Cotton',     -- Fabric type
       uda_value_02 = 'Machine Wash Cold',   -- Care instruction
       uda_num_01   = 85,                    -- Sustainability score
       uda_date_01  = DATE '2027-06-30'      -- Certification expiry
WHERE  item = '100400012345';

-- Oracle's base code calls this hook after creating an item
CREATE OR REPLACE PACKAGE BODY pkg_cust_item_hooks AS

    PROCEDURE after_item_create (
        p_item      IN  item_master.item%TYPE,
        p_dept      IN  item_master.dept%TYPE,
        p_class     IN  item_master.class%TYPE,
        p_subclass  IN  item_master.subclass%TYPE,
        p_status    IN  item_master.status%TYPE
    ) IS
    BEGIN
        -- Custom logic: Auto-populate extension attributes
        INSERT INTO cust_item_attr (
            item, custom_category, create_datetime
        ) VALUES (
            p_item,
            fn_derive_custom_category(p_dept, p_class),
            SYSDATE
        );
        
        -- Custom logic: Send notification to PLM system
        pkg_cust_integration.notify_plm_item_created(p_item);
        
    EXCEPTION
        WHEN OTHERS THEN
            -- Log error but don't block base item creation
            pkg_cust_error_log.log_error(
                'AFTER_ITEM_CREATE', p_item, SQLERRM
            );
    END after_item_create;

END pkg_cust_item_hooks;
/

BEGIN
    ORDS.DEFINE_MODULE(
        p_module_name => 'cust_items',
        p_base_path   => '/custom/items/',
        p_status      => 'PUBLISHED'
    );
    
    ORDS.DEFINE_HANDLER(
        p_module_name => 'cust_items',
        p_pattern     => ':item_id/attributes',
        p_method      => 'GET',
        p_source_type => 'json/collection',
        p_source      => '
            SELECT im.item, im.item_desc, im.dept, im.class,
                   ca.fabric_type, ca.wash_instruction,
                   ca.sustainability_score, ca.custom_category
            FROM   item_master im
            LEFT JOIN cust_item_attr ca ON im.item = ca.item
            WHERE  im.item = :item_id'
    );
    COMMIT;
END;
/