def generate_explicit_dt(explicit_files):
    with open(explicit_files,'r') as f:
        files=f.read().split('\n')
        files=files[:-1]

        for file in files:
            generate_explicit_sql(file)


def generate_explicit_sql(filename):
    from copy import deepcopy

    sql='''(SELECT
    r.upc,
    r.release_name,
     ai.name AS artist_name,
     r.sale_start_date,
     r.format,
     tr.id AS tuid,
     tr.isrc,
     tr.track_name,
     v.vendor_id AS label_id,
     IFF(v.company = '' OR v.company IS NULL, v.name, v.company) AS label_name,
     (oa.f_name || ' '||oa.l_name) AS label_manager,
     l.language,
     mpi.description,
     regexp_substr(mpi.description,{a}) AS explicit_words
    FROM orchard_app_reporting.art_relations.mkt_program_info mpi
     INNER JOIN orchard_app_reporting.art_relations.track tr ON tr.id = mpi.info_for_id
     INNER JOIN orchard_app_reporting.art_relations.releases r ON r.release_id = tr.release_id
     INNER JOIN orchard_app_reporting.art_relations.artist_info ai ON r.artist_id = ai.artist_id
     INNER JOIN orchard_app_reporting.art_relations.vendor v ON ai.vendor_id = v.vendor_id
     INNER JOIN orchard_app_reporting.art_relations.language l ON l.language_code=tr.meta_language
     LEFT JOIN orchard_app_reporting.art_relations.orchadmin_users oa ON v.assigned_to = oa.id
    WHERE tr.explicit_lyrics = 'N'
     AND l.language ILIKE \'%{c}%\'
     AND r.sale_start_date > CURRENT_DATE()
     AND mpi.info_for = 'track'
     AND (regexp_like(mpi.description,{b})
     )
     AND tr.id <> '29334170'
    ORDER BY 4 ASC)
    UNION ALL

    '''

    sql=sql.replace('\n',' ')

    sql=' '.join(sql.split())

    exp=''
    with open(filename,'r') as f:
        words=f.read().split('\n')
        words=words[:-1]
        exp+='\''

        for line in words:
            if "'" in line:
                line=line.replace("'","\\'")
            exp+='.*'
            exp+=line
            exp+='.*'
            exp+='|'
        exp=exp[:-1]
        exp+='\''
        exp2=exp.replace('.*','')
        language=filename.split('_')[0]


    print(sql.format(a=exp2,b=exp,c=language))

generate_explicit_dt('explicit_files.txt')