# -*- coding: utf-8 -*-
# Python 3.8
import os, socket, sys, traceback, urllib3, urllib.parse as urlparse, pycurl, time
from io import BytesIO
from simplejson.encoder import JSONEncoder
from simplejson.decoder import JSONDecoder

_iptvportal_auth_header = None

_iptvportal_server = 'DOMAIN.admin.iptvportal.ru' # DOMAIN = Your domain in the IPTVPORTAL platform
_username = 'admin'
_password = 'PASSWORD'

##############################################################################################################################################

_jsonrpc_url = 'https://' + _iptvportal_server + '/api/jsonrpc/'
_jsonsql_url = 'https://' + _iptvportal_server + '/api/jsonsql/'

##############################################################################################################################################

_id = 1
def NEW_UNICAL_ID ():
    global _id
    id = _id
    _id += 1
    return id



def send (url, data, headers={}):
    if type (data) is dict:
        print ("data is list", type (data))
    data = JSONEncoder (ensure_ascii=True).encode(data)
    print ("send data: ", data)
    method = 'POST'
    timeout = 300
    cacert = None
    c = pycurl.Curl ()
    c.setopt (pycurl.URL, str (url))
    buf = BytesIO ()
    c.setopt (pycurl.WRITEFUNCTION, buf.write)
    #c.setopt(pycurl.READFUNCTION, read)
    #data = StringIO (data)
    #c.setopt(pycurl.HEADERFUNCTION, header)
    if cacert:
        c.setopt (c.CAINFO, (cacert)) 
    c.setopt (pycurl.SSL_VERIFYPEER, cacert and 1 or 0)
    c.setopt (pycurl.SSL_VERIFYHOST, cacert and 2 or 0)
    c.setopt (pycurl.ENCODING, 'gzip')
    c.setopt (pycurl.CONNECTTIMEOUT, 30)
    c.setopt (pycurl.TIMEOUT, timeout)
    if method == 'POST':
        c.setopt (pycurl.POST, 1)
        c.setopt (pycurl.POSTFIELDS, data)
    if headers:
        hdrs = ['%s: %s' % (str(k), str(v)) for k, v in headers.items ()]
        c.setopt (pycurl.HTTPHEADER, hdrs)
    c.perform ()
    c.close ()
    res = buf.getvalue ()
    # print ("res:", res)
    if not res:
        print ("error: not result")
        return None
    res = JSONDecoder (encoding='utf-8').decode (res)
    # print ("res:", res)
    if type (res) is list:
        return res
    elif not res.get ('result'):
        print ("error:" , res.get ('error'))
        return False
    else:
        return res.get ('result')



def authorize_user (auth_url, username, password):
    data = {
        'jsonrpc' : "2.0",
        'id'      : NEW_UNICAL_ID (),
        'method'  : "authorize_user",
        'params'  : { 'username': username, 'password': password },
    }
    res = send ( auth_url, data)
    #print ("res:", res)
    if res:
        iptvportal_auth_header = {
            'Iptvportal-Authorization': 'sessionid=' + res.get ('session_id'),
            #'Iptvportal-Jsonsql': 'no-modify'
        }
    return iptvportal_auth_header



##############################################################################################################################################

if _iptvportal_auth_header is None:
    _iptvportal_auth_header = authorize_user (_jsonrpc_url, _username, _password)
    print ('authorize _iptvportal_auth_header : ', _iptvportal_auth_header)


##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################


# Get subscribers info
if 1==1: 
    data = []
    data += [{
        'jsonrpc' : "2.0",
        'id' : NEW_UNICAL_ID (),
        "method" : "select",
        "params" : {
            "data": [ 
                {'s': "id"},
                {'s': "username"},
                {'s': "password"},
                {'s': "disabled"},
            ],
            "from": [
                {"table": "subscriber", "as": "s"},
            ],
            ## if need select only for users list:
            # "where": {
            #     "and": [
            #         {"in": [{'s': "username"}, ["12345","1111"] ]},
            #     ]
            # },
            'order_by': [ {'s': "username"} ],
        }
    }]
    res = send ( _jsonsql_url, data, _iptvportal_auth_header)
    print ("res = ",res)




# Get subscribers and subscriber_package info
if 1==1: 
    data = []
    data += [{
        'jsonrpc' : "2.0",
        'id' : NEW_UNICAL_ID (),
        "method" : "select",
        "params" : {
            "data": [ 
                {'s': "id"},
                {'s': "username"},
                {'s': "password"},
                {'s': "disabled"},
                {'p': "id"},
                {'p': "name"},
                {'sp': "enabled"},
            ],
            "from": [
                {"table": "subscriber", "as": "s"},
                {'join': "subscriber_package", 'as': "sp", 'on': {'eq': [{'sp':"subscriber_id"}, {'s':"id"}]}},
                {'join': "package", 'as': "p", 'on': {'eq': [{'p':"id"}, {'sp':"package_id"}]}},
            ],
            ## if need select only for users list:
            # "where": { 
            #     "and": [
            #         {"in": [{'s': "username"}, ["12345","1111"] ]},
            #     ]
            # },
        }
    }]
    res = send ( _jsonsql_url, data, _iptvportal_auth_header)
    print ("res = ",res)





