package inventory

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/TaruLab/keluargamart-backend/internal/auth"
	"github.com/TaruLab/keluargamart-backend/internal/db"
	"github.com/TaruLab/keluargamart-backend/pkg/seq"
	"github.com/jackc/pgx/v5"
	"github.com/shopspring/decimal"
)

func transferActor(ctx context.Context) (int64, error) {
	u := auth.UserFrom(ctx)
	if u == nil || u.ID <= 0 || !auth.HasStoreScope(ctx) {
		return 0, ErrForbiddenWarehouse
	}
	return u.ID, nil
}

func validateTransfer(in TransferInput) error {
	if in.FromWarehouseID <= 0 || in.ToWarehouseID <= 0 || in.FromWarehouseID == in.ToWarehouseID {
		return errors.New("distinct source and destination warehouses are required")
	}
	if _, err := time.Parse("2006-01-02", in.TransferDate); err != nil {
		return errors.New("valid transfer date is required")
	}
	if len(in.Items) == 0 {
		return ErrItemRequired
	}
	seen := map[int64]bool{}
	for _, it := range in.Items {
		if it.ProductID <= 0 || !it.QuantityRequested.IsPositive() || !it.QuantityRequested.Equal(it.QuantityRequested.Round(4)) || seen[it.ProductID] {
			return errors.New("items require unique active products and positive quantities with at most four decimals")
		}
		seen[it.ProductID] = true
	}
	return nil
}

func (s *Service) transferAudit(ctx context.Context, q db.DBTX, id, actor int64, key, action, reason string, payload any) error {
	raw, err := json.Marshal(payload)
	if err != nil {
		return err
	}
	_, err = q.Exec(ctx, `INSERT INTO stock_transfer_events(stock_transfer_id,actor_id,request_key,action,reason,payload) VALUES($1,$2,$3,$4,$5,$6)`, id, actor, key, action, reason, raw)
	return err
}

