package user

import (
	"context"
	"log"

	detaildoEntity "refund/internal/entity/user/detaildo"
	ecommerceEntity "refund/internal/entity/user/ecommerce"
	paymentmethodEntity "refund/internal/entity/user/paymentmethod"
	pelapakEntity "refund/internal/entity/user/pelapak"
	refundEntity "refund/internal/entity/user/refund"
	"refund/pkg/errors"
	"refund/pkg/httpclient"

	"github.com/jmoiron/sqlx"
)

type (
	// Data ...
	Data struct {
		db    *sqlx.DB
		stmt  map[string]*sqlx.Stmt
		httpc *httpclient.Client
	}

	// statement ...
	statement struct {
		key   string
		query string
	}
)

const (
	getAllUsers  = "GetAllUsers"
	qGetAllUsers = "SELECT * FROM user_test"

	getListPelapakByOrderID  = "GetListPelapakByOrderID"
	qgetListPelapakByOrderID = "SELECT * FROM Transaction_pelapak WHERE orderidecommerce like ?"

	getDetailDo  = "GetDetailDo"
	qgetDetailDo = "SELECT Pro_Code,Ordlcl_Price, Pro_Name, TransfH_NoTransf,TransfDB_BatchNumber,TransfDB_Qty AS TransfD_Qty,TransfDB_Qty - TransfDB_Qty_Scan AS FailedQty,OrdLcl_Price * TransfDB_Qty_Scan AS TotalPrice," +
		" (TransfDB_Qty - TransfDB_Qty_Scan) * Ordlcl_Price AS Refund,TransfDB_Qty * Ordlcl_Price AS Grand_Total, TransfDB_Qty_Scan AS TransfD_Qty_Scan" +
		" FROM" +
		" (" +
		" SELECT * FROM `Transaction_pelapak` z" +
		" LEFT JOIN `Pharmanet_Bridge_Order` w" +
		" ON z.OrderID = w.Bridge_OrderIDWeb" +
		" LEFT JOIN `T_Order_Local_Refund` a" +
		" ON a.OrdLcl_Outlet = w.Bridge_Outlet" +
		" AND a.OrdLcl_Num = w.Bridge_OrderIDHO" +
		" LEFT JOIN `OrderSP_ECom_DepoPHN` b" +
		" ON b.EC_NoPO = a.Ordlcl_NoPO AND a.ordlcl_procod = b.EC_Procod" +
		" LEFT JOIN `TransfH_Refund` c" +
		" ON c.TransfH_OutcodeSP = b.EC_OutcodeSP" +
		" AND c.TransfH_NoSP = b.EC_NoSP" +
		" AND c.TransfH_Group = b.EC_Group" +
		" LEFT JOIN `TransfD_Refund` d" +
		" ON d.TransfD_NoTransf = c.TransfH_NoTransf" +
		" AND c.TransfH_OutcodeSP = d.TransfD_OutcodeSP" +
		" AND c.TransfH_NoSP = d.TransfD_NoSP" +
		" AND c.TransfH_Group = d.TransfD_Group" +
		" AND a.ordlcl_procod = d.TransfD_Procod" +
		" LEFT JOIN `TransfD_BatchDetail` e" +
		" ON  d.TransfD_Procod = e.TransfDB_Procod" +
		" AND d.TransfD_NoTransf = e.TransfDB_NoTransf" +
		" LEFT JOIN MH_Product_Refund f" +
		" ON d.TransfD_Procod = f.Pro_code" +
		" WHERE pro_activeyn = 'Y' AND pro_dataaktifyn = 'Y'" +
		" ) tabelJoin" +
		" WHERE TransfH_NoTransf = ?"

	getEcommerceID  = "GetEcommerceID"
	qgetEcommerceID = "SELECT OrderIDEcommerce, OutletName, CustomerName, TransfH_NoTransf, SUM(OrdLcl_Price * TransfD_Qty) AS " +
		" TotalPrice, EkspedisiPrice, OrdLcl_TglOrder, SUM((TransfDB_Qty - TransfDB_Qty_Scan) * Ordlcl_Price) AS TotalRefund" +
		" FROM `Transaction_pelapak` z" +
		" LEFT JOIN `Pharmanet_Bridge_Order` w" +
		" ON z.OrderID = w.Bridge_OrderIDWeb" +
		" LEFT JOIN `T_Order_Local_Refund` a" +
		" ON a.OrdLcl_Outlet = w.Bridge_Outlet" +
		" AND a.OrdLcl_Num = w.Bridge_OrderIDHO" +
		" LEFT JOIN `OrderSP_ECom_DepoPHN` b" +
		" ON b.EC_NoPO = a.Ordlcl_NoPO AND a.ordlcl_procod = b.EC_Procod" +
		" LEFT JOIN m_outlet1 e" +
		" ON e.outletid = a.OrdLcl_Outlet" +
		" LEFT JOIN `TransfH_Refund` c" +
		" ON c.TransfH_OutcodeSP = b.EC_OutcodeSP" +
		" AND c.TransfH_NoSP = b.EC_NoSP" +
		" AND c.TransfH_Group = b.EC_Group" +
		" LEFT JOIN `TransfD_Refund` d" +
		" ON d.TransfD_NoTransf = c.TransfH_NoTransf" +
		" AND c.TransfH_OutcodeSP = d.TransfD_OutcodeSP" +
		" AND c.TransfH_NoSP = d.TransfD_NoSP" +
		" AND c.TransfH_Group = d.TransfD_Group" +
		" AND a.ordlcl_procod = d.TransfD_Procod" +
		" LEFT JOIN TransfD_BatchDetail g" +
		" ON d.TransfD_Procod = g.TransfDB_Procod" +
		" AND d.TransfD_NoTransf = g.TransfDB_NoTransf" +
		" WHERE OrderIDEcommerce LIKE ?" +
		" GROUP BY OrderIDEcommerce"

	insertRefundH  = "InsertRefundH"
	qInsertRefundH = "INSERT INTO refundH (rh_orderIdEcommerce, rh_tglOrder, rh_noDO, rh_totalOrderPrice, rh_ekspedisiPrice, rh_totalRefundPrice, rh_activeYN, rh_updateTime, rh_coin, rh_voucher, rh_paymentMethod) VALUES (?, ?, ?, ?, ?, ?, 'Y', NOW(), ?, ?, ?)"

	insertRefundD  = "InsertRefundD"
	qInsertRefundD = "INSERT INTO refundD (rd_orderIdEcommerce, rd_procod, rd_proname, rd_batch, rd_unitPrice, rd_orderQty, rd_totalOrderPrice, rd_fulfilledYN, rd_successfulQty, rd_successfulPrice, rd_refundQty, rd_refundPrice, rd_keterangan, rd_activeYN, rd_updateTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'Y', NOW())"

	getRefundH  = "GetRefundH"
	qGetRefundH = "SELECT rh_orderIdEcommerce, rh_tglOrder, rh_noDO, rh_totalOrderPrice, rh_ekspedisiPrice, rh_totalRefundPrice FROM test.refundH WHERE rh_orderIdEcommerce like ?"

	updateRefundD   = "UpdateRefundD"
	qUpadateRefundD = "UPDATE refundD set rd_unitPrice = ? , rd_orderQty = ?, rd_totalOrderPrice = ?, rd_successfulQty = ?, rd_successfulPrice = ?, rd_refundQty = ?, rd_refundPrice = ?, rd_keterangan = ?, rd_roundedRefundPrice = ? WHERE rd_orderIdEcommerce like ? "

	getRefundD  = "GetRefundD"
	qGetRefundD = "SELECT rd_orderIdEcommerce, rd_procod, rd_proname, rd_batch, rd_unitPrice, rd_orderQty, rd_totalOrderPrice, rd_fulfilledYN, rd_successfulQty, rd_successfulPrice, rd_refundQty, rd_refundPrice, rd_keterangan, rd_roundedRefundPrice FROM refundD WHERE rd_orderIdEcommerce LIKE ?"

	getPaymentMethod  = "GetPaymentMethod"
	qGetPaymentMethod = "SELECT * FROM test.rh_payment_method WHERE Payment_Method_ID = ?"

	insertPaymentMethod  = "InsertPaymentMethod"
	qInsertPaymentMethod = "INSERT INTO test.rh_payment_method VALUES (NULL, ?)"

	getMaxPaymentID  = "GetMaxPaymentID"
	qGetMaxPaymentID = "SELECT MAX(Payment_Method_ID)+1 AS MaxPaymentID FROM test.rh_payment_method"
)