# create or update subscribers list with checking of subscriber existence
if 1==1: 
    subscribers__username_password_disabled = [
        ["new_subscriber_1", "pass111", False],
        ["new_subscriber_2", "pass222", False],
        ["new_subscriber_3", "pass333", False],
    ]
    data = []
    data += [{
        'jsonrpc' : "2.0",
        'id' : NEW_UNICAL_ID (),
        "method": "insert",
        "params": {
            "into": "subscriber",
            "columns": [
                "username",
                "password",
                "disabled",
            ],
            "values": subscribers__username_password_disabled,
            "on_conflict": {
                "columns": [
                    "username",
                ],
                "do": "update",
                "set": {
                    "username":{"excluded":"username"},
                    "password":{"excluded":"password"},
                    "disabled":{"excluded":"disabled"},
                },
                
            },
            "returning" : ["id"]
        }
    }]
    res = send ( _jsonsql_url, data, _iptvportal_auth_header)
    print ("res = ",res)


# add or update packages to subscribers list with checking of subscriber_package existence
# if the subscriber_package already exists, then updating the information
if 1==1:
    subscribers__username_package = [
        ["new_subscriber_1","paid1"],
        ["new_subscriber_1","paid2"],
        ["new_subscriber_2","paid2"],
    ]
    data = []
    data += [{
        'jsonrpc' : "2.0",
        'id' : NEW_UNICAL_ID (),
        "method": "insert",
        "params": {
            "into": "subscriber_package",
            "columns": [
                "subscriber_id",
                "package_id",
                "enabled",
            ],
            'select': {   
                "data": [ 
                    {'s': "id"},
                    {'p': "id"},
                    True,
                ],
                "from": [
                    {"values": subscribers__username_package, "as": ["values_array","s_username","p_name"] },
                    {'join': "subscriber", 'as': "s", 'on': {'eq': [{'s':"username"}, {'values_array':"s_username"} ]} },
                    {'join': "package", 'as': "p", 'on': {'eq': [{'p':"name"}, {'values_array':"p_name"} ]} },
                ],
            },
            "on_conflict": {
                "columns": [
                    "subscriber_id",
                    "package_id",
                ],
                "do": "update",
                "set": {
                    "subscriber_id":{"excluded":"subscriber_id"},
                    "package_id":{"excluded":"package_id"},
                    "enabled":{"excluded":"enabled"},
                },
                
            },
            "returning" : ["subscriber_id","package_id"]
        }
    }]
    res = send ( _jsonsql_url, data, _iptvportal_auth_header)
    print ("res = ",res)




# update subscriber (disconnection)
if 1==1:
    data = []
    data += [{
        'jsonrpc' : "2.0",
        'id' : NEW_UNICAL_ID (),
        "method": "update",
        "params": {
            'table': 'subscriber',
            'set': {'disabled': True},
            'where': { 'eq': ['username', 'new_subscriber_1'] },
            'returning': 'id',
        }
    }]
    res = send ( _jsonsql_url, data, _iptvportal_auth_header)
    print ("res = ",res)



# delete custom packages at subscriber
if 1==1:
    data = []
    data += [{
        'jsonrpc' : "2.0",
        'id' : NEW_UNICAL_ID (),
        "method": "delete",
        "params": {
            "from": "subscriber_package",
            "where":{
                "and":[
                    {"in":["subscriber_id", {"select":{"data":"id","from":"subscriber","where":{"eq":["username","new_subscriber_1"]}}} ]},
                    {"in":["package_id", {"select":{"data":"id","from":"package","where":{"in":["name", ["paid1","paid2"] ]}}} ]},
                ]
            },
            "returning" : ["subscriber_id","package_id"]
        }
    }]
    res = send ( _jsonsql_url, data, _iptvportal_auth_header)
    print ("res = ",res)



# delete all packages at subscriber
if 1==1:
    data = []
    data += [{
        'jsonrpc' : "2.0",
        'id' : NEW_UNICAL_ID (),
        "method": "delete",
        "params": {
            "from": "subscriber_package",
            "where":{"in":["subscriber_id", {"select":{"data":"id","from":"subscriber","where":{"eq":["username","new_subscriber_2"]}}} ]},
            "returning" : ["subscriber_id","package_id"]
        }
    }]
    res = send ( _jsonsql_url, data, _iptvportal_auth_header)
    print ("res = ",res)