"""Mysql queries to initialize test database."""

# flake8: noqa
CREATE_VENDOR_CONTRACT_TABLE = """CREATE TABLE IF NOT EXISTS `vendor_contract` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) DEFAULT NULL,
  `cont_start` date DEFAULT NULL,
  `cont_end` date DEFAULT NULL,
  `cont_version` varchar(50) DEFAULT NULL,
  `exclusive` enum('yes_digital','yes_physical','yes_both','no_both','y','n') DEFAULT 'no_both',
  `orchrep_name` varchar(55) DEFAULT NULL,
  `carve_out` varchar(255) DEFAULT NULL,
  `qualification_level` varchar(44) DEFAULT NULL,
  `territory_carve_out` text,
  `dms_carve_out` mediumtext,
  `dms_master_carve_out` text,
  `encoding_fees_cap_back_ctlg` float DEFAULT NULL,
  `encoding_fees_cap_new_release` float DEFAULT NULL,
  `digital_split` float DEFAULT NULL,
  `physical_split` float DEFAULT NULL,
  `contract_type` enum('vendor_term','per_release_term') DEFAULT 'vendor_term',
  `release_term` int(11) DEFAULT NULL,
  `advance_payment` float DEFAULT NULL,
  `advance_recoupable_percentage` float DEFAULT NULL,
  `dig_distribution_type` enum('digital_mobile','digital_only','mobile_only') DEFAULT 'digital_mobile',
  `possible_track_restrictions` enum('Y','N') DEFAULT 'N',
  `oms_type` enum('none','both','orchard','label') DEFAULT 'none',
  `oms_fee_percentage` float DEFAULT NULL,
  `negotiated_changes` enum('Y','N') DEFAULT NULL,
  `negotiated_change_comments` text,
  `contract_complete` enum('Y','N') DEFAULT 'N',
  `signature_date` date DEFAULT NULL,
  `marketing_restrictions` enum('Y','N') DEFAULT 'N',
  `orchard_assignment_right_restriction` enum('Y','N') DEFAULT 'N',
  `currency_id` smallint(5) unsigned DEFAULT '1',
  `third_party_responsibility` enum('standard','other') DEFAULT 'standard',
  `third_party_responsibility_detail` text,
  `possible_track_restriction_detail` text,
  `is_amendment` enum('Y','N') DEFAULT 'N',
  `extend_until_recouped` enum('Y','N') DEFAULT 'N',
  `sync_admin_territory` text,
  `sync_admin_commission` text,
  `sync_admin_type_of_deal` enum('master_admin','master_or_publishing_admin','publishing_admin_only') DEFAULT NULL,
  `royalty_collection_territory` text,
  `royalty_collection_commission` float DEFAULT NULL,
  `publishing_admin_commission` float DEFAULT NULL,
  `publishing_admin_territory` text,
  `publishing_admin_limit_grant_of_rights` varchar(50) DEFAULT NULL,
  `publishing_admin_misc_provisions` varchar(50) DEFAULT NULL,
  `parent_vendor_contract_id` int(11) DEFAULT NULL,
  `vendor_type` enum('vendor','oms_client') DEFAULT 'vendor',
  `vendor_proposed_term_id` int(11) DEFAULT NULL,
  `sync_admin_response_time` int(11) DEFAULT NULL,
  `ringtone_publishing_type` enum('both','label','orchard','none') DEFAULT 'none',
  `physical_track_publishing_type` enum('none','both','orchard','label') DEFAULT 'none',
  `unlimited_roll` enum('Y','N') DEFAULT 'N',
  `rollover_length_in_months` int(11) DEFAULT NULL,
  `can_terminate` enum('Y','N') DEFAULT 'N',
  `youtube_composition_clause` enum('N','Y') DEFAULT 'N',
  `sx_royalty_collection_commission` float DEFAULT '0',
  `topspin_rate` float DEFAULT '0',
  `topspin_rate_territory` text,
  `notice_required_in_days` int(11) DEFAULT NULL,
  `term_continues_until_recouped` enum('Y','N') DEFAULT 'N',
  `brand_split` float DEFAULT NULL,
  `other_rights_option` enum('any_and_all','other','none') DEFAULT 'any_and_all',
  `other_rights_text` varchar(50) DEFAULT NULL,
  `special_product_split` float DEFAULT NULL,
  `special_product_carve_out` text,
  `payment_interval` enum('month','quarter') DEFAULT 'quarter',
  `pay_after` enum('30','45','60','90') DEFAULT '45',
  `show_credit_card` enum('N','Y') DEFAULT 'N',
  `opt_out` enum('Y','N') DEFAULT 'N',
  `apply_fx_spread` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'N',
  `reserve_rate` float(10,2) unsigned NOT NULL DEFAULT '0.00',
  `number_of_months_before_payout` tinyint(1) unsigned DEFAULT '0',
  `number_of_installments` tinyint(1) unsigned DEFAULT '0',
  `contract_terms` text,
  `service_type_id` int(11) unsigned DEFAULT NULL,
  `is_automatic_rollover` enum('Y','N') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vendor_id` (`vendor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Holds all contracts of labels';
"""

