from lmf.dbv2 import db_command, db_query
import json
from lmfscrap import web, page
import traceback


# 表名解析器


def ext_tb(tbname):
    tb_arr = tbname.split("_")

    a = {}

    jytype_dict = {"gcjs": "工程建设", "zfcg": "政府采购", "yiliao": "医疗采购", "jqita": "其它类型", "qsy": "企事业单位", "qycg": "企业采购",
                   "xm": "项目"
                   }

    ggtype_dict = {"zhaobiao": "招标公告", "zhongbiao": "中标公告", "zhongbiaohx": "中标候选公告", "liubiao": "流标公告",

                   "kaibiao": "开标公告", "zgys": "资格预审公告", "zgysjg": "资格预审结果公告", 'zsjg': "资审结果", 'dyly': "单一来源",

                   "gqita": "其他公告", "kongzhijia": "控制价公告", "yucai": "预采公告",

                   "biangeng": "变更公告", "dayi": "答疑公告", "hetong": "合同公告", "yanshou": "验收公告", "zhongzhi": "终止公告",

                   # 项目审批
                   "beian": "备案", "hezhun": "核准", "jieguo": "结果", "hzqgs": "核准前公示",
                   "shenpi": "审批", "pihou": "批后", "kaigong": "开工", "jungong": "竣工"

    }

    ggtype_set = set(ggtype_dict)
    jytype_set = set(jytype_dict)

    xmztype_set = {"fangwu", "jiaotong", "shuili", "shizheng", "dianli"}
    for w in tb_arr:
        if w in jytype_set:
            a["jytype"] = jytype_dict[w]
        if w in ggtype_set:
            a["ggtype"] = ggtype_dict[w]
    if "ggtype" not in a.keys(): a["ggtype"] = None
    if "jytype" not in a.keys(): a["jytype"] = None
    return a


def create_gg(conp):
    sql1 = """
    drop table if exists %s.gg;
    create table if not exists %s.gg
    (
    name text,
    href text,
    ggstart_time text,
    ggtype text,
    jytype text,
    diqu  text,
    info  text ,
    primary key(name,href,ggstart_time)

    )
    """ % (conp[4], conp[4])

    db_command(sql1, dbtype="postgresql", conp=conp)


def insert_tb(tbname, diqu, conp):
    data = ext_tb(tbname)
    ggtype = data["ggtype"]
    jytype = data["jytype"]
    # info=data["info"]
    ggtype = "'%s'" % ggtype if ggtype is not None else "NULL"

    jytype = "'%s'" % jytype if jytype is not None else "NULL"
    schema = conp[4]

    print("gg_cdc开始，%s" % tbname)
    sql2 = """
    insert into %s.gg
    select  distinct on (name,href,ggstart_time ) name,href,ggstart_time,%s::text as ggtype,%s::text as jytype,
    '%s'::text diqu, info from %s.%s as a
    where not exists(select 1 from %s.gg as b where a.name=b.name and a.href =b.href and a.ggstart_time=b.ggstart_time)
    """ % (schema, ggtype, jytype, diqu, schema, tbname, schema)

    db_command(sql2, dbtype="postgresql", conp=conp)
    print("gg_cdc结束，%s" % tbname)


# 第一次形成gg表
def gg(conp, diqu, i=-1):
    create_gg(conp)
    sql = """
    select table_name from information_schema.tables where table_schema='%s' and table_name ~'_gg$' order by table_name
    """ % conp[4]

    df = db_query(sql, conp=conp, dbtype="postgresql")
    data = df['table_name'].tolist()
    if i == -1:
        data = data
    else:
        data = data[i:i + 1]
    for tbname in data:
        insert_tb(tbname, diqu=diqu, conp=conp)


# 后续更新公告表

def gg_cdc(conp, diqu, i=-1):
    # create_gg(conp)
    sql = """
    select table_name from information_schema.tables where table_schema='%s' and table_name ~'_gg_cdc$' order by table_name
    """ % conp[4]

    df = db_query(sql, conp=conp, dbtype="postgresql")
    data = df['table_name'].tolist()
    if i == -1:
        data = data
    else:
        data = data[i:i + 1]
    for tbname in data:
        insert_tb(tbname, diqu=diqu, conp=conp)


# 一次爬入所有
def work(conp, data, diqu, i=-1, headless=True, add_ip_flag=False):
    data = data.copy()
    if i == -1:
        data = data
    else:
        data = data[i:i + 1]
    for w in data:
        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0],
            "col": w[2],
            "conp": conp,
            "num": 10,
            "headless": headless
        }

        m = web(add_ip_flag=add_ip_flag)

        m.write(**setting)
    gg(conp, diqu)