var (
	readStmt = []statement{
		{getAllUsers, qGetAllUsers},
		{getListPelapakByOrderID, qgetListPelapakByOrderID},
		{getDetailDo, qgetDetailDo},
		{getEcommerceID, qgetEcommerceID},
		{insertRefundH, qInsertRefundH},
		{insertRefundD, qInsertRefundD},
		{getRefundH, qGetRefundH},
		{updateRefundD, qUpadateRefundD},
		{getRefundD, qGetRefundD},
		{getPaymentMethod, qGetPaymentMethod},
		{insertPaymentMethod, qInsertPaymentMethod},
		{getMaxPaymentID, qGetMaxPaymentID},
	}
)

// New ...
func New(db *sqlx.DB, httpc *httpclient.Client) Data {
	d := Data{
		db:    db,
		httpc: httpc,
	}

	d.initStmt()
	return d
}

func (d *Data) initStmt() {
	var (
		err   error
		stmts = make(map[string]*sqlx.Stmt)
	)

	for _, v := range readStmt {
		stmts[v.key], err = d.db.PreparexContext(context.Background(), v.query)
		if err != nil {
			log.Fatalf("[DB] Failed to initialize statement key %v, err : %v", v.key, err)
		}
	}

	d.stmt = stmts
}

// GetAllUsers digunakan untuk mengambil semua data user
func (d Data) GetAllUsers(ctx context.Context) ([]pelapakEntity.Pelapak, error) {
	var (
		user  pelapakEntity.Pelapak
		users []pelapakEntity.Pelapak
		err   error
	)

	// Query ke database
	rows, err := d.stmt[getAllUsers].QueryxContext(ctx)

	// Looping seluruh row data
	for rows.Next() {
		// Insert row data ke struct user
		if err := rows.StructScan(&user); err != nil {
			return users, errors.Wrap(err, "[DATA][GetPelapakOrderedID] ")
		}
		// Tambahkan struct user ke array user
		users = append(users, user)
	}
	// Return users array
	return users, err
}