func (s *Service) validateTransferReferences(ctx context.Context, q db.DBTX, in TransferInput) error {
	var count int
	if err := q.QueryRow(ctx, `SELECT count(*) FROM warehouses WHERE id=ANY($1::bigint[]) AND is_active`, []int64{in.FromWarehouseID, in.ToWarehouseID}).Scan(&count); err != nil {
		return err
	}
	if count != 2 {
		return errors.New("warehouses must be active")
	}
	for _, it := range in.Items {
		var ok bool
		if err := q.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM products WHERE id=$1 AND is_active AND deleted_at IS NULL)`, it.ProductID).Scan(&ok); err != nil {
			return err
		}
		if !ok {
			return errors.New("product is not active")
		}
	}
	return nil
}

func (s *Service) TransferCreate(ctx context.Context, in TransferInput) (*Transfer, error) {
	if err := validateTransfer(in); err != nil {
		return nil, err
	}
	actor, err := transferActor(ctx)
	if err != nil {
		return nil, err
	}
	if err = s.guardWarehouses(ctx, in.FromWarehouseID); err != nil {
		return nil, err
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	if err = s.validateTransferReferences(ctx, tx, in); err != nil {
		return nil, err
	}
	now := time.Now().In(s.loc)
	n, err := s.seq.Sequence(ctx, "trf", seq.MonthStart(now), tx)
	if err != nil {
		return nil, err
	}
	var id int64
	err = tx.QueryRow(ctx, `INSERT INTO stock_transfers(transfer_number,from_warehouse_id,to_warehouse_id,transfer_date,status,notes,created_by) VALUES($1,$2,$3,$4,'draft',$5,$6) RETURNING id`, fmt.Sprintf("TRF-%s-%04d", now.Format("20060102"), n), in.FromWarehouseID, in.ToWarehouseID, in.TransferDate, in.Notes, actor).Scan(&id)
	if err != nil {
		return nil, err
	}
	for _, it := range in.Items {
		if _, err = tx.Exec(ctx, `INSERT INTO stock_transfer_items(stock_transfer_id,product_id,quantity_requested) VALUES($1,$2,$3)`, id, it.ProductID, it.QuantityRequested); err != nil {
			return nil, err
		}
	}
	if err = s.transferAudit(ctx, tx, id, actor, "create", "create", "", in); err != nil {
		return nil, err
	}
	if err = tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.TransferByID(ctx, id)
}

func (s *Service) transferLoad(ctx context.Context, q db.DBTX, id int64, lock bool) (*Transfer, error) {
	t := &Transfer{}
	sql := `SELECT t.id,t.transfer_number,t.from_warehouse_id,t.to_warehouse_id,t.transfer_date::text,t.status,t.notes,t.created_by,t.approved_by,t.created_at,t.voucher_number,t.dispatched_at,t.voucher_snapshot,t.lifecycle_version,f.name,d.name FROM stock_transfers t JOIN warehouses f ON f.id=t.from_warehouse_id JOIN warehouses d ON d.id=t.to_warehouse_id WHERE t.id=$1`
	if lock {
		sql += ` FOR UPDATE OF t`
	}
	err := q.QueryRow(ctx, sql, id).Scan(&t.ID, &t.TransferNumber, &t.FromWarehouseID, &t.ToWarehouseID, &t.TransferDate, &t.Status, &t.Notes, &t.CreatedBy, &t.ApprovedBy, &t.CreatedAt, &t.VoucherNumber, &t.DispatchedAt, &t.VoucherSnapshot, &t.LifecycleVersion, &t.FromWarehouseName, &t.ToWarehouseName)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	rows, err := q.Query(ctx, `SELECT i.id,i.stock_transfer_id,i.product_id,i.quantity_requested,i.quantity_sent,i.quantity_received,i.quantity_transit,i.quantity_damaged,i.quantity_lost,p.code,p.name,COALESCE(u.symbol,'') FROM stock_transfer_items i JOIN products p ON p.id=i.product_id LEFT JOIN units u ON u.id=p.unit_id WHERE i.stock_transfer_id=$1 ORDER BY i.product_id,i.id`, id)
	if err != nil {
		return nil, err
	}
	t.Items = []TransferItem{}
	for rows.Next() {
		var it TransferItem
		if err = rows.Scan(&it.ID, &it.StockTransferID, &it.ProductID, &it.QuantityRequested, &it.QuantitySent, &it.QuantityReceived, &it.QuantityTransit, &it.QuantityDamaged, &it.QuantityLost, &it.ProductCode, &it.ProductName, &it.UnitSymbol); err != nil {
			rows.Close()
			return nil, err
		}
		t.Items = append(t.Items, it)
	}
	err = rows.Err()
	rows.Close()
	if err != nil {
		return nil, err
	}
	rows, err = q.Query(ctx, `SELECT id,action,actor_id,occurred_at,reason,payload FROM stock_transfer_events WHERE stock_transfer_id=$1 ORDER BY id`, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	t.Events = []TransferEvent{}
	for rows.Next() {
		var e TransferEvent
		if err = rows.Scan(&e.ID, &e.Action, &e.ActorID, &e.OccurredAt, &e.Reason, &e.Payload); err != nil {
			return nil, err
		}
		t.Events = append(t.Events, e)
	}
	return t, rows.Err()
}

func (s *Service) TransferByID(ctx context.Context, id int64) (*Transfer, error) {
	if _, err := transferActor(ctx); err != nil {
		return nil, err
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	t, err := s.transferLoad(ctx, tx, id, false)
	if err != nil {
		return nil, err
	}
	if err = s.guardTransferWarehouses(ctx, t.FromWarehouseID, t.ToWarehouseID); err != nil {
		return nil, err
	}
	return t, nil
}

func (s *Service) ListTransfers(ctx context.Context, f TransferFilter) ([]Transfer, int, error) {
	if _, err := transferActor(ctx); err != nil {
		return nil, 0, err
	}
	if f.Page < 1 {
		f.Page = 1
	}
	if f.PerPage < 1 || f.PerPage > 100 {
		f.PerPage = 20
	}
	whs, scoped, err := s.outletWarehouses(ctx)
	if err != nil {
		return nil, 0, err
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, 0, err
	}
	defer tx.Rollback(ctx)
	where := ` WHERE ($1='' OR t.status=$1) AND ($2 OR t.from_warehouse_id=ANY($3::bigint[]) OR t.to_warehouse_id=ANY($3::bigint[]))`
	var total int
	if err = tx.QueryRow(ctx, `SELECT count(*) FROM stock_transfers t`+where, f.Status, !scoped, whs).Scan(&total); err != nil {
		return nil, 0, err
	}

	orderCol := "t.id"
	orderDir := "DESC"
	if strings.ToLower(f.SortDir) == "asc" {
		orderDir = "ASC"
	}
	switch strings.ToLower(f.SortBy) {
	case "transfer_number":
		orderCol = "t.transfer_number"
	case "transfer_date":
		orderCol = "t.transfer_date"
	case "status":
		orderCol = "t.status"
	case "from_warehouse_name":
		orderCol = "f.name"
	case "to_warehouse_name":
		orderCol = "d.name"
	default:
		orderCol = "t.id"
		if f.SortDir == "" {
			orderDir = "DESC"
		}
	}
	orderClause := fmt.Sprintf(" ORDER BY %s %s, t.id DESC", orderCol, orderDir)
	query := `SELECT t.id FROM stock_transfers t JOIN warehouses f ON f.id=t.from_warehouse_id JOIN warehouses d ON d.id=t.to_warehouse_id` + where + orderClause + ` LIMIT $4 OFFSET $5`

	rows, err := tx.Query(ctx, query, f.Status, !scoped, whs, f.PerPage, (f.Page-1)*f.PerPage)
	if err != nil {
		return nil, 0, err
	}
	ids := []int64{}
	for rows.Next() {
		var id int64
		if err = rows.Scan(&id); err != nil {
			rows.Close()
			return nil, 0, err
		}
		ids = append(ids, id)
	}
	err = rows.Err()
	rows.Close()
	if err != nil {
		return nil, 0, err
	}
	out := []Transfer{}
	for _, id := range ids {
		t, e := s.transferLoad(ctx, tx, id, false)
		if e != nil {
			return nil, 0, e
		}
		t.ItemCount = int64(len(t.Items))
		t.Items = nil
		t.Events = nil
		out = append(out, *t)
	}
	return out, total, nil
}

func (s *Service) TransferEdit(ctx context.Context, id int64, in TransferInput, cancel bool) (*Transfer, error) {
	actor, err := transferActor(ctx)
	if err != nil {
		return nil, err
	}
	if !cancel {
		if err = validateTransfer(in); err != nil {
			return nil, err
		}
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	t, err := s.transferLoad(ctx, tx, id, true)
	if err != nil {
		return nil, err
	}
	if err = s.guardWarehouses(ctx, t.FromWarehouseID); err != nil {
		return nil, err
	}
	if cancel && t.Status == "cancelled" {
		return t, nil
	}
	if t.Status != "draft" && t.Status != "approved" {
		return nil, ErrUnsupportedState
	}
	action := "edit"
	if cancel {
		action = "cancel"
		_, err = tx.Exec(ctx, `UPDATE stock_transfers SET status='cancelled',updated_at=now() WHERE id=$1`, id)
	} else {
		if err = s.guardWarehouses(ctx, in.FromWarehouseID); err != nil {
			return nil, err
		}
		if err = s.validateTransferReferences(ctx, tx, in); err != nil {
			return nil, err
		}
		_, err = tx.Exec(ctx, `UPDATE stock_transfers SET from_warehouse_id=$2,to_warehouse_id=$3,transfer_date=$4,notes=$5,updated_at=now(),lifecycle_version=1 WHERE id=$1`, id, in.FromWarehouseID, in.ToWarehouseID, in.TransferDate, in.Notes)
		if err == nil {
			_, err = tx.Exec(ctx, `DELETE FROM stock_transfer_items WHERE stock_transfer_id=$1`, id)
		}
		if err == nil {
			for _, it := range in.Items {
				_, err = tx.Exec(ctx, `INSERT INTO stock_transfer_items(stock_transfer_id,product_id,quantity_requested) VALUES($1,$2,$3)`, id, it.ProductID, it.QuantityRequested)
				if err != nil {
					break
				}
			}
		}
	}
	if err != nil {
		return nil, err
	}
	key := fmt.Sprintf("%s-%d", action, time.Now().UnixNano())
	if err = s.transferAudit(ctx, tx, id, actor, key, action, "", in); err != nil {
		return nil, err
	}
	if err = tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.TransferByID(ctx, id)
}

func (s *Service) TransferApprove(ctx context.Context, id, operatorID int64) (*Transfer, error) {
	actor, err := transferActor(ctx)
	if err != nil {
		return nil, err
	}
	if operatorID != actor {
		return nil, ErrForbiddenWarehouse
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	t, err := s.transferLoad(ctx, tx, id, true)
	if err != nil {
		return nil, err
	}
	if err = s.guardWarehouses(ctx, t.FromWarehouseID); err != nil {
		return nil, err
	}
	if t.VoucherNumber != nil {
		return t, nil
	}
	if t.Status != "draft" && t.Status != "approved" {
		return nil, ErrUnsupportedState
	}
	if len(t.Items) == 0 {
		return nil, ErrItemRequired
	}
	draft := TransferInput{FromWarehouseID: t.FromWarehouseID, ToWarehouseID: t.ToWarehouseID, TransferDate: t.TransferDate}
	for _, it := range t.Items {
		draft.Items = append(draft.Items, TransferItemInput{ProductID: it.ProductID, QuantityRequested: it.QuantityRequested})
	}
	if err = validateTransfer(draft); err != nil {
		return nil, err
	}
	if err = s.validateTransferReferences(ctx, tx, draft); err != nil {
		return nil, err
	}
	// Source rows only, sorted by product by transferLoad. Dispatch never touches destination stock.
	for _, it := range t.Items {
		if !it.QuantityRequested.IsPositive() {
			return nil, ErrItemRequired
		}
		stock, e := s.store.LockStockRow(ctx, tx, it.ProductID, t.FromWarehouseID)
		if e != nil {
			if errors.Is(e, ErrNotFound) {
				return nil, ErrInsufficient
			}
			return nil, e
		}
		if stock.Quantity.Sub(stock.ReservedQuantity).LessThan(it.QuantityRequested) {
			return nil, ErrInsufficient
		}
	}
	ref := "stock_transfer"
	note := "Dispatch " + t.TransferNumber
	for i, it := range t.Items {
		if _, err = s.adjustStockOn(ctx, tx, it.ProductID, t.FromWarehouseID, it.QuantityRequested.Neg(), MovementTransferOut, &ref, &id, &note); err != nil {
			return nil, err
		}
		if _, err = tx.Exec(ctx, `UPDATE stock_transfer_items SET quantity_sent=quantity_requested,quantity_transit=quantity_requested WHERE id=$1`, it.ID); err != nil {
			return nil, err
		}
		t.Items[i].QuantitySent = it.QuantityRequested
		t.Items[i].QuantityTransit = it.QuantityRequested
	}
	now := time.Now().UTC()
	voucher := "VCH-" + t.TransferNumber
	t.VoucherNumber = &voucher
	t.DispatchedAt = &now
	t.ApprovedBy = &actor
	t.Status = "in_transit"
	var company json.RawMessage
	err = tx.QueryRow(ctx, `SELECT value->'store' FROM app_settings WHERE key='system'`).Scan(&company)
	if errors.Is(err, pgx.ErrNoRows) || err != nil || len(company) == 0 || string(company) == "null" {
		company = json.RawMessage(`{"name":"KeluargaMart","parentLine":"PT Sinar Retailindo Utama","address":"Jl. Merdeka No. 10, Medan","phone":"061-881234"}`)
	}
	var identity struct {
		Name string `json:"name"`
	}
	if json.Unmarshal(company, &identity) != nil || strings.TrimSpace(identity.Name) == "" {
		company = json.RawMessage(`{"name":"KeluargaMart","parentLine":"PT Sinar Retailindo Utama","address":"Jl. Merdeka No. 10, Medan","phone":"061-881234"}`)
	}
	snapshot, err := json.Marshal(struct {
		Company  json.RawMessage `json:"company"`
		Transfer *Transfer       `json:"transfer"`
	}{company, t})
	if err != nil {
		return nil, err
	}
	if _, err = tx.Exec(ctx, `UPDATE stock_transfers SET status='in_transit',approved_by=$2,voucher_number=$3,dispatched_at=$4,voucher_snapshot=$5,lifecycle_version=1,updated_at=now() WHERE id=$1`, id, actor, voucher, now, snapshot); err != nil {
		return nil, err
	}
	if err = s.transferAudit(ctx, tx, id, actor, "dispatch", "dispatch", "", t.Items); err != nil {
		return nil, err
	}
	if err = tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.TransferByID(ctx, id)
}

func (s *Service) TransferReceive(ctx context.Context, id int64, in TransferReceiptInput, reconcile bool) (*Transfer, error) {
	actor, err := transferActor(ctx)
	if err != nil {
		return nil, err
	}
	if strings.TrimSpace(in.RequestKey) == "" || len(in.RequestKey) > 128 || len(in.Items) == 0 {
		return nil, errors.New("request_key and items are required")
	}
	tx, err := s.store.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	t, err := s.transferLoad(ctx, tx, id, true)
	if err != nil {
		return nil, err
	}
	u := auth.UserFrom(ctx)
	isSuperadmin := u != nil && (u.Role == auth.RoleAdmin || u.Role == auth.RoleOwner)
	if !isSuperadmin {
		whs, scoped, err := s.outletWarehouses(ctx)
		if err != nil {
			return nil, err
		}
		if u != nil && u.WarehouseID != nil && *u.WarehouseID > 0 {
			if *u.WarehouseID != t.ToWarehouseID {
				return nil, ErrOnlyDestinationWarehouse
			}
		} else if scoped {
			if !containsInt64(whs, t.ToWarehouseID) {
				return nil, ErrOnlyDestinationWarehouse
			}
		} else {
			return nil, ErrOnlyDestinationWarehouse
		}
	}
	if err = s.guardWarehouses(ctx, t.ToWarehouseID); err != nil {
		return nil, err
	}
	action := "receipt"
	if reconcile {
		action = "reconcile"
		u := auth.UserFrom(ctx)
		if u.Role != "admin" && u.Role != "owner" {
			return nil, ErrForbiddenWarehouse
		}
	}
	raw, err := json.Marshal(in)
	if err != nil {
		return nil, err
	}
	var oldAction string
	var oldPayload []byte
	err = tx.QueryRow(ctx, `SELECT action,payload FROM stock_transfer_events WHERE stock_transfer_id=$1 AND request_key=$2`, id, in.RequestKey).Scan(&oldAction, &oldPayload)
	if err == nil {
		var old TransferReceiptInput
		if json.Unmarshal(oldPayload, &old) != nil || oldAction != action {
			return nil, ErrUnsupportedState
		}
		a, _ := json.Marshal(old)
		if string(a) != string(raw) {
			return nil, errors.New("request key was already used for different receipt")
		}
		return t, nil
	}
	if !errors.Is(err, pgx.ErrNoRows) {
		return nil, err
	}
	if t.LifecycleVersion != 1 || (t.Status != "in_transit" && t.Status != "partial" && t.Status != "discrepancy") {
		return nil, ErrUnsupportedState
	}
	byID := map[int64]TransferReceiptItem{}
	discrepancy := false
	for _, it := range in.Items {
		if _, ok := byID[it.ItemID]; ok {
			return nil, errors.New("duplicate receipt item")
		}
		if it.Good.IsNegative() || it.Damaged.IsNegative() || it.Missing.IsNegative() || !it.Good.Equal(it.Good.Round(4)) || !it.Damaged.Equal(it.Damaged.Round(4)) || !it.Missing.Equal(it.Missing.Round(4)) {
			return nil, errors.New("receipt quantities must be nonnegative with at most four decimals")
		}
		if !it.Good.Add(it.Damaged).Add(it.Missing).IsPositive() {
			return nil, errors.New("receipt quantities must be positive")
		}
		if reconcile && (!it.Good.IsZero() || !it.Damaged.IsZero() || !it.Missing.IsPositive()) {
			return nil, errors.New("reconciliation must explicitly record missing quantity as loss")
		}
		byID[it.ItemID] = it
	}
	outstanding := decimal.Zero
	anyReceived := false
	matched := 0
	for _, it := range t.Items {
		r, ok := byID[it.ID]
		if !ok {
			outstanding = outstanding.Add(it.QuantityTransit)
			if it.QuantityReceived.IsPositive() {
				anyReceived = true
			}
			continue
		}
		matched++
		physical := r.Good.Add(r.Damaged)
		if physical.Add(r.Missing).GreaterThan(it.QuantityTransit) {
			return nil, errors.New("receipt exceeds outstanding transit quantity")
		}
		if r.Damaged.IsPositive() || r.Missing.IsPositive() {
			discrepancy = true
		}
		remaining := it.QuantityTransit.Sub(physical)
		if reconcile {
			remaining = remaining.Sub(r.Missing)
		}
		outstanding = outstanding.Add(remaining)
		if r.Good.IsPositive() || it.QuantityReceived.IsPositive() {
			anyReceived = true
		}
	}
	if matched != len(byID) {
		return nil, errors.New("item does not belong to this transfer")
	}
	if (discrepancy || reconcile || outstanding.IsPositive()) && strings.TrimSpace(in.Reason) == "" {
		return nil, errors.New("discrepancy reason is required")
	}
	// Destination locks follow product order. Damaged units are quarantined on
	// the transfer, never added to operational stock; missing units remain transit
	// until an authorized loss reconciliation explicitly clears them.
	for _, it := range t.Items {
		r, ok := byID[it.ID]
		if !ok || !r.Good.IsPositive() {
			continue
		}
		if err = s.store.EnsureStockRow(ctx, tx, it.ProductID, t.ToWarehouseID); err != nil {
			return nil, err
		}
		if _, err = s.store.LockStockRow(ctx, tx, it.ProductID, t.ToWarehouseID); err != nil {
			return nil, err
		}
	}
	ref := "stock_transfer"
	note := action + " " + t.TransferNumber + " " + in.RequestKey
	for _, it := range t.Items {
		r, ok := byID[it.ID]
		if !ok {
			continue
		}
		if r.Good.IsPositive() {
			if _, err = s.adjustStockOn(ctx, tx, it.ProductID, t.ToWarehouseID, r.Good, MovementTransferIn, &ref, &id, &note); err != nil {
				return nil, err
			}
		}
		loss := decimal.Zero
		if reconcile {
			loss = r.Missing
		}
		_, err = tx.Exec(ctx, `UPDATE stock_transfer_items SET quantity_received=quantity_received+$2,quantity_damaged=quantity_damaged+$3,quantity_lost=quantity_lost+$4,quantity_transit=quantity_transit-$2-$3-$4 WHERE id=$1`, it.ID, r.Good, r.Damaged, loss)
		if err != nil {
			return nil, err
		}
	}
	status := "in_transit"
	if anyReceived {
		status = "partial"
	}
	if discrepancy || t.Status == "discrepancy" {
		status = "discrepancy"
	}
	if outstanding.IsZero() {
		status = "completed"
	}
	if _, err = tx.Exec(ctx, `UPDATE stock_transfers SET status=$2,updated_at=now() WHERE id=$1`, id, status); err != nil {
		return nil, err
	}
	if err = s.transferAudit(ctx, tx, id, actor, in.RequestKey, action, in.Reason, in); err != nil {
		return nil, err
	}
	if err = tx.Commit(ctx); err != nil {
		return nil, err
	}
	return s.TransferByID(ctx, id)
}