from sqlalchemy import text

class SQLGenerate():
    
    def __init__(self):
        pass
    
    def merge_updates(self):
        updatesql = text('''
            MERGE updates.tvupdatedata
            USING updates.tvupdates
            ON updates.tvupdatedata.seriesid = updates.tvupdates.seriesid
            WHEN MATCHED THEN
              UPDATE  SET updates.tvupdatedata.seriesid = updates.tvupdates.seriesid,updates.tvupdatedata.timestamp = updates.tvupdates.timestamp
            WHEN NOT MATCHED THEN  
              INSERT (seriesid, timestamp) 
              VALUES (updates.tvupdates.seriesid, updates.tvupdates.timestamp);
            ''')
        return(updatesql)
    
    def merge_series(self):
        seriessql = text('''
            MERGE dbo.seriesdata
              USING updates.newseriesdata ON
            dbo.seriesdata.seriesid = updates.newseriesdata.seriesid
            WHEN MATCHED THEN UPDATE
            SET
              dbo.seriesdata.seriesid = updates.newseriesdata.seriesid,
              dbo.seriesdata.thetvdb = updates.newseriesdata.thetvdb,
              dbo.seriesdata.tvrage = updates.newseriesdata.tvrage,
              dbo.seriesdata.imdb = updates.newseriesdata.imdb,
              dbo.seriesdata.[language] = updates.newseriesdata.[language],
              dbo.seriesdata.genres = updates.newseriesdata.genres,
              dbo.seriesdata.name = updates.newseriesdata.name,
              dbo.seriesdata.status = updates.newseriesdata.status,
              dbo.seriesdata.premiered = updates.newseriesdata.premiered,
              dbo.seriesdata.days = updates.newseriesdata.days,
              dbo.seriesdata.[time] = updates.newseriesdata.[time],
              dbo.seriesdata.runtime = updates.newseriesdata.runtime,
              dbo.seriesdata.rating = updates.newseriesdata.rating,
              dbo.seriesdata.[type] = updates.newseriesdata.[type],
              dbo.seriesdata.summary = updates.newseriesdata.summary,
              dbo.seriesdata.medium = updates.newseriesdata.medium,
              dbo.seriesdata.original = updates.newseriesdata.original,
              dbo.seriesdata.[weight] = updates.newseriesdata.[weight],
              dbo.seriesdata.networkid = updates.newseriesdata.networkid,
              dbo.seriesdata.webchannelid = updates.newseriesdata.webchannelid,
              dbo.seriesdata.officialsite = updates.newseriesdata.officalsite,
              dbo.seriesdata.url = updates.newseriesdata.url,
              dbo.seriesdata.serieslink = updates.newseriesdata.serieslink,
              dbo.seriesdata.updated_on = GETDATE()
              WHEN NOT MATCHED THEN INSERT
                (seriesid,
                thetvdb,
                tvrage,
                imdb,
                [language],
                genres,
                name,
                status,
                premiered,
                days,
                [time],
                runtime,
                rating,
                [type],
                summary,
                medium,
                original,
                weight,
                networkid,
                webchannelid,
                officialsite,
                url,
                serieslink,
                created_on)
              VALUES (updates.newseriesdata.seriesid,
              updates.newseriesdata.thetvdb,
              updates.newseriesdata.tvrage,
              updates.newseriesdata.imdb,
              updates.newseriesdata.[language],
              updates.newseriesdata.genres,
              updates.newseriesdata.name,
              updates.newseriesdata.status,
              updates.newseriesdata.premiered,
              updates.newseriesdata.days,
              updates.newseriesdata.[time],
              updates.newseriesdata.runtime,
              updates.newseriesdata.rating,
              updates.newseriesdata.[type],
              updates.newseriesdata.summary,
              updates.newseriesdata.medium,
              updates.newseriesdata.original,
              updates.newseriesdata.weight,
              updates.newseriesdata.networkid,
              updates.newseriesdata.webchannelid,
              updates.newseriesdata.officalsite,
              updates.newseriesdata.url,
              updates.newseriesdata.serieslink,
              GETDATE());
            ''')
        return(seriessql)

    def merge_episodes(self):
        episodesql = text('''
            MERGE dbo.epdata
              USING updates.newepdata ON
            dbo.epdata.episodeid = updates.newepdata.episodeid
            WHEN MATCHED THEN UPDATE
            SET
              dbo.epdata.episodeid = updates.newepdata.episodeid,
              dbo.epdata.seriesid = updates.newepdata.seriesid,
              dbo.epdata.season = updates.newepdata.season,
              dbo.epdata.episodenumber = updates.newepdata.episodenumber,
              dbo.epdata.episodename = updates.newepdata.episodename,
              dbo.epdata.airdate = updates.newepdata.airdate,
              dbo.epdata.airtime = updates.newepdata.airtime,
              dbo.epdata.episodesummary = updates.newepdata.episodesummary,
              dbo.epdata.airstamp = updates.newepdata.airstamp,
              dbo.epdata.runtime = updates.newepdata.runtime,
              dbo.epdata.eplink = updates.newepdata.eplink,
              dbo.epdata.mediumimage = updates.newepdata.mediumimage,
              dbo.epdata.originalimage = updates.newepdata.originalimage,
              dbo.epdata.[url] = updates.newepdata.[url],
              dbo.epdata.updated_on = GETDATE()
              WHEN NOT MATCHED THEN INSERT
                (episodeid,
                seriesid,
                season,
                episodenumber,
                episodename,
                airdate,
                airtime,
                episodesummary,
                airstamp,
                runtime,
                eplink,
                mediumimage,
                originalimage,
                [url],
                created_on)
              VALUES (updates.newepdata.episodeid,
              updates.newepdata.seriesid,
              updates.newepdata.season,
              updates.newepdata.episodenumber,
              updates.newepdata.episodename,
              updates.newepdata.airdate,
              updates.newepdata.airtime,
              updates.newepdata.episodesummary,
              updates.newepdata.airstamp,
              updates.newepdata.runtime,
              updates.newepdata.eplink,
              updates.newepdata.mediumimage,
              updates.newepdata.originalimage,
              updates.newepdata.[url],
              GETDATE());
            ''')
        return(episodesql)   

    def merge_cast(self):
        castsql = text('''
        MERGE castdata as N
        USING updates.newcastdata as O
        ON N.seriesid = O.seriesid and N.characterid = O.characterid and N.actorid = O.actorid
         WHEN MATCHED THEN
          UPDATE  SET N.seriesid = O.seriesid, N.characterid = O.characterid,N.actorid = O.actorid,N.charactername = O.charactername,N.mediumimage = O.mediumimage,N.originalimage = O.originalimage,N.apilink = O.apilink,N.tvmazelink = O.tvmazelink, N.updated_on = GETDATE()
        WHEN NOT MATCHED THEN  
          INSERT (seriesid,characterid,actorid,charactername,mediumimage,originalimage,apilink,tvmazelink, created_on) 
          VALUES 
          (O.seriesid,O.characterid,O.actorid,O.charactername,O.mediumimage,O.originalimage,O.apilink,O.tvmazelink, GETDATE());
        ''')
        return(castsql)
    
    def merge_actors(self):
        actorsql = text('''
        MERGE actors as N
        USING updates.newactordata as O
        ON N.actorid = O.actorid and N.actorname = O.actorname
         WHEN MATCHED THEN
          UPDATE  SET N.actorid = O.actorid, N.actorname = O.actorname, N.countryname = O.countryname, N.countrytimezone = O.countrytimezone, N.birthday = O.birthday, N.deathday = O.deathday, N.gender = O.gender, N.mediumimage = O.mediumimage, N.originalimage = O.originalimage, N.apilink = O.apilink, N.url = O.url, N.countrycode = O.countrycode, N.updated_on = GETDATE()
        WHEN NOT MATCHED THEN  
          INSERT (actorid,actorname,countryname,countrytimezone,birthday,deathday,gender,mediumimage,originalimage,apilink,url,countrycode,created_on) 
          VALUES 
          (O.actorid,O.actorname,O.countryname,O.countrytimezone,O.birthday,O.deathday,O.gender,O.mediumimage,O.originalimage,O.apilink,O.url,O.countrycode, GETDATE());
        ''')
        return(actorsql)
    
    def merge_crew(self):
      crewsql = text('''
      MERGE dbo.crewdata as N
      USING updates.newcrewdata as O ON
      N.[type] = O.[type]
      and N.seriesid = O.seriesid
      and N.crewid = O.crewid
      WHEN MATCHED THEN UPDATE
      SET
        N.seriesid = O.seriesid,
        N.crewid = O.crewid,
        N.countryid = O.countryid,
        N.type = O.type,
        N.name = O.name,
        N.medium = O.medium,
        N.original = O.original,
        N.apilink = O.apilink,
        N.gender = O.gender,
        N.birthday = O.birthday,
        N.deathday = O.deathday,
        N.updated_on = GETDATE()
        WHEN NOT MATCHED THEN INSERT
          (seriesid,
          crewid,
          countryid,
          type,
          name,
          medium,
          original,
          apilink,
          gender,
          birthday,
          deathday,
          created_on)
        VALUES (O.seriesid,
          O.crewid,
          O.countryid,
          O.type,
          O.name,
          O.medium,
          O.original,
          O.apilink,
          O.gender,
          O.birthday,
          O.deathday,
        GETDATE());
        ''')
      return(crewsql)
    
    def merge_country(self):
        countrysql = text('''
            MERGE countrydata
            USING updates.newcountrydata
            ON countrydata.id = updates.newcountrydata.id
            WHEN MATCHED THEN
              UPDATE  SET countrydata.name = updates.newcountrydata.name, countrydata.code = updates.newcountrydata.code, countrydata.timezone = updates.newcountrydata.timezone, countrydata.updated_on = GETDATE()
            WHEN NOT MATCHED THEN  
              INSERT (name, code, timezone, created_on) 
              VALUES 
              (updates.newcountrydata.name,updates.newcountrydata.code,updates.newcountrydata.timezone, GETDATE());
            ''')
        return(countrysql)   
                
    def __del__(self):
        pass
        
