"""Different database-related test helpers."""

from sqlalchemy import text

from deliveryhistory.connectors import mysql

SEED_PRODUCT_TYPE = """INSERT INTO `product_type`
(`id`, `product_type`)
VALUES (1, 'Music');
"""

SEED_PROJECT = """INSERT INTO `project`
(`project_id`, `vendor_id`, `subaccount_id`, `project_name`, `created_date_utc`, `updated_date_utc`)
VALUES (1, 1, 0, 'test', NOW(), NOW());
"""

SEED_RELEASES_TABLE = """
INSERT INTO `releases` (release_id, upc, display_upc, release_status, project_id) VALUES
(111, '1110', 1110, 'in_content', 1),
(222, '2220', 2220, 'in_content', 1),
(333, '3330', 3330, 'in_content', 1);
"""

SEED_CUSTOMER_MASTER_MASTER_TABLE = """
INSERT INTO `customer_master_master` (customer_master_master_id, customer_name) VALUES
(1, 'ITunes/Apple'),
(2, 'Napster - Legacy'),
(3, 'MediaNet');
"""

SEED_DELIVERY_HISTORY_TABLE = """
INSERT INTO `delivery_history` (delivery_id, upc, customer_master_master_id, date_delivered) VALUES
(1, 1110, 1, '2019-01-01'),
(2, 1110, 2, '2019-01-01'),
(3, 2220, 1, '2019-01-02'),
(4, 2220, 2, '2019-01-02'),
(5, 2220, 3, '2019-01-02'),
(6, 3330, 1, '2019-01-03');
"""


def seed_delivery_tables():
    """Seed test tables."""
    with mysql.ar_session_scope() as session:
        session.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))
        session.execute(text('TRUNCATE TABLE `delivery_history`;'))
        session.execute(text('TRUNCATE TABLE `customer_master_master`;'))
        session.execute(text('TRUNCATE TABLE `releases`;'))
        session.execute(text('TRUNCATE TABLE `product_type`;'))
        session.execute(text('TRUNCATE TABLE `project`;'))
        session.execute(text('SET FOREIGN_KEY_CHECKS = 1;'))
        session.execute(text(SEED_PRODUCT_TYPE))
        session.execute(text(SEED_PROJECT))
        session.execute(text(SEED_RELEASES_TABLE))
        session.execute(text(SEED_CUSTOMER_MASTER_MASTER_TABLE))
        session.execute(text(SEED_DELIVERY_HISTORY_TABLE))


def clear_ows_tables():
    """Clear ows-delivery-history tables."""
    with mysql.session_scope() as session:
        session.execute(text('SET FOREIGN_KEY_CHECKS = 0;'))
        session.execute(text('TRUNCATE TABLE `masters_delivery_history`;'))
        session.execute(text('SET FOREIGN_KEY_CHECKS = 1;'))