/*
 * Inconsistent Item Audience Report
 *
 * Your library may use a different scheme for location codes, but at
 * Stark Library location codes generally take the form
 *
 *     +----------+     +------------+     +----------+     +---------+
 *     |  Branch  |  +  |  Audience  |  +  |  Format  |  +  |  Genre  |
 *     +----------+     +------------+     +----------+     +---------+
 *       2 chars            1 char            1 char          1 char
 *
 * Compare the third "item audience" character of item location codes to the
 * leading J or YA of call numbers to identify items which require resolution.
 *
 * Note:  This might have been better handled using the subfield table
 * instead of brute-force editing the |a out of the call number as I did.
 * If you have a more elegant solution which runs about as quickly or faster,
 * please post it below.
 *
 * Bob Gaydos <bgaydos@starklibrary.org>
 * March 26, 2025
 */
WITH CallNumberPrefix AS
(
    SELECT
        i.id AS item_record_id,
        UPPER(
            CASE
                WHEN LEFT(TRIM(c.field_content) || ' ', 2) = 'J '
                    THEN 'J'
                WHEN LEFT(TRIM(c.field_content) || ' ', 3) = 'YA '
                    THEN 'YA'
                WHEN LEFT(TRIM(c.field_content) || ' ', 4) = '|aJ '
                    THEN 'J'
                WHEN LEFT(TRIM(c.field_content) || ' ', 5) = '|aYA '
                    THEN 'YA'
                WHEN TRIM(LEFT(TRIM(c.field_content), POSITION(' ' IN TRIM(c.field_content)))) LIKE '|a%'
                    THEN TRIM(SUBSTRING(LEFT(TRIM(c.field_content), POSITION(' ' IN TRIM(c.field_content))), 3))
                ELSE
                    TRIM(LEFT(TRIM(c.field_content), POSITION(' ' IN TRIM(c.field_content))))
            END) AS call_number_prefix
    FROM item_record i
    INNER JOIN record_metadata rm
       ON rm.id = i.id
    LEFT OUTER JOIN varfield c
       ON c.record_id = i.id
      AND c.varfield_type_code = 'c'
    WHERE TRIM(COALESCE(rm.campus_code, '')) = ''
),
Audience AS
(
    SELECT
        i.id AS item_record_id,
        LOWER(TRIM(RIGHT(LEFT(i.location_code || '     ', 3), 1))) AS audience
    FROM item_record i
    INNER JOIN record_metadata rm
       ON rm.id = i.id
    WHERE TRIM(COALESCE(rm.campus_code, '')) = ''
),
Discrepancies AS
(
    SELECT
        a.item_record_id,
        'Adult Location Code, Juvenile Call Number' AS discrepancy
    FROM Audience a
    INNER JOIN CallNumberPrefix cnp
       ON cnp.item_record_id = a.item_record_id
    WHERE a.audience = 'a'
      AND cnp.call_number_prefix = 'J'
    --
    UNION
    --
    SELECT
        a.item_record_id,
        'Adult Location Code, YA Call Number' AS discrepancy
    FROM Audience a
    INNER JOIN CallNumberPrefix cnp
       ON cnp.item_record_id = a.item_record_id
    WHERE a.audience = 'a'
      AND cnp.call_number_prefix = 'YA'
    --
    UNION
    --
    SELECT
        a.item_record_id,
        'Child Location Code, YA Call Number'
    FROM Audience a
    INNER JOIN CallNumberPrefix cnp
       ON cnp.item_record_id = a.item_record_id
    WHERE a.audience = 'c'
      AND cnp.call_number_prefix = 'YA'
    --
    UNION
    --
    SELECT
        a.item_record_id,
        'Child Location Code, Call Number not J *'
    FROM Audience a
    INNER JOIN CallNumberPrefix cnp
       ON cnp.item_record_id = a.item_record_id
    WHERE a.audience = 'c'
      AND cnp.call_number_prefix NOT IN ('J', 'YA')
    --
    UNION
    --
    SELECT
        a.item_record_id,
        'Teen Location Code, Juvenile Call Number'
    FROM Audience a
    INNER JOIN CallNumberPrefix cnp
       ON cnp.item_record_id = a.item_record_id
    WHERE a.audience IN ('t', 'g')
      AND cnp.call_number_prefix = 'J'
    --
    UNION
    --
    SELECT
        a.item_record_id,
        'Teen Location Code, Call Number not YA *'
    FROM Audience a
    INNER JOIN CallNumberPrefix cnp
       ON cnp.item_record_id = a.item_record_id
    WHERE a.audience IN ('t', 'g')
      AND cnp.call_number_prefix NOT IN ('J', 'YA')
)
SELECT
    rm.record_type_code || rm.record_num::VARCHAR(8) || 'a' AS "Item Record Num",
    TRIM(COALESCE(ib.field_content, '')) AS "Item Barcode",
    TRIM(i.location_code) AS "Location Code",
    d.discrepancy AS "Discrepancy",
    CASE
        WHEN TRIM(COALESCE(ic.field_content, '')) LIKE '|a%'
            THEN TRIM(SUBSTRING(TRIM(ic.field_content), 3))
        ELSE
            TRIM(COALESCE(ic.field_content, ''))
    END AS "Call Number",
    TRIM(COALESCE(brp.best_title, '')) AS "Title",
    TRIM(COALESCE(brp.best_author, '')) AS "Author"
FROM Discrepancies d
INNER JOIN item_record i
   ON i.id = d.item_record_id
INNER JOIN record_metadata rm
   ON rm.id = d.item_record_id
LEFT OUTER JOIN bib_record_item_record_link bil
   ON bil.item_record_id = d.item_record_id
LEFT OUTER JOIN bib_record_property brp
   ON brp.bib_record_id = bil.bib_record_id
LEFT OUTER JOIN varfield ib
   ON ib.record_id = d.item_record_id
  AND ib.varfield_type_code = 'b'
LEFT OUTER JOIN varfield ic
   ON ic.record_id = d.item_record_id
  AND ic.varfield_type_code = 'c'
ORDER BY
    "Discrepancy",
    "Title",
    "Author"
;