class Charactermerge():
    
    def __init__(self):
        pass
    
    def __del__(self):
        pass

class Episodemerge():
    def __init_(self):
        pass

    def __del__(self):
        pass

class Jsonmerge():
    
    def __init__(self):
        pass
    
    def merge(self, engine):
        seriessql = text('''BEGIN TRANSACTION;
            MERGE dbo.seriesjson as N
            USING updates.seriesjsonnewvals as O
            ON N.seriesid = O.seriesid and N.jdata = O.jdata
            WHEN MATCHED THEN
              UPDATE  SET N.seriesid = O.seriesid, N.jdata = O.jdata, N.updated_on = GETDATE()
            WHEN NOT MATCHED THEN  
              INSERT (seriesid, jdata, created_on) 
              VALUES 
              (O.seriesid, O.jdata, GETDATE());
            COMMIT;
            ''')
        with engine.connect() as con:
            rs = con.execute(seriessql)
        #engine.commit()
            
        episodesql = text('''BEGIN TRANSACTION;
            MERGE updates.tvmaze_episodejson as N
            USING updates.episodejsonnewvals as O
            ON N.seriesid = O.seriesid
             WHEN MATCHED THEN
              UPDATE  SET N.seriesid = O.seriesid, N.jdata = O.jdata, N.updated_on = GETDATE()
            WHEN NOT MATCHED THEN  
              INSERT (seriesid, jdata, created_on) 
              VALUES 
              (O.seriesid, O.jdata, GETDATE());
            SELECT * FROM updates.tvmaze_episodejson;COMMIT;
            ''')
        with engine.connect() as con:
            rs = con.execute(episodesql)
        #engine.commit()
            
        charactersql = text('''BEGIN TRANSACTION;
            MERGE updates.tvmaze_characterjson as N
            USING updates.characterjsonnewvals as O
            ON N.seriesid = O.seriesid
             WHEN MATCHED THEN
              UPDATE  SET N.seriesid = O.seriesid, N.jdata = O.jdata, N.updated_on = GETDATE()
            WHEN NOT MATCHED THEN  
              INSERT (seriesid, jdata, created_on) 
              VALUES 
              (O.seriesid, O.jdata, GETDATE());
            SELECT * FROM updates.tvmaze_characterjson;COMMIT;
            ''')
        with engine.connect() as con:
            rs = con.execute(charactersql)
        #engine.commit()
    
    def __del__(self):
        pass