-- ARTISTS VIEW
-- tables. Person names are displayed as "Firstname Surname (Secondary Name)"

-- Warning: As a result of the LEFT OUTER JOIN, a NULL value in additional
-- names will imply that it has display = 'secondary', but this is ok, because
-- the default is display = 'rarely' so NULL values in additional names should
-- not exist

--   'artist_id': Universal artist_id
--        'name': COOL. Show full name plus any secondary name in brackets
--                e.g. Firstname Surname (Secondary Name)
--   'shortname': VERY COOL. Show surnames if surname is unique, otherwise
--                also show initials. For groups, show full name if no 
--                explicit shortname exists.
--                e.g. 'Uniquename' or 'F Commonname' or 'Group Name' or 'GN'
-- 'artist_type': person or group
-- 'contactable': Whether or not the artist is contactable. I think this is
--                unncessary for the moment, because it is a big time waster.
--                DISABLED FOR NOW

CREATE VIEW view_artists AS
-- Artists with a unique surname
SELECT p.artist_id,
       -- full name
         CONCAT_WS(' ', p.firstname, p.surname,
           IF(an.name_display = 'secondary', -- if the additional name is 'secondary'
               CONCAT('(',an.firstname,' ', an.surname,  ')'),
               NULL)
         ) AS name,
       -- surname for sorting
       p.surname AS sortname,
       -- short name
       p.surname AS shortname,
       'person' AS artist_type
       -- ,EXISTS (SELECT * FROM contactable WHERE artist_id = p.artist_id) AS contactable
FROM persons AS p LEFT OUTER JOIN additional_names AS an USING (artist_id)
WHERE 1 = (SELECT COUNT(*) FROM persons WHERE persons.surname = p.surname)
UNION
-- Artists with a shared surname
SELECT p.artist_id,
       -- full name
       CONCAT_WS(' ',p.firstname,p.surname,
           IF(an.name_display = 'secondary', -- if the additional name is 'secondary'
               CONCAT('(', an.firstname, ' ', an.surname, ')'),
           NULL)
       ) AS name,
       -- for sorting
       CONCAT_WS(' ', 
            p.surname, 
            CONCAT(LEFT(p.firstname,1),LEFT(SUBSTRING(p.firstname, LOCATE(' ',p.firstname),2),LOCATE(' ',p.firstname)))
       ) AS sortname,
       -- short name
       CONCAT_WS(' ', CONCAT( 
             LEFT(p.firstname,1),LEFT(SUBSTRING(p.firstname, LOCATE(' ',p.firstname),2),LOCATE(' ',p.firstname))), 
             p.surname
       ) AS shortname,
       'person' AS artist_type
       -- ,EXISTS (SELECT * FROM contactable WHERE artist_id = p.artist_id) AS contactable
FROM persons AS p 
     LEFT OUTER JOIN additional_names AS an USING (artist_id) 
     -- LEFT OUTER JOIN contactable AS c USING (artist_id)
WHERE 1 < (SELECT COUNT(*) FROM persons WHERE persons.surname = p.surname)
UNION
-- Groups
SELECT artist_id,
         name,
         -- For sorting
         -- Using TRIM() here to remove articles is broken in MySQL 5.0, 5.1
         -- plus, it wouldn't be language independent
         name AS sortname,
         COALESCE(shortname,name) AS shortname,
         'group' AS artist_type
         -- ,EXISTS (SELECT * FROM contactable WHERE artist_id = groups.artist_id) AS contactable
FROM groups;

-- -- Old, but perhaps quicker version (no nested queries)
-- -- shortname shows initials and the surname (only western names)
--   CREATE VIEW IF NOT EXISTS view_artists AS
--     SELECT 
--            persons.artist_id,
--            -- Full name with secondary name in brackets
--            CONCAT_WS(' ',persons.firstname,persons.surname,
--              CONCAT('(',additional_names.firstname,' ',
--                         additional_names.surname,  ')')) AS name,
--            -- Shortname or Initials and Surname
--            CONCAT_WS(' ', CONCAT( LEFT(persons.firstname,1),
--                LEFT(SUBSTRING(persons.firstname,
--                     LOCATE(' ',persons.firstname),2),
--                LOCATE(' ',persons.firstname))), 
--                persons.surname) AS shortname,
--            -- 'person' is of course the artist_type
--            'person' AS artist_type
--     FROM persons LEFT OUTER JOIN additional_names USING (artist_id)
--     WHERE additional_names.display = 'secondary' OR 
--                 ISNULL(additional_names.display)
--     UNION
--     SELECT artist_id,
--            name,
--            COALESCE(shortname,name) AS shortname,
--            'group' AS artist_type
--     FROM groups
--   ;

-- SELECT * FROM view_artists;


-- RECORDINGS VIEW (unimportant columns have been commmented out)
-- recording_id
-- performance_id
-- work_id
-- movement_number
-- filename
-- format
-- quality
-- name (for standardisation?)
-- shortname (?)

CREATE VIEW view_artist_recordings AS
SELECT r.recording_id,
       r.work_id,
       p.artist_id,
       p.role,
       wt.title,
       '' AS movement_title,
       r.duration
FROM performs AS p NATURAL JOIN recordings AS r LEFT OUTER JOIN work_titles AS wt USING (work_id)
WHERE wt.display = 'primary' AND r.movement_number IS NULL
UNION
SELECT r.recording_id,
       r.work_id,
       p.artist_id,
       p.role,
       wt.title,
       CONCAT_WS(' ',wt.title,CONCAT('(',mt.movement_number,'. ',mt.movement_title,')')) AS movement_title,
       r.duration
FROM performs AS p NATURAL JOIN recordings AS r LEFT OUTER JOIN movement_titles AS mt USING (work_id,movement_number), work_titles AS wt
WHERE mt.display = 'primary' AND r.movement_number IS NOT NULL AND wt.work_id = r.work_id
;

-- WORKS
-- Aligning a single title (primary plus secondary in brackets) to a work, 
-- with a single composer name. If multiple composers create a work it will
-- appear twice.
-- If there is no primary title, one is made up using the key and form

CREATE VIEW view_works AS
SELECT works.work_id,
       va.artist_id AS composer_id,
       CONCAT_WS(' ',pt.title,CONCAT(_utf8'(',st.title,_utf8')')) AS title,
       va.shortname AS composer
FROM view_artists AS va
     NATURAL JOIN create_work AS cw 
     NATURAL JOIN works 
     LEFT OUTER JOIN work_titles AS pt ON pt.work_id=works.work_id AND pt.display = 'primary'
     LEFT OUTER JOIN work_titles AS st ON st.work_id=pt.work_id AND st.display='secondary'
WHERE cw.contribution = 'composer'
      AND pt.title IS NOT NULL
UNION
-- The following is for works without a primary title: we attempt to use form, key and mode
SELECT w.work_id,
       va.artist_id AS composer_id,
       IF(form IS NULL, _utf8'title missing',
        CONCAT_WS(' ',
         CONVERT(CONCAT_WS(' ',form,CONCAT('in ',central_key,' ',central_mode)) USING utf8),
         CONCAT(_utf8'(',st.title,_utf8')'))
       ) AS title,
       va.shortname AS composer
FROM view_artists AS va
     NATURAL JOIN create_work AS cw 
     NATURAL JOIN works AS w
     LEFT OUTER JOIN work_titles AS pt ON pt.work_id=w.work_id AND pt.display = 'primary'
     LEFT OUTER JOIN work_titles AS st ON st.work_id=w.work_id AND st.display='secondary'
WHERE cw.contribution = 'composer'
      AND pt.title IS NULL
;
    

-- SELECT * from view_works;