#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Gerador do "Plano de Ataque" (PPTX) por quinzena.

Uso: coloque este executavel na MESMA PASTA de:
  - um arquivo .json de cores  (ex.: cores.json)
  - um arquivo .pptx modelo    (com o slide 2 = layout de uma quinzena)
  - um arquivo .xlsx           (planilha de planejamento)

e execute. O programa detecta esses 3 arquivos automaticamente na pasta
e gera "<nome-do-modelo>_ATUALIZADO.pptx" na mesma pasta.

Nao precisa de Python nem de nenhuma instalacao no computador que for
rodar o executavel -- tudo ja vai embutido nele.
"""
import glob
import json
import os
import re
import sys
import copy
import traceback
from datetime import date, timedelta


def base_dir():
    """Pasta onde o executavel (ou este script) esta localizado."""
    if getattr(sys, 'frozen', False):
        return os.path.dirname(os.path.abspath(sys.executable))
    return os.path.dirname(os.path.abspath(__file__))


def pause_on_exit(frozen_only=True):
    if not frozen_only or getattr(sys, 'frozen', False):
        try:
            input('\nPressione ENTER para sair...')
        except EOFError:
            pass


def find_one(patterns, folder, label, exclude_suffix=None):
    candidates = []
    for pat in patterns:
        for f in glob.glob(os.path.join(folder, pat)):
            name = os.path.basename(f)
            if name.startswith('~$'):
                continue
            if exclude_suffix and name.endswith(exclude_suffix):
                continue
            candidates.append(f)
    candidates = sorted(set(candidates))
    if not candidates:
        raise SystemExit(
            f'ERRO: nenhum arquivo {label} encontrado na pasta:\n  {folder}\n'
            f'Coloque um arquivo {label} nessa pasta e execute novamente.')
    if len(candidates) > 1:
        lista = '\n  '.join(candidates)
        raise SystemExit(
            f'ERRO: mais de um arquivo {label} encontrado na pasta:\n  {lista}\n'
            f'Deixe apenas UM arquivo {label} na pasta e execute novamente.')
    return candidates[0]


def main():
    folder = base_dir()
    print(f'Pasta de trabalho: {folder}')

    json_path = find_one(['cores.json', '*.json'], folder, '.json (cores)')
    xlsx_path = find_one(['*.xlsx'], folder, '.xlsx (planilha)')
    pptx_path = find_one(['*.pptx'], folder, '.pptx (modelo)', exclude_suffix='_ATUALIZADO.pptx')

    print(f'  cores  : {os.path.basename(json_path)}')
    print(f'  xlsx   : {os.path.basename(xlsx_path)}')
    print(f'  modelo : {os.path.basename(pptx_path)}')

    with open(json_path, 'r', encoding='utf-8') as f:
        cfg = json.load(f)

    import openpyxl
    from pptx import Presentation
    from pptx.oxml.ns import qn
    from pptx.opc.constants import RELATIONSHIP_TYPE as RT
    from lxml import etree

    out_name = os.path.splitext(os.path.basename(pptx_path))[0] + '_ATUALIZADO.pptx'
    out_path = os.path.join(folder, out_name)

    # -----------------------------------------------------------
    # 1) Coordenadas de pixel dos eixos, medidas nas imagens do modelo
    #    original (colunas 1-26 na "Imagem 6", colunas 26-48 na "Imagem 3").
    #    Isso e' fixo porque descreve a planta em si, nao a cor.
    # -----------------------------------------------------------
    im1_cols_list = [102.1, 152.3, 203.3, 253.2, 303.9, 353.7, 404.8, 455.0, 505.3, 556.4,
                      606.4, 657.1, 707.2, 757.9, 808.1, 858.6, 909.4, 959.4, 1010.6, 1059.7,
                      1110.7, 1160.8, 1211.5, 1262.3, 1312.4, 1363.6]
    im2_cols_list = [83.0, 132.8, 183.7, 234.0, 284.5, 335.4, 385.2, 435.9, 486.0, 536.7,
                      587.0, 637.4, 688.4, 738.3, 789.0, 839.0, 889.8, 939.8, 990.6, 1041.1,
                      1091.4, 1142.1, 1192.1]

    IM1_COLPX = {i + 1: v for i, v in enumerate(im1_cols_list)}
    IM2_COLPX = {26 + i: v for i, v in enumerate(im2_cols_list)}
    IM1_ROWPX = {'D': 88.8, 'C': 178.3, 'B1': 237.7, 'B': 305.3, 'A1': 359.2, 'A': 434.9}
    IM2_ROWPX = {'D': 72.5, 'C': 161.5, 'B1': 221.5, 'B': 288.5, 'A1': 342.5, 'A': 418.5}
    IM1_PXSIZE = (1442, 500)
    IM2_PXSIZE = (1267, 485)

    def which_image(c1, c2):
        return 'im1' if max(c1, c2) <= 26 else 'im2'

    def quad_pixel_rect(c1, c2, r1, r2):
        img = which_image(c1, c2)
        colpx = IM1_COLPX if img == 'im1' else IM2_COLPX
        rowpx = IM1_ROWPX if img == 'im1' else IM2_ROWPX
        x0, x1 = sorted([colpx[c1], colpx[c2]])
        y0, y1 = sorted([rowpx[r1], rowpx[r2]])
        return img, x0, y0, x1, y1

    # -----------------------------------------------------------
    # 2) Transform pixel -> EMU, usando a posicao/tamanho reais das
    #    imagens 'Imagem 6' (im1) e 'Imagem 3' (im2) no slide-modelo.
    #    'Imagem 3' tem um corte (srcRect) aplicado no PowerPoint que
    #    precisa ser descontado, senao o desenho fica deslocado.
    # -----------------------------------------------------------
    IM1_OFF = (16846, 989588)
    IM1_EXT = (8200608, 2841514)
    IM2_OFF = (4073548, 3846576)
    IM2_EXT = (8118452, 3006806)

    IM1_SX = IM1_EXT[0] / IM1_PXSIZE[0]
    IM1_SY = IM1_EXT[1] / IM1_PXSIZE[1]

    IM2_CROP_T = 3246 / 100000
    IM2_CROP_B = 1 / 100000
    IM2_VIS_Y0 = IM2_CROP_T * IM2_PXSIZE[1]
    IM2_VIS_Y1 = (1 - IM2_CROP_B) * IM2_PXSIZE[1]
    IM2_VIS_X0 = 0.0
    IM2_VIS_X1 = IM2_PXSIZE[0]

    IM2_SX = IM2_EXT[0] / (IM2_VIS_X1 - IM2_VIS_X0)
    IM2_SY = IM2_EXT[1] / (IM2_VIS_Y1 - IM2_VIS_Y0)

    def to_emu(img, px, py):
        if img == 'im1':
            return IM1_OFF[0] + px * IM1_SX, IM1_OFF[1] + py * IM1_SY
        return (IM2_OFF[0] + (px - IM2_VIS_X0) * IM2_SX,
                IM2_OFF[1] + (py - IM2_VIS_Y0) * IM2_SY)

    # -----------------------------------------------------------
    # 3) Ler planilha de planejamento
    # -----------------------------------------------------------
    pat = re.compile(r'^(\d+)-(\d+)/([A-Z0-9]+)-([A-Z0-9]+)$')

    def to_date(v):
        if v is None:
            return None
        return v.date() if hasattr(v, 'date') else v

    def parse_quads(path):
        wb = openpyxl.load_workbook(path, data_only=True)
        ws = wb['Planejamento']
        quads = []
        for row in ws.iter_rows(min_row=4, max_row=ws.max_row, values_only=True):
            if row[1] is None:
                continue
            m = pat.match(str(row[1]).strip())
            if not m:
                continue
            c1, c2, r1, r2 = int(m.group(1)), int(m.group(2)), m.group(3), m.group(4)
            quads.append(dict(
                c1=c1, c2=c2, r1=r1, r2=r2,
                geo=(to_date(row[2]), to_date(row[3])),
                inf=(to_date(row[4]), to_date(row[5])),
                sup=(to_date(row[6]), to_date(row[7])),
            ))
        return quads

    QUADS = parse_quads(xlsx_path)
    if not QUADS:
        raise SystemExit('ERRO: nenhum quadrante valido foi lido da planilha (aba "Planejamento").')

    def status(inicio, termino, p_end):
        if inicio is None or inicio > p_end:
            return None
        if termino is not None and termino <= p_end:
            return 'concluido'
        return 'andamento'

    # -----------------------------------------------------------
    # 4) Quinzenas
    # -----------------------------------------------------------
    qz_cfg = cfg.get('quinzena', {})
    PERIOD_DAYS = int(qz_cfg.get('duracao_dias', 14))
    START = date.fromisoformat(qz_cfg.get('data_inicio', '2026-12-14'))
    FIRST_NUM = int(qz_cfg.get('numero_inicial', 2))

    all_dates = []
    for q in QUADS:
        for k in ('geo', 'inf', 'sup'):
            i, f = q[k]
            if i:
                all_dates.append(i)
            if f:
                all_dates.append(f)
    if not all_dates:
        raise SystemExit('ERRO: a planilha nao tem nenhuma data preenchida.')
    DATA_FIM = max(all_dates)

    PERIODS = []
    cur = START
    num = FIRST_NUM
    while cur <= DATA_FIM:
        p_end = cur + timedelta(days=PERIOD_DAYS - 1)
        PERIODS.append((num, cur, p_end))
        cur = p_end + timedelta(days=1)
        num += 1

    def fmt(d):
        return f'{d.day:02d}/{d.month:02d}/{d.year}'

    # -----------------------------------------------------------
    # 5) Cores (lidas do JSON)
    # -----------------------------------------------------------
    JSON_KEY = {'geo': 'geotecnia', 'sup': 'superestrutura', 'inf': 'infraestrutura'}
    COLOR_HEX, BORDER_HEX, ALPHA_DRAWING_ST = {}, {}, {}
    for short, jkey in JSON_KEY.items():
        try:
            section = cfg[jkey]
        except KeyError:
            raise SystemExit(f'ERRO: o JSON de cores nao tem a secao "{jkey}".')
        for st in ('andamento', 'concluido'):
            try:
                entry = section[st]
                COLOR_HEX[(short, st)] = entry['preenchimento'].lstrip('#').upper()
                BORDER_HEX[(short, st)] = entry['borda'].lstrip('#').upper()
                ALPHA_DRAWING_ST[(short, st)] = int(round(float(entry.get('alpha_preenchimento', 100)) * 1000))
            except KeyError as e:
                raise SystemExit(f'ERRO: falta o campo {e} em "{jkey}.{st}" no JSON de cores.')

    ALPHA_LEGEND = 100000
    MARKER_FRAC = float(cfg.get('marcador_fracao', 0.42))

    def _solid_fill_xml(hexcol, alpha):
        return (f'<a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">'
                f'<a:srgbClr val="{hexcol}"><a:alpha val="{alpha}"/></a:srgbClr></a:solidFill>')

    def _fill_xml(key):
        return _solid_fill_xml(COLOR_HEX[key], ALPHA_DRAWING_ST[key])

    def _line_xml(key):
        return (f'<a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" w="9525">'
                f'<a:solidFill><a:srgbClr val="{BORDER_HEX[key]}"/></a:solidFill></a:ln>')

    FILL_XML = {k: _fill_xml(k) for k in COLOR_HEX}
    LINE_XML = {k: _line_xml(k) for k in COLOR_HEX}

    ROUND_ADJ_QUAD = 12000
    ROUND_ADJ_MARK = 30000
    STATUS_RANK = {'andamento': 1, 'concluido': 2}

    _shape_id_counter = [9000]

    def next_id():
        _shape_id_counter[0] += 1
        return _shape_id_counter[0]

    def make_roundrect_xml(name, x_emu, y_emu, w_emu, h_emu, fill_xml, adj, line_xml=None):
        sid = next_id()
        line = line_xml if line_xml else '<a:ln><a:noFill/></a:ln>'
        return f'''<p:sp xmlns:p="http://schemas.openxmlformats.org/presentationml/2006/main" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
  <p:nvSpPr>
    <p:cNvPr id="{sid}" name="{name} {sid}"/>
    <p:cNvSpPr/>
    <p:nvPr/>
  </p:nvSpPr>
  <p:spPr>
    <a:xfrm><a:off x="{int(x_emu)}" y="{int(y_emu)}"/><a:ext cx="{int(w_emu)}" cy="{int(h_emu)}"/></a:xfrm>
    <a:prstGeom prst="roundRect"><a:avLst><a:gd name="adj" fmla="val {adj}"/></a:avLst></a:prstGeom>
    {fill_xml}
    {line}
  </p:spPr>
  <p:txBody>
    <a:bodyPr/><a:lstStyle/>
    <a:p><a:endParaRPr lang="pt-BR"/></a:p>
  </p:txBody>
</p:sp>'''

    IM1_COL_STEP = (max(im1_cols_list) - min(im1_cols_list)) / (len(im1_cols_list) - 1)
    IM2_COL_STEP = (max(im2_cols_list) - min(im2_cols_list)) / (len(im2_cols_list) - 1)
    COL_STEP_PX = {'im1': IM1_COL_STEP, 'im2': IM2_COL_STEP}

    def add_quadrant_overlays(slide, p_end):
        spTree = slide.shapes._spTree

        for q in QUADS:
            st = status(*q['geo'], p_end)
            if st is None:
                continue
            img, x0, y0, x1, y1 = quad_pixel_rect(q['c1'], q['c2'], q['r1'], q['r2'])
            ex0, ey0 = to_emu(img, x0, y0)
            ex1, ey1 = to_emu(img, x1, y1)
            xml = make_roundrect_xml('Estaqueamento', ex0, ey0, ex1 - ex0, ey1 - ey0,
                                      FILL_XML[('geo', st)], ROUND_ADJ_QUAD, LINE_XML[('geo', st)])
            spTree.append(etree.fromstring(xml))

        for key in ('inf', 'sup'):
            best = {}
            for q in QUADS:
                st = status(*q[key], p_end)
                if st is None:
                    continue
                img = which_image(q['c1'], q['c2'])
                for col in (q['c1'], q['c2']):
                    for row in (q['r1'], q['r2']):
                        pt = (img, col, row)
                        cur_st = best.get(pt)
                        if cur_st is None or STATUS_RANK[st] > STATUS_RANK[cur_st]:
                            best[pt] = st

            name = 'Superestrutura' if key == 'sup' else 'Infraestrutura'
            for (img, col, row), st in best.items():
                colpx = IM1_COLPX if img == 'im1' else IM2_COLPX
                rowpx = IM1_ROWPX if img == 'im1' else IM2_ROWPX
                px, py = colpx[col], rowpx[row]
                ex, ey = to_emu(img, px, py)
                step = COL_STEP_PX[img]
                sx = IM1_SX if img == 'im1' else IM2_SX
                sy = IM1_SY if img == 'im1' else IM2_SY
                mw, mh = step * MARKER_FRAC * sx, step * MARKER_FRAC * sy
                xml = make_roundrect_xml(name, ex - mw / 2, ey - mh / 2, mw, mh,
                                          FILL_XML[(key, st)], ROUND_ADJ_MARK, LINE_XML[(key, st)])
                spTree.append(etree.fromstring(xml))

    # -----------------------------------------------------------
    # 6) Duplicar slide (clona shapes + relacionamentos de imagem)
    # -----------------------------------------------------------
    def duplicate_slide(prs, source_index):
        source = prs.slides[source_index]
        dest = prs.slides.add_slide(source.slide_layout)
        for shp in list(dest.shapes):
            shp._element.getparent().remove(shp._element)

        rid_map = {}
        for rId, rel in source.part.rels.items():
            if rel.reltype == RT.SLIDE_LAYOUT:
                continue
            if rel.is_external:
                continue
            new_rId = dest.part.relate_to(rel.target_part, rel.reltype)
            rid_map[rId] = new_rId

        r_embed = qn('r:embed')
        r_link = qn('r:link')
        for shp in source.shapes:
            new_el = copy.deepcopy(shp._element)
            for el in new_el.iter():
                old = el.get(r_embed)
                if old is not None and old in rid_map:
                    el.set(r_embed, rid_map[old])
                old = el.get(r_link)
                if old is not None and old in rid_map:
                    el.set(r_link, rid_map[old])
            dest.shapes._spTree.append(new_el)
        return dest

    def set_quinzena_title(slide, num, p_start, p_end):
        for sh in slide.shapes:
            if sh.name == 'CaixaDeTexto 5':
                runs = sh.text_frame.paragraphs[0].runs
                runs[-1].text = f'Quinzena {num:02d} ({fmt(p_start)} a {fmt(p_end)})'
                return
        raise RuntimeError('titulo (CaixaDeTexto 5) nao encontrado no slide-modelo')

    LEGEND_Y_TO_KEY = {
        4200000: ('geo', 'andamento'),
        4597600: ('geo', 'concluido'),
        4993578: ('sup', 'andamento'),
        5381498: ('sup', 'concluido'),
        5802852: ('inf', 'andamento'),
        6190772: ('inf', 'concluido'),
    }

    def update_legend_colors(slide):
        for sh in slide.shapes:
            if sh.shape_type != 6:  # GROUP
                continue
            for sub in sh.shapes:
                if not sub.name.startswith('Rounded'):
                    continue
                key = None
                for y, k in LEGEND_Y_TO_KEY.items():
                    if abs(sub.top - y) < 5000:
                        key = k
                        break
                if key is None:
                    continue
                sp = sub._element.spPr
                for el in sp.findall(qn('a:solidFill')):
                    sp.remove(el)
                fill_el = etree.fromstring(_solid_fill_xml(COLOR_HEX[key], ALPHA_LEGEND))
                ln = sp.find(qn('a:ln'))
                sp.insert(list(sp).index(ln), fill_el)
                for c in ln.findall(qn('a:solidFill')):
                    ln.remove(c)
                ln_fill = etree.fromstring(
                    f'<a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">'
                    f'<a:srgbClr val="{BORDER_HEX[key]}"/></a:solidFill>')
                ln.append(ln_fill)

    # -----------------------------------------------------------
    # 7) Montagem final
    # -----------------------------------------------------------
    prs = Presentation(pptx_path)
    if len(prs.slides) < 3:
        raise SystemExit('ERRO: o modelo .pptx precisa ter pelo menos 3 slides '
                          '(capa, slide de quinzena, encerramento).')

    template_idx = 1
    update_legend_colors(prs.slides[template_idx])

    targets = [prs.slides[template_idx]]
    for _ in range(len(PERIODS) - 1):
        targets.append(duplicate_slide(prs, template_idx))

    for slide, (num, p_start, p_end) in zip(targets, PERIODS):
        set_quinzena_title(slide, num, p_start, p_end)
        add_quadrant_overlays(slide, p_end)

    sldIdLst = prs.slides._sldIdLst
    ids = list(sldIdLst)
    thankyou_el = ids[2]
    sldIdLst.remove(thankyou_el)
    sldIdLst.append(thankyou_el)

    prs.save(out_path)
    print(f'\nOK! Gerado: {out_path}')
    print(f'Total de slides: {len(prs.slides._sldIdLst)}  |  Quinzenas: {len(PERIODS)}')
    print(f'Primeira quinzena: {PERIODS[0][0]:02d} ({fmt(PERIODS[0][1])} a {fmt(PERIODS[0][2])})')
    print(f'Ultima quinzena:   {PERIODS[-1][0]:02d} ({fmt(PERIODS[-1][1])} a {fmt(PERIODS[-1][2])})')


if __name__ == '__main__':
    try:
        main()
    except SystemExit as e:
        print(str(e))
        pause_on_exit()
        sys.exit(1)
    except Exception:
        print('ERRO INESPERADO:')
        traceback.print_exc()
        pause_on_exit()
        sys.exit(1)
    else:
        pause_on_exit()