"""SQL queries for inserting matches."""

CREATE_ACRCLOUD_MATCHES_TABLE = f"""
    CREATE TABLE `acrcloud_matches` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `song_id` mediumint(8) unsigned DEFAULT NULL,
      `acrid` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `label` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `artist` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `isrc` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `upc` bigint(20) DEFAULT NULL,
      `album` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
      `release_date` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `response` json DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_acrcloud_matches_song_id` (`song_id`),
      KEY `isrc` (`isrc`),
      CONSTRAINT `fk_acrcloud_matches_song_id` FOREIGN KEY (`song_id`) REFERENCES `songs` (`song_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""


CREATE_ACRCLOUD_MATCHES_RIGHTS_TABLE = f"""
    CREATE TABLE `acrcloud_matches_rights` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `song_id` mediumint(8) unsigned DEFAULT NULL,
      `acrid` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `label` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `artist` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `distributor` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `isrc` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `upc` bigint(20) DEFAULT NULL,
      `album` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '',
      `release_date` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `response` json DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_acrcloud_matches_rights_song_id` (`song_id`),
      KEY `isrc` (`isrc`),
      CONSTRAINT `fk_acrcloud_matches_rights_song_id` FOREIGN KEY (`song_id`) REFERENCES `songs` (`song_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""


INSERT_ACRCCLOUD_MATCH = f"""
    INSERT INTO acrcloud_matches (song_id, acrid, label, title, isrc, upc, album, release_date, artist, response)
    VALUES (
        %(song_id)s, 
        %(acrid)s, 
        %(label)s,
        %(title)s,
        %(isrc)s,
        %(upc)s,
        %(album)s,
        %(release_date)s,
        %(artist)s,
        %(response)s
    )
"""

INSERT_ACRCCLOUD_RIGHTS_MATCH = f"""
    INSERT INTO acrcloud_matches_rights (song_id, acrid, label, title, isrc, upc, album, release_date, artist, distributor, response)
    VALUES (
        %(song_id)s, 
        %(acrid)s, 
        %(label)s,
        %(title)s,
        %(isrc)s,
        %(upc)s,
        %(album)s,
        %(release_date)s,
        %(artist)s,
        %(distributor)s,
        %(response)s
    )
"""

UPDATE_SONG_ACRCLOUD_RECOGNIZED = f"""
    UPDATE songs SET acrcloud = %s WHERE song_id = %s;
"""

UPDATE_SONG_ACRCLOUD_RIGHTS_RECOGNIZED = f"""
    UPDATE songs SET acrcloud_rights = %s WHERE song_id = %s;
"""

GET_RIGHTS_CLAIM = f"""
    SELECT 
        acrcloud_matches_rights.song_id,
        acrcloud_matches_rights.upc as acr_upc, songs.upc as orch_upc,
        acrcloud_matches_rights.label as acr_label, songs.label as orch_label,
        acrcloud_matches_rights.isrc as acr_isrc, songs.isrc as orch_isrc,
        acrcloud_matches_rights.artist as acr_artist, songs.artist as orch_artist,
        acrcloud_matches_rights.album as acr_album, songs.release_name as orch_album,
        acrcloud_matches_rights.title as acr_title, songs.track_name as orch_title,
        acrcloud_matches_rights.response
    FROM acrcloud_matches_rights
    JOIN songs on songs.song_id = acrcloud_matches_rights.song_id
    WHERE response LIKE '%rights_claim%'
"""

GET_ACRCLOUD_MATCHES = f"""
    SELECT 
        acrcloud_matches_rights.song_id,
        acrcloud_matches_rights.upc as acr_upc, songs.upc as orch_upc,
        acrcloud_matches_rights.label as acr_label, songs.label as orch_label,
        acrcloud_matches_rights.isrc as acr_isrc, songs.isrc as orch_isrc,
        acrcloud_matches_rights.artist as acr_artist, songs.artist as orch_artist,
        acrcloud_matches_rights.album as acr_album, songs.release_name as orch_album,
        acrcloud_matches_rights.title as acr_title, songs.track_name as orch_title,
        acrcloud_matches_rights.response
    FROM acrcloud_matches_rights
    JOIN songs on songs.song_id = acrcloud_matches_rights.song_id
    WHERE songs.date_created >= "2020-12-05"
"""