# PostgreSQL Queries
get_yt_asset_report_query = """SELECT * FROM {} LIMIT 1;"""

# MySQL Queries
check_table_query = """SHOW TABLES LIKE %s;"""

create_map_table_query = """CREATE TABLE `{}` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`filename` VARCHAR(255) DEFAULT NULL,
`asset_id` VARCHAR(180) DEFAULT NULL,
`custom_id` VARCHAR(1500) DEFAULT NULL,
`display_upc` VARCHAR(50) DEFAULT NULL,
`upc` VARCHAR(50) DEFAULT NULL,
`isrc` VARCHAR(50) DEFAULT NULL,
`derived_tuid` VARCHAR(25) DEFAULT NULL,
`reason` VARCHAR(255) DEFAULT NULL,
`derived_ownership` varchar(2400) DEFAULT NULL,
`last_delivery_date` varchar(25) DEFAULT NULL,
`ioda_compare_cat` VARCHAR(1) DEFAULT NULL,
`is_locked` TINYINT(1) DEFAULT 0,
PRIMARY KEY (`id`),
KEY(`upc`),
KEY(`isrc`),
KEY(`asset_id`),
KEY(`display_upc`)
) DEFAULT CHARSET=utf8mb4;"""

create_log_table_query = """
CREATE TABLE `{}`(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `filename` VARCHAR(255),
  `reason` VARCHAR(255),
  `asset_id` VARCHAR(50),
  `recent_daily_average` VARCHAR(20),
  `asset_type` VARCHAR(50),
  `custom_id` VARCHAR(50),
  `isrc` VARCHAR(20),
  `grid` VARCHAR(20),
  `upc` VARCHAR(20),
  `artist` VARCHAR(255),
  `song` VARCHAR(255),
  `album` VARCHAR(255),
  `label` VARCHAR(255),
  `constituent_asset_ids` VARCHAR(255),
  `active_reference_ids` VARCHAR(255),
  `inactive_reference_ids` VARCHAR(511),
  `is_merged_asset` VARCHAR(20),
  `embedded_asset_id` VARCHAR(255),
  PRIMARY KEY (`id`)
);

"""

source_assets_query = """SELECT * FROM `{}` LIMIT %s OFFSET %s"""

get_track_by_upc_and_isrc_query = """SELECT t.id, t.isrc, t.upc, r.display_upc
                                      FROM `track` t INNER JOIN `releases` r
                                      USING (release_id)
                                      WHERE (t.`upc` = %(upc)s OR r.`display_upc` = %(upc)s)
                                        AND t.`isrc` = %(isrc)s"""

update_ownership_by_row_id_query = """UPDATE `{}`
                        SET derived_ownership = %s
                        WHERE id = %s"""

insert_tuid_query = """INSERT INTO `{}`
                        (`custom_id`,
                        `filename`,
                        `asset_id`,
                        `display_upc`,
                        `upc`,
                        `isrc`,
                        `derived_tuid`,
                        `reason`,
                        `derived_ownership`,
                        `last_delivery_date`)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
"""

update_isrc_and_upc_query = """UPDATE `{}`
                                SET isrc = %s,
                                    display_upc = %s
                                WHERE id = %s"""

get_track_by_itm_query = """SELECT itm.`ioda_track_id`, t.id, t.upc, t.isrc
                              FROM ioda_track_mapping itm INNER JOIN track t
                                ON itm.`orchard_track_id` = t.`id`
                              WHERE ioda_track_id = %s
"""

get_track_by_isrc_query = """SELECT t.id AS tuid,
                                    r.upc,
                                    r.display_upc,
                                    t.isrc
                                FROM track t
                                INNER JOIN releases r
                                  USING (release_id)
                                WHERE t.isrc = %s
                                """

get_track_by_tuid_query = """SELECT t.id AS tuid,
                                    r.upc,
                                    r.display_upc,
                                    t.isrc
                                FROM track t
                                INNER JOIN releases r
                                  USING (release_id)
                                WHERE t.id = %s
                                """

truncate_target_table = """TRUNCATE TABLE `{}`"""

get_delivery_history = """SELECT date_delivered
                            FROM delivery_history dh
                            WHERE dh.`customer_master_master_id` = 453
                              AND upc = %s
                            ORDER BY dh.date_delivered DESC
                            LIMIT 1"""

get_row_from_target_table_by_id = """SELECT * FROM `{}` WHERE id = {};"""

get_row_from_target_table_by_field = """SELECT * FROM `{}` WHERE `{}` = '{}';"""

get_error_row_from_target_table = """SELECT * FROM `{}` WHERE id = {};"""

get_max_id_from_target_table = """SELECT MAX(id) as max_id FROM `{}`;"""

get_column_names = """SELECT COLUMN_NAME
                        FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE TABLE_SCHEMA = '{}'
                        AND TABLE_NAME = '{}';
"""

