"""Find labels affected by contract issue.

Outputs tuples like:
 (period_id, label_id, possible_contract, correct_contract)
"""

from datetime import date
from os import environ

import MySQLdb

from dotenv import find_dotenv
from dotenv import load_dotenv
load_dotenv(find_dotenv())

CONTRACT_SQL = """
SELECT b.period_id, x.label_id, x.contract_id, b.vendor_contract_id
FROM (
SELECT
v.`vendor_id` as label_id,
vcc.`id` as contract_id
FROM `vendor` v
JOIN `vendor_contract` vcc ON v.`vendor_id` = vcc.`vendor_id`
LEFT JOIN `vendor_contract` vcc2 ON vcc.`vendor_id` = vcc2.`vendor_id`
AND vcc2.`cont_start` <= '{period}'
AND vcc.`cont_start` < vcc2.`cont_start`
AND ((
        vcc2.`contract_type` = 'per_release_term'
        AND (
            vcc2.`cont_start` = '0000-00-00'
            OR vcc2.`release_term` <= 0
            OR (vcc2.`cont_start`
                + interval vcc2.`release_term` month) > '{period}'
        )) OR (
        COALESCE(vcc2.`contract_type`, 'null') <> 'per_release_term'
    ))
WHERE vcc2.`id` IS NULL
AND vcc.`cont_start` <= '{period}'
AND v.`status` IN ('signed', 'deletion')
AND ((
        vcc.`contract_type` = 'per_release_term'
        AND (
            vcc.`cont_start` = '0000-00-00'
            OR vcc.`release_term` <= 0
            OR (vcc.`cont_start`
                + interval vcc.`release_term` month) > '{period}'
        ))
    OR (
        COALESCE(vcc.`contract_type`, 'null') <> 'per_release_term'
    ))) x
INNER JOIN `booked_vendor_contract_snapshot` b ON x.`label_id` = b.`vendor_id`
    AND b.`period_id` = {period_id}
HAVING contract_id <> vendor_contract_id

"""


def main():
    """Iterate over past periods, searching for possible mismatches."""
    for dateset in iterate_months():
        period = '{}-{}-28'.format(dateset['year'], dateset['month'])
        sql = CONTRACT_SQL.format(
            period=period, period_id=dateset['period_id'])
        connection = get_connection()
        cursor = connection.cursor()
        cursor.execute(sql)
        for row in cursor.fetchall():
            print('{}, {}, {}, {}, {}'.format(
                period, row[0], row[1], row[2], row[3]))


def iterate_months():
    """Yield accounting periods as dict representations."""
    past_date = {
        'year': 1999,
        'month': 1,
        'period_id': 1
    }
    today = date.today()
    while (past_date['year'] < today.year or past_date['month'] < today.month):

        yield past_date

        past_date['period_id'] += 1
        past_date['month'] += 1
        if (past_date['month'] > 12):
            past_date['month'] = 1
            past_date['year'] += 1


def get_connection():
    """Return Connection."""
    db_config = {
        'user':     environ.get('DB_USER'),
        'password': environ.get('DB_PASS'),
        'host':     environ.get('DB_HOST'),
        'database': environ.get('DB_NAME'),
    }
    return MySQLdb.connect(**db_config)


if __name__ == '__main__':
    main()