def est_tables(conp, data, headless=True, add_ip_flag=False):
    data = data.copy()
    for w in data:
        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0],
            "col": w[2],
            "conp": conp,
            "num": 10,
            "headless": headless
        }

        m = web(add_ip_flag=add_ip_flag)

        m.write(**setting)


def cdc_sql(conp, tb):
    schema = conp[4]

    sql = """
        insert into {shm}.{tb}
        select * from {shm}.{tb}_cdc as a
        where not exists(select 1 from {shm}.{tb} as b where a.name=b.name and a.href =b.href and a.ggstart_time=b.ggstart_time)
        """.format(shm=schema, tb=tb)

    # sql = """insert into %s.%s
    # select * from %s.%s_cdc
    # except
    # select * from %s.%s
    # """ % (schema, tb, schema, tb, schema, tb)

    db_command(sql, dbtype="postgresql", conp=conp)


def cdc(conp, data, diqu, i=-1, headless=True, add_ip_flag=False):
    data = data.copy()
    if i == -1:
        data = data
    else:
        data = data[i:i + 1]
    for w in data:
        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0] + "_cdc",
            "col": w[2],
            "conp": conp,
            "num": 4,
            "total": 3,
            "headless": headless
        }

        m = web(add_ip_flag=add_ip_flag)

        m.write(**setting)
        cdc_sql(conp, w[0])

    gg_cdc(conp, diqu)


def est_tables_cdc(conp, data, headless=True, add_ip_flag=False):
    data = data.copy()

    for w in data:
        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0] + "_cdc",
            "col": w[2],
            "conp": conp,
            "num": 4,
            "total": 3,
            "headless": headless
        }

        m = web(add_ip_flag=add_ip_flag)

        m.write(**setting)
        cdc_sql(conp, w[0])


def gg_meta(conp, data, diqu, i=-1, headless=True):
    sql = """select table_name from information_schema.tables where table_schema='%s'""" % (conp[4])
    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["table_name"].values

    if "gg" in arr:
        cdc(conp, data, diqu, i, headless)
    else:
        work(conp, data, diqu, i, headless)


#####################################################get_html##########################################


def html_work(conp, f, size=None, headless=True):
    m = page()
    if size is not None:
        sql = "select distinct href from %s.gg where not coalesce(info,'{}')::jsonb?'hreftype' or coalesce(info,'{}')::jsonb->>'hreftype'='可抓网页' limit %d" % (
            conp[4], size)
    else:
        sql = "select distinct href from %s.gg where not coalesce(info,'{}')::jsonb?'hreftype' or coalesce(info,'{}')::jsonb->>'hreftype'='可抓网页' " % (
            conp[4])

    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["href"].values
    print(arr[:3])
    setting = {"num": 20, "arr": arr, "f": f, "conp": conp, "tb": "gg_html", "headless": headless}
    m.write(**setting)


"""
update  "weihai"."gg"

set info=coalesce(info,'{}')::jsonb ||'{"hreftype":"不可抓网页"}'
 where  href in (select distinct href from weihai.gg where href not in(select href from weihai.gg_html ) 


and (not coalesce(info,'{}')::jsonb?'hreftype' or coalesce(info,'{}')::jsonb->>'hreftype'='可抓网页'))

"""


def html_cdc(conp, f, headless=True):
    m = page()
    sql = "select distinct href from %s.gg where href not in(select href from %s.gg_html ) and (not coalesce(info,'{}')::jsonb?'hreftype' or coalesce(info,'{}')::jsonb->>'hreftype'='可抓网页')" % (
        conp[4], conp[4])

    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["href"].values
    if arr == []:
        print("无href更新")
        return None

    setting = {"num": 5, "arr": arr, "f": f, "conp": conp, "tb": "gg_html", "headless": headless}
    m.write(**setting)


def gg_html(conp, f, headless=True):
    sql = """select table_name from information_schema.tables where table_schema='%s'""" % (conp[4])
    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["table_name"].values

    if "gg_html" in arr:
        html_cdc(conp, f, headless=headless)
    else:
        html_work(conp, f, headless=headless)


def est_tables(conp, data, headless=True, add_ip_flag=False):
    data = data.copy()
    for w in data:
        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0],
            "col": w[2],
            "conp": conp,
            "num": 10,
            "headless": headless
        }
        m = web(add_ip_flag=add_ip_flag)

        m.write(**setting)