get_dupe_isrcs_by_filename = """SELECT `isrc`, COUNT(`isrc`) AS `c`
                      FROM `{}`
                      WHERE `filename` = '{}'
                      GROUP BY `isrc`
                      HAVING `c` > 1;
"""

get_dupe_isrcs = """SELECT `isrc`, COUNT(`isrc`) AS `c`
                      FROM `{}`
                      GROUP BY `isrc`
                      HAVING `c` > 1;
"""

get_dupe_isrcs_by_exclude_filename = """
          SELECT isrc,
          COUNT(isrc) as `c`
          FROM `{}`
          WHERE `filename` NOT LIKE '{}'
          GROUP BY `isrc` HAVING c > 1;"""

delete_rows_by_isrc_and_filename = """
  DELETE FROM `{}` WHERE `isrc` IN ('{}') AND `filename`='{}';
"""

get_unlocked_list_with_tuid_from_target_table_by_isrcs = """
    SELECT * FROM `{}` WHERE isrc in ('{}')
      AND is_locked = 0
      AND derived_tuid IS NOT NULL
      AND trim(derived_tuid) <> '';
"""

get_unlocked_list_with_tuid_from_target_table_by_isrcs_and_filename = """
    SELECT * FROM `{}` WHERE isrc in ('{}')
      AND `filename` = '{}'
      AND is_locked = 0
      AND derived_tuid IS NOT NULL
      AND trim(derived_tuid) <> '';
"""


get_unlocked_list_from_target_table_by_isrcs_and_filename = """
    SELECT * FROM `{}` WHERE isrc in ('{}')
      AND `filename` = '{}'
      AND is_locked = 0
      AND derived_tuid IS NOT NULL
      AND trim(derived_tuid) <> ''
"""

get_list_from_target_table_by_isrcs_and_filename = """
    SELECT * FROM `{}` WHERE isrc in ('{}')
      AND `filename` = '{}';
"""

get_recent_fact_sales_rows_by_isrc = """
SELECT dt.isrc,
	c.country_code,
	fs.releaseid as upc,
	dt.track_unique_id,
	max(fs.accountingperiodid) as period
FROM dev.production.fact_sales fs
	INNER JOIN dev.production.dim_track dt
		ON dt.trackid = fs.trackid
	INNER JOIN dim_country c
		ON c.countryid = fs.countryid
WHERE fs.storeid = 453
	AND fs.activityperiodid >= 209
	AND fs.activityperiodid <= 212
	AND dt.isrc in (
	'{}'
	)
GROUP BY
	dt.isrc,
	c.country_code,
	fs.releaseid,
	dt.track_unique_id
ORDER BY
    dt.isrc,
	c.country_code,
	period desc;
"""

get_unique_isrcs_from_target_table= """
SELECT DISTINCT isrc FROM `{}`;
"""

get_unique_unlocked_isrcs_with_tuids_from_target_table= """
SELECT DISTINCT isrc FROM `{}`
  WHERE `is_locked` = 0
    AND derived_tuid IS NOT NULL
    AND trim(derived_tuid) <> '';
"""

get_unique_unlocked_isrcs_from_target_table= """
SELECT DISTINCT isrc FROM `{}`
  WHERE `is_locked` = 0
    AND derived_tuid IS NOT NULL
    AND trim(derived_tuid) <> '';
"""

update_target_locks_by_isrc="""
  UPDATE `{}` SET `is_locked`=1 WHERE `isrc` IN ('{}');
"""

update_target_locks_by_filename="""
  UPDATE `{}` SET `is_locked`=1 WHERE `filename` ='{}';
"""

update_target_lock_all="""
  UPDATE `{}` SET `is_locked`=1;
"""

get_rows_by_isrc_and_territory= """
  SELECT * FROM `{}` WHERE `isrc`='{}' AND `derived_ownership` LIKE '%{}%';
"""

get_row_by_isrc_filename_and_territory="""
  SELECT * FROM `{}` WHERE `isrc`='{}'
  AND `filename` = '{}'
  AND `derived_ownership` LIKE '%{}%';
"""

get_rows_by_isrc_tuid_and_filename= """
  SELECT * FROM `{}` WHERE `isrc`='{}'
  AND `filename` = '{}'
  AND derived_tuid = '{}';
"""

get_max_of_table = """
  SELECT MAX(id) as max from `{}`
"""

write_asset_to_log = """
  INSERT INTO %s ( %s ) VALUES ( %s )
"""

get_by_isrc = """SELECT * FROM `{}` WHERE `isrc` = '{}'"""

update_ioda_category_by_isrc="""
  UPDATE `{}` SET `ioda_compare_cat`='{}' WHERE `isrc` = '{}';
"""

update_table_add_ioda_col="""
ALTER TABLE `{}`
  ADD COLUMN `ioda_compare_cat` VARCHAR(1) NULL AFTER `last_delivery_date`;
"""

check_table_for_ioda_col="""
SELECT *
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = '{}'
AND TABLE_NAME = '{}'
AND COLUMN_NAME = 'ioda_compare_cat';
"""