package sales

import (
	"context"
	"testing"

	"github.com/jackc/pgx/v5/pgtype"

	"github.com/TaruLab/keluargamart-backend/internal/db"
)

// sales_name must resolve through employees, never through users.
//
// The two id spaces overlap by accident, which is what made this invisible:
// an order's sales_id can be a valid id in BOTH `users` and `employees`,
// and the old read path took COALESCE(users.name, employees.name). Because
// users came first, any order whose employees id also existed in users was
// attributed to the WRONG PERSON — 91 orders on the live database were in
// that state, and a user who created an order as user 5 (Staf Gudang, whose
// employees row is id 4 = Joko Prasetyo) showed "Kasir Toko", which is who
// users id 4 happens to be.
//
// These tests execute the real generated queries against PostgreSQL rather
// than a fake store, because a fake would not interpret the SQL and would
// therefore assert nothing about which name the JOIN produces. The fixture
// collides on id deliberately, so the wrong answer is reachable.

// salesNameDDL mirrors the columns the read queries select, plus the
// id-overlap fixture that reproduces the bug.
var salesNameDDL = []string{
	`CREATE TABLE users (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL)`,
	`CREATE TABLE employees (
		id BIGSERIAL PRIMARY KEY,
		employee_number TEXT NOT NULL,
		user_id BIGINT REFERENCES users(id),
		name TEXT NOT NULL
	)`,
	`CREATE TABLE customers (id BIGSERIAL PRIMARY KEY, code TEXT NOT NULL, name TEXT NOT NULL)`,
	`CREATE TABLE warehouses (id BIGSERIAL PRIMARY KEY, code TEXT NOT NULL, name TEXT NOT NULL)`,
	`CREATE TABLE cashier_shifts (id BIGSERIAL PRIMARY KEY, cashier_id BIGINT REFERENCES users(id))`,
	`CREATE TABLE sales_orders (
		id BIGSERIAL PRIMARY KEY,
		so_number TEXT NOT NULL,
		customer_id BIGINT NOT NULL REFERENCES customers(id),
		warehouse_id BIGINT REFERENCES warehouses(id),
		-- The real schema's FK: employees, not users (000001_init_schema.sql:235).
		sales_id BIGINT REFERENCES employees(id),
		store_id BIGINT,
		order_date DATE NOT NULL,
		delivery_date DATE,
		status TEXT NOT NULL DEFAULT 'draft',
		order_type TEXT NOT NULL DEFAULT 'regular',
		source_type TEXT,
		subtotal NUMERIC(15,2) NOT NULL DEFAULT 0,
		tax_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
		discount_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
		total_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
		paid_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
		remaining_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
		shipping_cost NUMERIC(15,2) NOT NULL DEFAULT 0,
		shipping_address TEXT,
		notes TEXT,
		-- Selected by the read queries: payment_terms on every order, shift_id
		-- only by the POS list (cashier_name falls back to the field rep).
		payment_terms INT NOT NULL DEFAULT 0,
		shift_id BIGINT REFERENCES cashier_shifts(id),
		created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
		updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
	)`,
	`INSERT INTO users (id, name, email) VALUES
		(1, 'Staf Gudang', 'gudang@x.id'),
		(4, 'Kasir Toko', 'kasir@x.id'),
		(2, 'Sales Representative', 'sales@x.id')`,
	`INSERT INTO employees (id, employee_number, user_id, name) VALUES
		-- employees id 4 belongs to user 1. users id 4 is a DIFFERENT person.
		(4, 'EMP-004', 1, 'Joko Prasetyo'),
		-- employees id 2 has no user account at all.
		(2, 'EMP-002', NULL, 'Siti Aminah')`,
	`INSERT INTO customers (id, code, name) VALUES (1, 'CUS-000', 'Pelanggan Umum')`,
	`INSERT INTO warehouses (id, code, name) VALUES (1, 'WH1', 'Gudang Pusat Medan')`,
	// ListSalesOrders subselects line_count and item_qty off it, so it must
	// exist before the query runs (and after sales_orders, which it references).
	`CREATE TABLE sales_order_items (
		id BIGSERIAL PRIMARY KEY,
		sales_order_id BIGINT NOT NULL REFERENCES sales_orders(id),
		product_id BIGINT NOT NULL,
		quantity NUMERIC(15,3) NOT NULL DEFAULT 0
	)`,
	// The collision case: this order's salesperson is employees id 4.
	`INSERT INTO sales_orders (so_number, customer_id, warehouse_id, sales_id, order_date, total_amount, remaining_amount)
	 VALUES ('SO-TEST-1', 1, 1, 4, '2026-08-11', 1000, 1000)`,
}

