-- ==========================================
-- Populate / refresh spells table from scrolls
-- ==========================================
INSERT INTO spells (
    name, WAR, MNK, WHM, BLM, RDM, THF, PLD, DRK, BST,
    BRD, RNG, SAM, NIN, DRG, SMN, BLU, COR, PUP, DNC,
    SCH, GEO, RUN
)
SELECT
    -- Strip off leading "Scroll of " (case-insensitive)
    regexp_replace(name, '^scroll of\\s+', '', 'i')                                          AS name,

    substring(description FROM 'WAR\\s+Lv\\.\\s*(\\d+)')::int                                     AS WAR,
    substring(description FROM 'MNK\\s+Lv\\.\\s*(\\d+)')::int                                     AS MNK,
    substring(description FROM 'WHM\\s+Lv\\.\\s*(\\d+)')::int                                     AS WHM,
    substring(description FROM 'BLM\\s+Lv\\.\\s*(\\d+)')::int                                     AS BLM,
    substring(description FROM 'RDM\\s+Lv\\.\\s*(\\d+)')::int                                     AS RDM,
    substring(description FROM 'THF\\s+Lv\\.\\s*(\\d+)')::int                                     AS THF,
    substring(description FROM 'PLD\\s+Lv\\.\\s*(\\d+)')::int                                     AS PLD,
    substring(description FROM 'DRK\\s+Lv\\.\\s*(\\d+)')::int                                     AS DRK,
    substring(description FROM 'BST\\s+Lv\\.\\s*(\\d+)')::int                                     AS BST,
    substring(description FROM 'BRD\\s+Lv\\.\\s*(\\d+)')::int                                     AS BRD,
    substring(description FROM 'RNG\\s+Lv\\.\\s*(\\d+)')::int                                     AS RNG,
    substring(description FROM 'SAM\\s+Lv\\.\\s*(\\d+)')::int                                     AS SAM,
    substring(description FROM 'NIN\\s+Lv\\.\\s*(\\d+)')::int                                     AS NIN,
    substring(description FROM 'DRG\\s+Lv\\.\\s*(\\d+)')::int                                     AS DRG,
    substring(description FROM 'SMN\\s+Lv\\.\\s*(\\d+)')::int                                     AS SMN,
    substring(description FROM 'BLU\\s+Lv\\.\\s*(\\d+)')::int                                     AS BLU,
    substring(description FROM 'COR\\s+Lv\\.\\s*(\\d+)')::int                                     AS COR,
    substring(description FROM 'PUP\\s+Lv\\.\\s*(\\d+)')::int                                     AS PUP,
    substring(description FROM 'DNC\\s+Lv\\.\\s*(\\d+)')::int                                     AS DNC,
    substring(description FROM 'SCH\\s+Lv\\.\\s*(\\d+)')::int                                     AS SCH,
    substring(description FROM 'GEO\\s+Lv\\.\\s*(\\d+)')::int                                     AS GEO,
    substring(description FROM 'RUN\\s+Lv\\.\\s*(\\d+)')::int                                     AS RUN
FROM usable_items
WHERE type_description = 'SCROLL'
ON CONFLICT (name) DO UPDATE SET
    WAR = EXCLUDED.WAR,
    MNK = EXCLUDED.MNK,
    WHM = EXCLUDED.WHM,
    BLM = EXCLUDED.BLM,
    RDM = EXCLUDED.RDM,
    THF = EXCLUDED.THF,
    PLD = EXCLUDED.PLD,
    DRK = EXCLUDED.DRK,
    BST = EXCLUDED.BST,
    BRD = EXCLUDED.BRD,
    RNG = EXCLUDED.RNG,
    SAM = EXCLUDED.SAM,
    NIN = EXCLUDED.NIN,
    DRG = EXCLUDED.DRG,
    SMN = EXCLUDED.SMN,
    BLU = EXCLUDED.BLU,
    COR = EXCLUDED.COR,
    PUP = EXCLUDED.PUP,
    DNC = EXCLUDED.DNC,
    SCH = EXCLUDED.SCH,
    GEO = EXCLUDED.GEO,
    RUN = EXCLUDED.RUN;