BY_SOCIETY_FIELDS = ['CONTRIBUTOR_ID', 'CONTRIBUTION_ID', 'BUTOR_UUID', 'BUTION_UUID', 'RECORDING_ID',
                     'SOCIETY_IP', 'SOCIETY_NAME', 'WHOLEDESCRIPTION', 'CONVERTED_DESCRIPTION', 'NOTES', 'CREATED_DATE']

UNION = "\nUNION\n"


def featured():
    return "INNER JOIN facts.prod.nr_contributor_has_featured_contribution_nr_contribution fc on rc.id = fc.nr_contribution_external_id\n"


def non_featured():
    return "INNER JOIN facts.prod.nr_contributor_has_non_featured_contribution_nr_contribution fc on rc.id = fc.nr_contribution_external_id\n"


def other_featured():
    return "INNER JOIN facts.prod.nr_contributor_has_other_featured_contribution_nr_contribution fc on rc.id = fc.nr_contribution_external_id\n"


def all_types_of_contributor_for_society(thecursor, society_id, offset, limit):
    f = contributors_for_society_by_type(society_id, featured)
    nf = contributors_for_society_by_type(society_id, non_featured)
    of = contributors_for_society_by_type(society_id, other_featured)
    union = f + UNION + nf + UNION + of + f" ORDER BY neo_contributor_uuid LIMIT  {limit} OFFSET {offset};"
    thecursor.execute(union)
    return thecursor.fetchall()

def non_and_other_featured_contributors_for_society(society_id):
    nf = contributors_for_society_by_type(society_id, non_featured)
    of = contributors_for_society_by_type(society_id, other_featured)
    union = nf + UNION + of + ";"
    return union

def contributors_for_society_by_type(society_id, type):
    select = 'SELECT DISTINCT nrc.id as neo_contributor_uuid\n'
    from_clause_one = "from awal_sax.sax.interested_parties ip inner join awal_sax.sax.agreements a on a.acquirer_id = ip.id\ninner join awal_sax.sax.record_nr_registrations rnr on rnr.agreement_id = a.id\n"
    from_clause_inner_one = "INNER JOIN awal_sax.sax.record_nr_reg_history rnrh ON rnr.last_history_id = rnrh.id\n"
    from_clause_inner_two = "INNER JOIN awal_sax.sax.record_nr_reg_statuses rnrs ON rnrh.registration_status_id = rnrs.id\n"
    from_clause_inner_three = "INNER JOIN awal_sax.sax.recording_contributors rc ON rnr.recording_contributor_id = rc.id\n"
    from_clause_inner_four = "INNER JOIN facts.prod.nr_contributor nrc on nrc.external_id = rc.party_id\n"
    from_clause_inner_five = type()
    where_clause = f"WHERE ip.id = '{society_id}'"
    return select + from_clause_one + from_clause_inner_one + from_clause_inner_two + from_clause_inner_three + from_clause_inner_four + from_clause_inner_five + where_clause


def by_society_and_contributor_all(thecursor, society_id, contributor_id, queue=False, offset=0, limit=500):
    f = by_society_and_contributor_id_for_contribution_type(
        society_id, contributor_id, featured, queue)
    nf = by_society_and_contributor_id_for_contribution_type(
        society_id, contributor_id, non_featured, queue)
    of = by_society_and_contributor_id_for_contribution_type(
        society_id, contributor_id, other_featured, queue)
    union = f + UNION + nf + UNION + of + f" ORDER BY contributor_uuid, contribution_uuid LIMIT {limit} OFFSET {offset};"
    thecursor.execute(union)
    return thecursor.fetchall()

def by_society_and_contributor_non_other(society_id, contributor_id, queue=False):
    nf = by_society_and_contributor_id_for_contribution_type(
        society_id, contributor_id, non_featured, queue)
    of = by_society_and_contributor_id_for_contribution_type(
        society_id, contributor_id, other_featured, queue)
    union = nf + UNION + of + ";"
    return union


def by_society_and_contributor_id_for_contribution_type(society_id, contributor_id, type, queue=False):
    select_clause = "select distinct nrc.id as contributor_uuid,\nfc.nr_contribution_id as contribution_uuid,\nip.id as society_ip,\nrnrs.id as status_id,\nrnrh.created_date\n"
    from_clause_one = "from awal_sax.sax.interested_parties ip inner join awal_sax.sax.agreements a on a.acquirer_id = ip.id\ninner join awal_sax.sax.record_nr_registrations rnr on rnr.agreement_id = a.id\n"
    from_clause_inner_one = "INNER JOIN awal_sax.sax.record_nr_reg_history rnrh ON rnr.last_history_id = rnrh.id\n"
    from_clause_inner_two = "INNER JOIN awal_sax.sax.record_nr_reg_statuses rnrs ON rnrh.registration_status_id = rnrs.id\n"
    from_clause_inner_three = "INNER JOIN awal_sax.sax.recording_contributors rc ON rnr.recording_contributor_id = rc.id\n"
    from_clause_inner_four = "INNER JOIN facts.prod.nr_contributor nrc on nrc.external_id = rc.party_id\n"
    from_clause_inner_five = type()
    queueded_clause = "in" if queue else "not in"
    where_clause = f"WHERE status_id {queueded_clause} ('RNRRS161', 'RNRRS96', 'RNRRS86', 'RNRRS88', 'RNRRS97', 'RNRR141', 'RNRRS91', 'RNRRS90') and society_ip = '{society_id}' and contributor_uuid = '{contributor_id}'"
    return select_clause + from_clause_one + from_clause_inner_one + from_clause_inner_two + from_clause_inner_three + from_clause_inner_four + from_clause_inner_five + where_clause


status = {
    'RNRRS99': 'error',
    'RNRRS98': 'error',
    'RNRRS95': 'error',
    "RNRRS81": "excluded",
    'RNRRS92': 'excluded',
    'RNRRS94': 'excluded',
    'RNRRS93': 'excluded',
    'RNRRS84': 'excluded',
    'RNRRS83': 'excluded',
    'RNRRS96': 'queued',
    'RNRRS86': 'queued',
    'RNRRS88': 'queued',
    'RNRRS97': 'queued',
    'RNRR141': 'queued',
    'RNRRS90': 'queued',
    'RNRRS91': 'queued',
    'RNRRS161': 'queued',
    'RNRRS82': 'success',
    'RNRR121': 'success',
    'RNRRS85': 'success',
    'RNRRS87': 'success',
    'RNRRS89': 'success',
}

def get_contribution_ids(limit, offset):
    query  = f'SELECT id FROM facts.prod.nr_contribution WHERE is_deleted = FALSE ORDER BY id LIMIT {limit} OFFSET {offset}'
    return query


def get_unchanged_contribution(cursor, job_last_updated, contribution_ids):
    ids_string = "','".join(contribution_ids)

    query = f"""select nc.id 
        from FACTS.PROD.NR_CONTRIBUTION_CONTRIBUTED_TO_NR_SOUND_RECORDING rel
        inner join FACTS.PROD.NR_CONTRIBUTION nc on rel.nr_contribution_id = nc.id
        inner join FACTS.PROD.NR_SOUND_RECORDING nsr on rel.nr_sound_recording_id = nsr.id
        where 
            nc.last_modified_at < try_to_date('{job_last_updated}')
            and nsr.last_modified_at < try_to_date('{job_last_updated}')
        and nc.id in ('{ids_string}')
    """
    cursor.execute(query)
    result = cursor.fetchall()
    return [x['ID'] for x in result]