from datetime import timedelta

from opensipkd.base.models import CommonModel
from opensipkd.pbb.models import (PbbBase, PBB_ARGS, PbbDBSession, Sppt)
from sqlalchemy import (Column, String, DateTime, Integer, BigInteger, func, and_, not_, union)

class SpptJurnal(PbbBase, CommonModel):
    __tablename__ = 'sppt_jurnal'
    id = Column(BigInteger, primary_key=True)
    kd_propinsi = Column(String(2))
    kd_dati2 = Column(String(2), )
    kd_kecamatan = Column(String(3), )
    kd_kelurahan = Column(String(3), )
    kd_blok = Column(String(3))
    no_urut = Column(String(4), )
    kd_jns_op = Column(String(1), )
    thn_pajak_sppt = Column(String(4), )
    nop = Column(String(18))
    ref = Column(Integer)  # Diisi siklus atau pembayaran_ke
    src = Column(String(4))  # Source Data SPPT / Pembayaran / STP
    pokok = Column(BigInteger)
    denda = Column(BigInteger)
    tgl_transaksi = Column(DateTime)  # Diisi tgl_terbit SPPT dan atau tgl_pembayaran SPPT
    tgl_jurnal = Column(DateTime)  # diisi tgl_rekam_bayar_sppt
    # Jika Sumber SPPT
    # Data Awal diisi dengan tgl_cetak SPPT (Select Insert)
    # Data Selanjutnya dengan sysdate (Trigger)
    create_date = Column(DateTime)  # default sysdate
    tgl_jatuh_tempo_sppt = Column(DateTime)
    kd_kanwil = Column(String(2))
    kd_kantor = Column(String(2))
    kd_tp = Column(String(2))

    __table_args__ = (PBB_ARGS,)

    @classmethod
    def query(cls, thn_pajak=None):
        qry = PbbDBSession.query(cls)
        if thn_pajak:
            qry = qry.filter(cls.thn_pajak_sppt == thn_pajak)
        return qry

    @classmethod
    def qry_cutoff(cls, tgl_akhir, thn_pajak=None):
        """
        Digunakan untuk menjumlahkan data sampai dengan tanggal tertentu
        @param tgl_akhir:
        @param thn_pajak:
        @return:
        """
        tgl_akhir += timedelta(days=1)
        return cls.query(thn_pajak).filter(cls.tgl_jurnal < tgl_akhir)

    @classmethod
    def qry_cutoff_awal(cls, tgl_awal, thn_pajak=None):
        """
        Digunakan untuk menjumlahkan data sampai dengan tanggal tertentu
        @param tgl_awal:
        @param thn_pajak:
        @return:
        """
        return cls.query(thn_pajak).filter(cls.tgl_jurnal < tgl_awal)

    @classmethod
    def qry_periode(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        qry = cls.qry_cutoff(tgl_akhir, thn_pajak)
        if tgl_awal:
            qry = qry.filter(cls.tgl_jurnal >= tgl_awal)
        return qry

    @classmethod
    def qry_awal(cls, tgl_awal, thn_pajak=None):
        return cls.qry_cutoff_awal(tgl_awal, thn_pajak)

    @classmethod
    def qry_tetap(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        return cls.qry_periode(tgl_awal, tgl_akhir, thn_pajak).filter(cls.src == 'S')

    @classmethod
    def qry_bayar(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        return cls.qry_periode(tgl_awal, tgl_akhir, thn_pajak).filter(cls.src == 'P')

    @classmethod
    def qry_akhir(cls, tgl_akhir, thn_pajak=None):
        return cls.qry_cutoff(tgl_akhir, thn_pajak)

    @classmethod
    def qry_lb(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        """
        Digunakan untuk menampilkan data objek pajak yang lebih bayar pada periode tertentu
        @param tgl_awal:
        @param tgl_akhir:
        @param thn_pajak:
        @return: nop, thn_pajak_sppt, pokok
        @rtype: Query object
        """

        sub_q = cls.qry_periode(tgl_awal, tgl_akhir, thn_pajak).subquery()
        qry = PbbDBSession.query(sub_q.c.id, sub_q.c.kd_propinsi, sub_q.c.kd_dati2, sub_q.c.kd_kecamatan,
                sub_q.c.kd_kelurahan, sub_q.c.kd_blok, sub_q.c.no_urut, sub_q.c.kd_jns_op, sub_q.c.thn_pajak_sppt,
                sub_q.c.nop, sub_q.c.ref, sub_q.c.src, func.sum(sub_q.c.pokok).label('pokok'), sub_q.c.denda, 
                sub_q.c.tgl_transaksi, sub_q.c.tgl_jurnal, sub_q.c.create_date, sub_q.c.tgl_jatuh_tempo_sppt, 
                sub_q.c.kd_kanwil, sub_q.c.kd_kantor, sub_q.c.kd_tp, ). \
            group_by(sub_q.c.nop, sub_q.c.thn_pajak_sppt, sub_q.c.id,
                sub_q.c.kd_propinsi, sub_q.c.kd_dati2, sub_q.c.kd_kecamatan, sub_q.c.kd_kelurahan, sub_q.c.kd_blok,
                sub_q.c.no_urut, sub_q.c.kd_jns_op, sub_q.c.ref, sub_q.c.src, sub_q.c.denda, sub_q.c.tgl_transaksi,
                sub_q.c.tgl_jurnal, sub_q.c.create_date, sub_q.c.tgl_jatuh_tempo_sppt, sub_q.c.kd_kanwil, 
                sub_q.c.kd_kantor, sub_q.c.kd_tp).having(func.sum(sub_q.c.pokok) < 0)
        return qry

    @classmethod
    def qry_saldo(cls, tgl_awal, tgl_akhir, thn_pajak=None, lb=None):
        """
        Digunakan untuk menghitung posisi tagihan pada periode tertentu per nop
        @param tgl_awal: Date
        @param tgl_akhir: Date
        @param thn_pajak: String
        @param lb: Boolean
        @return: Query: nop, thn_pajak_sppt, pokok
        """
        sub_q = cls.qry_periode(tgl_awal, tgl_akhir, thn_pajak).subquery()
        qry = PbbDBSession.query(sub_q.c.id, sub_q.c.kd_propinsi, sub_q.c.kd_dati2, sub_q.c.kd_kecamatan,
                sub_q.c.kd_kelurahan, sub_q.c.kd_blok, sub_q.c.no_urut, sub_q.c.kd_jns_op, sub_q.c.thn_pajak_sppt,
                sub_q.c.nop, sub_q.c.ref, sub_q.c.src, func.sum(sub_q.c.pokok).label('pokok'), sub_q.c.denda, 
                sub_q.c.tgl_transaksi, sub_q.c.tgl_jurnal, sub_q.c.create_date, sub_q.c.tgl_jatuh_tempo_sppt, 
                sub_q.c.kd_kanwil, sub_q.c.kd_kantor, sub_q.c.kd_tp, ). \
            group_by(sub_q.c.nop, sub_q.c.thn_pajak_sppt, sub_q.c.id,
                sub_q.c.kd_propinsi, sub_q.c.kd_dati2, sub_q.c.kd_kecamatan, sub_q.c.kd_kelurahan, sub_q.c.kd_blok,
                sub_q.c.no_urut, sub_q.c.kd_jns_op, sub_q.c.ref, sub_q.c.src, sub_q.c.denda, sub_q.c.tgl_transaksi,
                sub_q.c.tgl_jurnal, sub_q.c.create_date, sub_q.c.tgl_jatuh_tempo_sppt, sub_q.c.kd_kanwil, 
                sub_q.c.kd_kantor, sub_q.c.kd_tp)
        if lb:
            return qry.having(func.sum(sub_q.c.pokok) < 0)  # Lebih Bayar
        else:
            return qry.having(func.sum(sub_q.c.pokok) > 0)  # Belum/Kurang Bayar

    @classmethod
    def get_saldo_awal(cls, tgl_akhir, thn_pajak=None):
        subq = cls.qry_saldo(None, tgl_akhir, thn_pajak).subquery()
        return PbbDBSession.query(func.sum(subq.c.pokok)).scalar() or 0
        
    @classmethod
    def get_saldo_awal2(cls, tgl_awal, thn_pajak=None):
        subq = cls.qry_awal(tgl_awal, thn_pajak).subquery()
        amount = PbbDBSession.query(subq.c.nop,subq.c.thn_pajak_sppt,
                                    func.sum(subq.c.pokok).label('pokok')).\
            group_by(subq.c.nop,subq.c.thn_pajak_sppt).\
            having(func.sum(subq.c.pokok) > 0).subquery()            
        return PbbDBSession.query(func.sum(amount.c.pokok)).scalar() or 0
        
    @classmethod
    def get_saldo_lb_awal(cls, tgl_awal, thn_pajak=None):
        subq = cls.qry_awal(tgl_awal, thn_pajak).subquery()
        amount = PbbDBSession.query(subq.c.nop,subq.c.thn_pajak_sppt,
                                    func.sum(subq.c.pokok).label('pokok')).\
            group_by(subq.c.nop,subq.c.thn_pajak_sppt).\
            having(func.sum(subq.c.pokok) < 0).subquery()            
        return PbbDBSession.query(func.sum(amount.c.pokok)).scalar() or 0

    @classmethod
    def get_saldo_lb(cls, tgl_akhir, thn_pajak=None):
        subq = cls.qry_saldo(None, tgl_akhir, lb=1).subquery()
        amount = PbbDBSession.query(func.sum(subq.c.pokok)).scalar() or 0
        return amount

    @classmethod
    def get_invoice(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        subq = cls.qry_tetap(tgl_awal, tgl_akhir, thn_pajak).subquery()
        amount = PbbDBSession.query(func.sum(subq.c.pokok)).scalar() or 0
        return amount

    @classmethod
    def get_payment(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        subq = cls.qry_bayar(tgl_awal, tgl_akhir, thn_pajak).subquery()
        amount = PbbDBSession.query(func.sum(subq.c.pokok)*-1).scalar() or 0
        return amount

    @classmethod
    def get_lb(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        subq = cls.qry_lb(tgl_awal, tgl_akhir, thn_pajak).subquery()
        amount = PbbDBSession.query(func.sum(subq.c.pokok)).scalar() or 0
        return amount
        
    @classmethod
    def get_saldo_akhir(cls, tgl_akhir, thn_pajak=None):
        subq = cls.qry_akhir(tgl_akhir, thn_pajak).subquery()
        amount = PbbDBSession.query(subq.c.nop,subq.c.thn_pajak_sppt,
                                    func.sum(subq.c.pokok).label('pokok')).\
            group_by(subq.c.nop,subq.c.thn_pajak_sppt).\
            having(func.sum(subq.c.pokok) > 0).subquery()            
        return PbbDBSession.query(func.sum(amount.c.pokok)).scalar() or 0
        
    @classmethod
    def get_saldo_lb_akhir(cls, tgl_akhir, thn_pajak=None):
        subq = cls.qry_akhir(tgl_akhir, thn_pajak).subquery()
        amount = PbbDBSession.query(subq.c.nop,subq.c.thn_pajak_sppt,
                                    func.sum(subq.c.pokok).label('pokok')).\
            group_by(subq.c.nop,subq.c.thn_pajak_sppt).\
            having(func.sum(subq.c.pokok) < 0).subquery()            
        return PbbDBSession.query(func.sum(amount.c.pokok)).scalar() or 0

    @classmethod
    def get_per_tahun(cls, tgl_awal, tgl_akhir, thn_pajak=None):
        # SALDO AWAL
        sw_subq = cls.qry_awal(tgl_awal, thn_pajak).subquery()
        sw_subq2 = PbbDBSession.query(sw_subq.c.nop,sw_subq.c.thn_pajak_sppt,
                                      func.sum(sw_subq.c.pokok).label('pokok')).\
            group_by(sw_subq.c.nop,sw_subq.c.thn_pajak_sppt).\
            having(func.sum(sw_subq.c.pokok) > 0).subquery()  
        sw_subq3 = PbbDBSession.query(sw_subq2.c.thn_pajak_sppt,
                                      func.sum(sw_subq2.c.pokok).label('saldo_awal'),0,0,0,0).\
            group_by(sw_subq2.c.thn_pajak_sppt).subquery()
        
        # KETETAPAN / PERUBAHAN
        k_subq = cls.qry_tetap(tgl_awal, tgl_akhir, thn_pajak).subquery()
        k_subq2 = PbbDBSession.query(k_subq.c.nop,k_subq.c.thn_pajak_sppt,
                                     func.sum(k_subq.c.pokok).label('pokok')).\
            group_by(k_subq.c.nop,k_subq.c.thn_pajak_sppt).subquery()  
        k_subq3 = PbbDBSession.query(k_subq2.c.thn_pajak_sppt,
                                     0,func.sum(k_subq2.c.pokok).label('ketetapan'),0,0,0).\
            group_by(k_subq2.c.thn_pajak_sppt).subquery()
        
        # REALISASI
        r_subq = cls.qry_bayar(tgl_awal, tgl_akhir, thn_pajak).subquery()
        r_subq2 = PbbDBSession.query(r_subq.c.nop,r_subq.c.thn_pajak_sppt,
                                     (func.sum(r_subq.c.pokok)*-1).label('pokok')).\
            group_by(r_subq.c.nop,r_subq.c.thn_pajak_sppt).subquery()  
        r_subq3 = PbbDBSession.query(r_subq2.c.thn_pajak_sppt,
                                     0,0,func.sum(r_subq2.c.pokok).label('realisasi'),0,0).\
            group_by(r_subq2.c.thn_pajak_sppt).subquery()
        
        # SALDO LB (AKHIR - AWAL)
        # SALDO LB AWAL    
        sw_lb_subq2 = PbbDBSession.query(sw_subq.c.nop,sw_subq.c.thn_pajak_sppt,
                                         func.sum(sw_subq.c.pokok).label('pokok')).\
            group_by(sw_subq.c.nop,sw_subq.c.thn_pajak_sppt).\
            having(func.sum(sw_subq.c.pokok) < 0).subquery()
        # SALDO LB AKHIR
        sk_subq = cls.qry_akhir(tgl_akhir, thn_pajak).subquery()
        sk_lb_subq2 = PbbDBSession.query(sk_subq.c.nop,sk_subq.c.thn_pajak_sppt,
                                         func.sum(sk_subq.c.pokok).label('pokok')).\
            outerjoin(sw_lb_subq2,not_(and_(sw_lb_subq2.c.nop == sk_subq.c.nop,
                                       sw_lb_subq2.c.thn_pajak_sppt == sk_subq.c.thn_pajak_sppt))).\
            group_by(sk_subq.c.nop,sk_subq.c.thn_pajak_sppt).\
            having(func.sum(sk_subq.c.pokok) < 0).subquery()  
        sk_lb_subq3 = PbbDBSession.query(sk_lb_subq2.c.thn_pajak_sppt,
                                         0,0,0,func.sum(sk_lb_subq2.c.pokok).label('lebih_bayar'),0).\
            group_by(sk_lb_subq2.c.thn_pajak_sppt).subquery()
            
        # SALDO AKHIR
        sk_subq2 = PbbDBSession.query(sk_subq.c.nop,sk_subq.c.thn_pajak_sppt,
                                      func.sum(sk_subq.c.pokok).label('pokok')).\
            group_by(sk_subq.c.nop,sk_subq.c.thn_pajak_sppt).\
            having(func.sum(sk_subq.c.pokok) > 0).subquery()  
        sk_subq3 = PbbDBSession.query(sk_subq2.c.thn_pajak_sppt,
                                      0,0,0,0,func.sum(sk_subq2.c.pokok).label('saldo_akhir')).\
            group_by(sk_subq2.c.thn_pajak_sppt).subquery()
            
        subq = union(sw_subq3,k_subq3,r_subq3,sk_lb_subq3,sk_subq3).subquery()
        qry = PbbDBSession.query(subq.c.thn_pajak_sppt.label('tahun'),
                                 func.sum(subq.c.saldo_awal).label('saldo_awal'),
                                 func.sum(subq.c.ketetapan).label('ketetapan'),
                                 func.sum(subq.c.realisasi).label('realisasi'),
                                 func.sum(subq.c.lebih_bayar).label('lebih_bayar'),
                                 func.sum(subq.c.saldo_akhir).label('saldo_akhir')).\
            group_by(subq.c.thn_pajak_sppt).\
            order_by(subq.c.thn_pajak_sppt)
        return qry