###############y优化一些接口
def est_tbs(conp, data, **args):
    data = data.copy()

    for w in data:
        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0],
            "col": w[2],
            "conp": conp,
            "num": 10,
            "headless": True
        }
        if "num" in args.keys():
            setting["num"] = args["num"]
        if "total" in args.keys():
            setting["total"] = args["total"]
        if "headless" in args.keys():
            setting["headless"] = args["headless"]
        setting = {**setting, **args}

        if "add_ip_flag" in args.keys():
            add_ip_flag = args["add_ip_flag"]
        else:
            add_ip_flag = False
        m = web(add_ip_flag=add_ip_flag)

        m.write(**setting)


def est_gg(conp, **arg):
    if "diqu" in arg.keys():
        diqu = arg["diqu"]
    else:
        diqu = "未知"
    create_gg(conp)
    sql = """
    select table_name from information_schema.tables where table_schema='%s' and table_name ~'_gg$' order by table_name
    """ % conp[4]

    df = db_query(sql, conp=conp, dbtype="postgresql")
    data = df['table_name'].tolist()

    for tbname in data:
        insert_tb(tbname, diqu=diqu, conp=conp)


def est_work(conp, data, **arg):
    est_tbs(conp, data, **arg)
    est_gg(conp, **arg)


def est_cdc(conp, data, **args):
    data = data.copy()

    for w in data:

        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0] + "_cdc",
            "col": w[2],
            "conp": conp,
            "num": 4,
            "total": 3,
            "headless": True
        }

        setting = {**setting, **args}

        if "num" in args.keys():
            setting["num"] = args["num"]

        if "cdc_total" in args.keys():
            setting["total"] = args["cdc_total"]

        if "diqu" in args.keys():
            diqu = args["diqu"]
        else:
            diqu = "未知"

        if "add_ip_flag" in args.keys():
            add_ip_flag = args["add_ip_flag"]
        else:
            add_ip_flag = False
        m = web(add_ip_flag=add_ip_flag)
        m.write(**setting)
        print("sql_cdc开始...(%s)" % setting["tb"])
        cdc_sql(conp, w[0])
        print("sql_cdc完成")
    gg_cdc(conp, diqu)


def est_meta(conp, data, **arg):
    sql = """select table_name from information_schema.tables where table_schema='%s'""" % (conp[4])
    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["table_name"].values

    if "gg" in arr:
        est_cdc(conp, data, **arg)
    else:
        est_work(conp, data, **arg)


def add_info(f, info):
    def wrap(*arg):
        df = f(*arg)
        if "info" not in df.columns:
            df[df.columns[-1]] = df[df.columns[-1]].map(lambda x: json.dumps({**(json.loads(x)), **(info)},
                                                                             ensure_ascii=False) if x is not None else json.dumps(
                info, ensure_ascii=False))
        else:
            df["info"] = df["info"].map(lambda x: json.dumps({**(json.loads(x)), **(info)},
                                                             ensure_ascii=False) if x is not None else json.dumps(info,
                                                                                                                  ensure_ascii=False))
        return df

    return wrap


def est_html_work(conp, f, **args):
    if "size" in args.keys():
        size = args["size"]
    else:
        size = None

    if size is not None:
        sql = "select distinct href from %s.gg where not coalesce(info,'{}')::jsonb?'hreftype' or coalesce(info,'{}')::jsonb->>'hreftype'='可抓网页' limit %d" % (
            conp[4], size)
    else:
        sql = "select distinct href from %s.gg where not coalesce(info,'{}')::jsonb?'hreftype' or coalesce(info,'{}')::jsonb->>'hreftype'='可抓网页' " % (
            conp[4])

    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["href"].values
    if "add_ip_flag" in args.keys():
        add_ip_flag = args["add_ip_flag"]
    else:
        add_ip_flag = False

    if "html_total" in args.keys():
        html_total = args["html_total"]
        arr = arr[:html_total]

    print(arr[:3])
    setting = {"num": 20, "arr": arr, "f": f, "conp": conp, "tb": "gg_html", "headless": True}

    if "num" in args.keys():
        setting["num"] = args["num"]
    setting = {**setting, **args}

    m = page(add_ip_flag=add_ip_flag)
    m.write(**setting)