CREATE_CURRENCIES_TABLE = """CREATE TABLE IF NOT EXISTS `currencies` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `ISO_4217_code` varchar(12) DEFAULT NULL,
  `symbol_html_entity_code` varchar(45) DEFAULT NULL COMMENT 'Html entity of symbol',
  `currency_name` varchar(45) DEFAULT NULL COMMENT 'Name of currency.',
  `decimal_mark` char(1) DEFAULT NULL COMMENT 'Decimal mark',
  `thousands_separator` char(1) DEFAULT NULL COMMENT 'Thousand separator',
  `symbol_infront` char(1) DEFAULT NULL COMMENT 'Place symbol infront of dollar amount\n',
  `subunit_to_unit` smallint(6) DEFAULT NULL COMMENT 'Subunit of unit',
  `subunit_name` varchar(45) DEFAULT NULL COMMENT 'Name of subunit',
  `supported_payout_currency` char(1) DEFAULT 'N' COMMENT 'Orchard supported payout currency\n',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ISO_4217_code_UNIQUE` (`ISO_4217_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""

CREATE_COUNTRY_TABLE = """CREATE TABLE IF NOT EXISTS `country` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key.',
  `name` varchar(45) NOT NULL COMMENT 'Name of the country.',
  `country_code` char(2) DEFAULT NULL COMMENT 'ISO 3166-1 alpha-2 country code',
  `abbrivation` char(2) NOT NULL,
  `continent` enum('Africa','Antarctica','Asia','Europe','North America','Oceania','South America') NOT NULL,
  `latitude` decimal(10,6) DEFAULT NULL COMMENT 'Latitude of the country',
  `longitude` decimal(10,6) DEFAULT NULL COMMENT 'Longitude of the country',
  `iso3166a3` char(3) DEFAULT NULL,
  `continent_id` tinyint(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `country_code` (`country_code`),
  KEY `abbrivation` (`abbrivation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 ROW_FORMAT=DYNAMIC;
"""

CREATE_VENDOR_CONTRACT_DISTRIBUTION_TYPE_TABLE = """CREATE TABLE IF NOT EXISTS `vendor_contract_distribution_type` (
  `id` int(12) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `vendor_contract_id` int(10) DEFAULT NULL COMMENT 'Foreign key referencing vendor contract table',
  `distribution_type_id` int(10) DEFAULT NULL COMMENT 'Reference to distribution_type table.',
  `new_store_default` enum('Y','N') DEFAULT 'N' COMMENT 'Flag indicating default distribution type value when new store is added to the system',
  PRIMARY KEY (`id`),
  KEY `vendor_contract_id` (`vendor_contract_id`),
  KEY `distribution_type_id` (`distribution_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Holds distribution type allowed in the corresponding label c'
"""


CREATE_DISTRIBUTION_TYPE_TABLE = """CREATE TABLE IF NOT EXISTS `distribution_type` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` varchar(20) NOT NULL COMMENT 'distribution_type of a vendor, e.g.  full track, ringtone, etc',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Distribution type of labels, e.g. ringtone, full track, etc'
"""

CREATE_CUSTOMER_MASTER_MASTER = """CREATE TABLE IF NOT EXISTS `customer_master_master` (
  `customer_master_master_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key.',
  `customer_name` varchar(50) NOT NULL,
  `sony_dms_code` varchar(3) DEFAULT NULL,
  `ci_dms_code` varchar(5) DEFAULT NULL,
  `delivery_option` enum('full','cherry_pick','filtered_catalog') NOT NULL DEFAULT 'full',
  `show_delivery_info` enum('Y','N') NOT NULL DEFAULT 'Y',
  `product_type_id` text,
  `delivery_cap` int(10) unsigned DEFAULT NULL,
  `weekly_limit` smallint(5) unsigned DEFAULT NULL,
  `eo_limit` smallint(5) unsigned DEFAULT NULL,
  `encoding_order_status` enum('close','open') NOT NULL DEFAULT 'open',
  `mv_delivery_option` enum('full','cherry_pick') DEFAULT 'cherry_pick',
  `ddex_party_id` varchar(20) DEFAULT NULL,
  `optin_date` date DEFAULT NULL,
  `required_genre_code` enum('Y','N') DEFAULT NULL,
  `required_subgenre_code` enum('Y','N') DEFAULT NULL,
  `required_subgenre` enum('Y','N') DEFAULT NULL,
  `instant_grat` enum('Y') DEFAULT NULL,
  `hd_only` enum('Y','N') DEFAULT 'N',
  `status` enum('active','inactive','terminated') DEFAULT 'active',
  `label_store` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`customer_master_master_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1693 DEFAULT CHARSET=utf8 COMMENT='Holds master store information'
"""

CREATE_VENDOR_DMS_MASTER_RESTRICTION = """CREATE TABLE IF NOT EXISTS `vendor_dms_master_restriction` (
  `restriction_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `customer_master_master_id` smallint(5) unsigned NOT NULL,
  `distribution_type_id` tinyint(3) unsigned NOT NULL,
  `vendor_contract_id` int(10) unsigned NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`restriction_id`),
  UNIQUE KEY `vendor_restriction` (`vendor_contract_id`,`customer_master_master_id`,`distribution_type_id`),
  KEY `distribution_type_id` (`distribution_type_id`),
  KEY `customer_master_master_id` (`customer_master_master_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23202369 DEFAULT CHARSET=utf8 COMMENT='Holds master store restrictions'
"""