// GetListPelapak ...
func (d Data) GetListPelapak(ctx context.Context, orderidecommerce string) ([]pelapakEntity.Pelapak, error) {
	var (
		listPelapak []pelapakEntity.Pelapak
		pelapak     pelapakEntity.Pelapak
		err         error
	)
	rows, err := d.stmt[getListPelapakByOrderID].QueryxContext(ctx, orderidecommerce)

	for rows.Next() {
		if err := rows.StructScan(&pelapak); err != nil {
			return listPelapak, errors.Wrap(err, "[DATA][GetOrderIDEcommerce] ")
		}
		listPelapak = append(listPelapak, pelapak)
	}
	defer rows.Close()
	return listPelapak, err
}

// GetDetailDo ...
func (d Data) GetDetailDo(ctx context.Context, TransfH_NoTransf string) (map[string][]detaildoEntity.DetailDo, error) {
	var (
		listDetailDoFullfilled   []detaildoEntity.DetailDo
		listDetailDoUnfullfilled []detaildoEntity.DetailDo
		detaildo                 detaildoEntity.DetailDo
		err                      error
	)
	rows, err := d.stmt[getDetailDo].QueryxContext(ctx, TransfH_NoTransf)
	mapDetailDO := make(map[string][]detaildoEntity.DetailDo)
	for rows.Next() {
		if err := rows.StructScan(&detaildo); err != nil {
			return mapDetailDO, errors.Wrap(err, "[DATA][GetDetailDO] ")
		}
		if detaildo.FailedQty > 0 {
			listDetailDoUnfullfilled = append(listDetailDoUnfullfilled, detaildo)
		} else {
			listDetailDoFullfilled = append(listDetailDoFullfilled, detaildo)
		}
	}

	mapDetailDO["fullfilled"] = listDetailDoFullfilled
	mapDetailDO["unfullfilled"] = listDetailDoUnfullfilled
	defer rows.Close()
	return mapDetailDO, err
}

// GetEcommerceId ...
func (d Data) GetEcommerceId(ctx context.Context, OrderIDEcommerce string, rh_orderIdEcommerce string) ([]ecommerceEntity.Ecommerce, []refundEntity.RefundH, error) {
	var (
		listEcomID  []ecommerceEntity.Ecommerce
		EcomID      ecommerceEntity.Ecommerce
		listRefundH []refundEntity.RefundH
		RefundH     refundEntity.RefundH
		err         error
	)
	rows, err := d.stmt[getEcommerceID].QueryxContext(ctx, OrderIDEcommerce)
	rows2, err := d.stmt[getRefundH].QueryxContext(ctx, rh_orderIdEcommerce)

	for rows.Next() {
		if err := rows.StructScan(&EcomID); err != nil {
			return listEcomID, listRefundH, errors.Wrap(err, "[DATA][GetEcommerceID] ")
		}
		listEcomID = append(listEcomID, EcomID)
	}
	for rows2.Next() {
		if err := rows2.StructScan(&RefundH); err != nil {
			return listEcomID, listRefundH, errors.Wrap(err, "[DATA][GetRefundH] ")
		}
		listRefundH = append(listRefundH, RefundH)
	}
	defer rows.Close()
	return listEcomID, listRefundH, err
}