func listArgs(search string) db.ListSalesOrdersParams {
	var s pgtype.Text
	if search != "" {
		s = pgtype.Text{String: search, Valid: true}
	}
	return db.ListSalesOrdersParams{Limit: 100, Offset: 0, Search: s}
}

func countArgs(search string) db.CountSalesOrdersParams {
	var s pgtype.Text
	if search != "" {
		s = pgtype.Text{String: search, Valid: true}
	}
	return db.CountSalesOrdersParams{Search: s}
}

func TestGetSalesOrderByID_ResolvesSalesNameFromEmployeesNotUsers(t *testing.T) {
	repo, _ := scratchRepo(t, salesNameDDL)

	got, err := repo.q.GetSalesOrderByID(context.Background(), 1)
	if err != nil {
		t.Fatal(err)
	}
	// employees.id 4 is Joko Prasetyo. "Kasir Toko" is users.id 4, a
	// different person, and is what the old users-first COALESCE returned.
	if got.SalesName.String != "Joko Prasetyo" {
		t.Errorf("sales_name = %q, want %q (sales_id resolves through employees, not users)",
			got.SalesName.String, "Joko Prasetyo")
	}
}

func TestListSalesOrders_ResolvesSalesNameFromEmployeesNotUsers(t *testing.T) {
	repo, _ := scratchRepo(t, salesNameDDL)

	rows, err := repo.q.ListSalesOrders(context.Background(), listArgs(""))
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 1 {
		t.Fatalf("rows = %d, want 1", len(rows))
	}
	if rows[0].SalesName.String != "Joko Prasetyo" {
		t.Errorf("sales_name = %q, want %q", rows[0].SalesName.String, "Joko Prasetyo")
	}
}

// An employees row with no user account must still be found, and the list and
// count must agree — they are the same predicate, so a mismatch here means
// pagination silently drops rows.
func TestListSalesOrders_EmployeeWithoutUserAccount_AndCountAgrees(t *testing.T) {
	repo, _ := scratchRepo(t, append(append([]string{}, salesNameDDL...),
		`INSERT INTO sales_orders (so_number, customer_id, warehouse_id, sales_id, order_date, total_amount, remaining_amount)
		 VALUES ('SO-TEST-2', 1, 1, 2, '2026-08-12', 2000, 2000)`))

	ctx := context.Background()
	rows, err := repo.q.ListSalesOrders(ctx, listArgs(""))
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 2 {
		t.Fatalf("rows = %d, want 2", len(rows))
	}
	total, err := repo.q.CountSalesOrders(ctx, countArgs(""))
	if err != nil {
		t.Fatal(err)
	}
	if int(total) != len(rows) {
		t.Errorf("count = %d, list = %d — they must agree or pagination drops rows", total, len(rows))
	}
}

// Searching by the EMPLOYEE's name must find the order; searching by the
// unrelated users row that shares the id must not.
func TestListSalesOrders_SearchResolvesThroughEmployeesOnly(t *testing.T) {
	repo, _ := scratchRepo(t, salesNameDDL)
	ctx := context.Background()

	rows, err := repo.q.ListSalesOrders(ctx, listArgs("Joko Prasetyo"))
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 1 {
		t.Errorf("search %q: rows = %d, want 1", "Joko Prasetyo", len(rows))
	}

	rows, err = repo.q.ListSalesOrders(ctx, listArgs("Kasir Toko"))
	if err != nil {
		t.Fatal(err)
	}
	if len(rows) != 0 {
		t.Errorf("search %q: rows = %d, want 0 — a users row must not attribute an order", "Kasir Toko", len(rows))
	}
}

// An order with no salesperson at all (the new-sales-account case) must read
// as empty, not borrow a name from an unrelated row.
func TestGetSalesOrderByID_NullSalesIDReadsAsEmptyNotSomebodyElse(t *testing.T) {
	repo, _ := scratchRepo(t, append(append([]string{}, salesNameDDL...),
		`INSERT INTO sales_orders (so_number, customer_id, warehouse_id, sales_id, order_date, total_amount, remaining_amount)
		 VALUES ('SO-TEST-3', 1, 1, NULL, '2026-08-13', 3000, 3000)`))

	got, err := repo.q.GetSalesOrderByID(context.Background(), 2)
	if err != nil {
		t.Fatal(err)
	}
	if got.SalesName.String != "" {
		t.Errorf("sales_name = %q, want empty for an order with no salesperson", got.SalesName.String)
	}
}