def est_html_cdc(conp, f, **args):
    print("查询本次需要抓取的href")
    # sql="select distinct href from %s.gg where href not in(select href from %s.gg_html ) and (not coalesce(info,'{}')::jsonb?'hreftype' or coalesce(info,'{}')::jsonb->>'hreftype'='可抓网页')"%(conp[4],conp[4])
    sql = "select distinct href from %s.gg as a  where  not exists (select 1 from %s.gg_html as b where a.href=b.href )" % (
        conp[4], conp[4])
    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["href"].unique().tolist()
    if arr == []:
        print("无href更新")
        return None
    if "html_total" in args.keys():
        html_total = args["html_total"]
        arr = arr[:html_total]

    setting = {"num": 5, "arr": arr, "f": f, "conp": conp, "tb": "gg_html", "headless": True}
    if "num" in args.keys():
        setting["num"] = args["num"]
    else:
        if len(arr) > 2000 and setting['num'] < 20:
            setting["num"] = 20
        else:
            setting["num"] = 5

    if "add_ip_flag" in args.keys():
        add_ip_flag = args["add_ip_flag"]
    else:
        add_ip_flag = False

    setting = {**setting, **args}

    m = page(add_ip_flag=add_ip_flag)
    m.write(**setting)


def est_html(conp, f, **args):
    sql = """select table_name from information_schema.tables where table_schema='%s'""" % (conp[4])
    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["table_name"].values

    if "gg_html" in arr:
        est_html_cdc(conp, f, **args)
    else:
        est_html_work(conp, f, **args)


def gg_existed(conp):
    sql = """select table_name from information_schema.tables where table_schema='%s'""" % (conp[4])
    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["table_name"].values
    if "gg" in arr:
        return True
    else:
        return False


#### 总页数过多 写入表函数
def get_items_large(conp, data, **krg):
    data = data.copy()

    for w in data:
        setting = {
            "url": w[1],
            "f1": w[3],
            "f2": w[4],
            "tb": w[0],
            "col": w[2],
            "conp": conp,
            "num": 10,
            "headless": True
        }

        if "headless" in krg.keys():
            setting["headless"] = krg["headless"]
        if "num" in krg.keys():
            setting["num"] = krg["num"]

        ### 页数分割数
        if "interval_page" in krg.keys():
            interval_page = krg["interval_page"]
        else:
            interval_page = 500

        setting = {**setting, **krg}

        if "add_ip_flag" in krg.keys():
            add_ip_flag = krg["add_ip_flag"]
        else:
            add_ip_flag = False

        if "ipNum" in krg.keys():
            ipNum = krg["ipNum"]
        else:
            ipNum = 5
        if "retry" in krg.keys():
            retry = krg["retry"]
        else:
            retry = 10

        m = web(add_ip_flag=add_ip_flag)

        ##指定部分页数 增量更新  cdc_part指定格式为 {'表名':[一个页码列表,第一页为0,列表元素为int]}
        if 'cdc_part' in krg.keys():
            cdc_part_dict = krg["cdc_part"]

            if w[0] in cdc_part_dict.keys():
                arr_nums = [cdc_part_dict[w[0]]]
            else:
                continue

            # 获得self.total
            m.get_total(f2=w[4], url=w[1], ipNum=ipNum, retry=retry)

        ##爬全量
        else:
            page_total = m.get_total(f2=w[4], url=w[1], ipNum=ipNum, retry=retry)

            if page_total == "failed":
                raise ValueError('获取总页数失败')

            arr = range(page_total)

            arr_nums = []

            y = 0
            while y < page_total:
                tmp = list(arr[y:y + interval_page]).copy()
                y += interval_page
                arr_nums.append(tmp)

            print(list(map(lambda x: [x[0] + 1, '...', x[-1] + 1], arr_nums)))

        ### 页数分段爬取
        for nums in arr_nums:
            i = 0
            while i < 2:
                try:
                    print('当前爬取 %s 页' % str(str(nums[0] + 1) + ' ... ' + str(nums[-1] + 1)))
                    m.write_large(total=nums, **setting)
                    print('成功爬取 %s 页' % str(str(nums[0] + 1) + ' ... ' + str(nums[-1] + 1)))
                    break
                except:
                    traceback.print_exc()
                    i += 1
                    print('页数 %s 失败第%s次' % (str(nums), i))


#### 总页数过多 爬取函数
def est_meta_large(conp, data, **arg):
    sql = """select table_name from information_schema.tables where table_schema='%s'""" % (conp[4])
    df = db_query(sql, dbtype="postgresql", conp=conp)
    arr = df["table_name"].values

    if ("gg" in arr) and ('cdc_part' not in arg.keys()):
        est_cdc(conp, data, **arg)
    else:
        get_items_large(conp, data, **arg)
        est_gg(conp, **arg)