//InsertRefundH ...
func (d Data) InsertRefundH(ctx context.Context, refund refundEntity.RefundH) error {
	_, err := d.stmt[insertRefundH].ExecContext(ctx,
		refund.Rh_orderIdEcommerce,
		refund.Rh_tglOrder,
		refund.Rh_noDO,
		refund.Rh_totalOrderPrice,
		refund.Rh_ekspedisiPrice,
		//refund.Rh_totalPaidPrice,
		refund.Rh_totalRefundPrice,
		refund.Rh_Coin,
		refund.Rh_Voucher,
		refund.Rh_PaymentMethod)

	return err

}

//InsertRefundD ...
func (d Data) InsertRefundD(ctx context.Context, refund refundEntity.RefundD) error {
	_, err := d.stmt[insertRefundD].ExecContext(ctx,
		refund.Rd_orderIdEcommerce,
		refund.Rd_procod,
		refund.Rd_proname,
		refund.Rd_batch,
		refund.Rd_unitPrice,
		refund.Rd_orderQty,
		refund.Rd_totalOrderPrice,
		refund.Rd_fulfilledYN,
		refund.Rd_successfulQty,
		refund.Rd_successfulPrice,
		refund.Rd_refundQty,
		refund.Rd_refundPrice,
		refund.Rd_keterangan)

	return err
}

//UpdateRefundD ...
func (d Data) UpdateRefundD(ctx context.Context, OrderID string, refundd refundEntity.RefundD) error {
	_, err := d.stmt[updateRefundD].ExecContext(ctx,
		refundd.Rd_unitPrice,
		refundd.Rd_orderQty,
		refundd.Rd_totalOrderPrice,
		refundd.Rd_successfulQty,
		refundd.Rd_successfulPrice,
		refundd.Rd_refundQty,
		refundd.Rd_refundPrice,
		refundd.Rd_keterangan,
		refundd.Rd_roundedRefundPrice,
		OrderID)

	return err
}

//GetRefundD ...
func (d Data) GetRefundD(ctx context.Context, ID string) (map[string][]refundEntity.RefundD, error) {
	var (
		listRefundDFullfilled   []refundEntity.RefundD
		listRefundDUnfullfilled []refundEntity.RefundD
		refund                  refundEntity.RefundD
		err                     error
	)
	rows, err := d.stmt[getRefundD].QueryxContext(ctx, ID)
	mapRefundD := make(map[string][]refundEntity.RefundD)
	for rows.Next() {
		if err := rows.StructScan(&refund); err != nil {
			return mapRefundD, errors.Wrap(err, "[DATA][GetRefundD]")
		}
		if refund.Rd_refundQty > 0 {
			listRefundDUnfullfilled = append(listRefundDUnfullfilled, refund)
		} else {
			listRefundDFullfilled = append(listRefundDFullfilled, refund)
		}
	}

	mapRefundD["fullfilled"] = listRefundDFullfilled
	mapRefundD["unfullfilled"] = listRefundDUnfullfilled
	defer rows.Close()
	return mapRefundD, err
}

//GetPaymentMethod ...
func (d Data) GetPaymentMethod(ctx context.Context, ID string) ([]paymentmethodEntity.PaymentMethod, error) {
	var (
		pay  paymentmethodEntity.PaymentMethod
		pays []paymentmethodEntity.PaymentMethod
		err  error
	)
	rows, err := d.stmt[getPaymentMethod].QueryxContext(ctx, ID)

	for rows.Next() {
		if err := rows.StructScan(&pay); err != nil {
			return pays, errors.Wrap(err, "[DATA][GetPaymentMethod] ")
		}
		pays = append(pays, pay)
	}
	return pays, err
}

//GetMaxPaymentID ...
func (d Data) GetMaxPaymentID(ctx context.Context) (int64, error) {
	var (
		err  error
		temp int64
	)

	if err = d.stmt[getMaxPaymentID].QueryRowxContext(ctx).StructScan(&temp); err != nil {
		return temp, errors.Wrap(err, "[DATA][GetMaxPaymentID] ")
	}
	return temp, err
}

//InsertPaymentMethod ...
func (d Data) InsertPaymentMethod(ctx context.Context, pay paymentmethodEntity.PaymentMethod) error {
	_, err := d.stmt[insertPaymentMethod].ExecContext(ctx,
		pay.Index)

	return err

}