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

CREATE_AUDIBLEMAGIC_MATCHES_TABLE = f"""
    CREATE TABLE `audiblemagic_matches` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `song_id` mediumint(8) unsigned DEFAULT NULL,
      `amItemID` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `vendor` varchar(255) 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,
      `release_date` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `response` json DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_audiblemagic_matches_song_id` (`song_id`),
      CONSTRAINT `fk_audiblemagic_matches_song_id` FOREIGN KEY (`song_id`) 
      REFERENCES `songs` (`song_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""

INSERT_AUDIBLEMAGIC_MATCH = f"""
    INSERT INTO audiblemagic_matches (
        song_id, amItemID, vendor, label, title, isrc, upc, artist, release_date, response)
    VALUES (
        %(song_id)s, 
        %(amItemID)s, 
        %(vendor)s,
        %(label)s,
        %(title)s,
        %(isrc)s,
        %(upc)s,
        %(artist)s,
        %(release_date)s,
        %(response)s
    )
"""

UPDATE_SONG_AUDIBLEMAGIC_IDENTIFIED = f"""
    UPDATE songs SET audiblemagic = %s WHERE song_id = %s;
"""

"""Common queries."""

SELECT_SONG_ID_BY_NAME = f"""
    SELECT song_id
    FROM songs
    WHERE song_name = %s;
"""

INSERT_SONG = f"""
    INSERT INTO songs (song_name, extension, path)
    VALUES (%s, %s, %s);
"""


SELECT_SONGS = f"""
    SELECT song_name, extension, acrcloud, audiblemagic, path FROM songs;
"""

CREATE_SONGS_TABLE = f"""
CREATE TABLE `songs` (
  `song_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `song_name` varchar(250) NOT NULL,
  `fingerprinted` tinyint(4) DEFAULT '0',
  `file_sha1` binary(20) DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `total_hashes` int(11) NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `extension` varchar(16) NOT NULL DEFAULT '',
  `acrcloud` tinyint(4) DEFAULT NULL,
  `audiblemagic` tinyint(4) DEFAULT NULL,
  `upc` varchar(50) DEFAULT NULL,
  `tuid` int(12) DEFAULT NULL,
  `isrc` varchar(16) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`song_id`),
  UNIQUE KEY `song_name` (`song_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
"""