package store

import (
	"context"

	"atlas9.dev/c/core"
	"atlas9.dev/c/core/dbi"
	"atlas9.dev/c/demo/lib/access"
	"atlas9.dev/c/demo/todos"
)

type SqliteTodoStore struct {
	db    dbi.DBI
	guard access.Guard
}

var _ todos.Store = (*SqliteTodoStore)(nil)

func NewSqliteTodoStore(db dbi.DBI, guard access.Guard) *SqliteTodoStore {
	return &SqliteTodoStore{db: db, guard: guard}
}

func (s *SqliteTodoStore) CreateList(ctx context.Context, list *todos.List) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_CreateList, list.Tenant, ""); err != nil {
		return err
	}

	_, err := s.db.Exec(ctx, `
		INSERT INTO todo_lists (id, tenant, name) VALUES ($1, $2, $3)
	`, list.ID, list.Tenant, list.Name)
	return err
}

func (s *SqliteTodoStore) UpdateList(ctx context.Context, list *todos.List) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_UpdateList, list.Tenant, ""); err != nil {
		return err
	}

	res, err := s.db.Exec(ctx, `
		UPDATE todo_lists SET name = $1 WHERE id = $2 AND tenant = $3
	`, list.Name, list.ID, list.Tenant)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return core.ErrNotFound
	}
	return nil
}

func (s *SqliteTodoStore) GetList(ctx context.Context, tenant core.ID, id core.ID, out *todos.List) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_ReadList, tenant, ""); err != nil {
		return err
	}

	return dbi.Get(ctx, s.db, out, `
		SELECT id, tenant, name FROM todo_lists WHERE id = $1 AND tenant = $2
	`, id, tenant)
}

func (s *SqliteTodoStore) ListLists(ctx context.Context, tenant core.ID, page core.PageReq, out *core.Page[todos.List]) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_ReadList, tenant, ""); err != nil {
		return err
	}

	return dbi.Paginate(ctx, s.db, page, out,
		func(l todos.List) string { return l.ID.String() },
		`SELECT id, tenant, name FROM todo_lists
		 WHERE tenant = $1 AND id > $cursor ORDER BY id LIMIT $limit`,
		tenant)
}

func (s *SqliteTodoStore) DeleteList(ctx context.Context, tenant core.ID, id core.ID) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_DeleteList, tenant, ""); err != nil {
		return err
	}

	_, err := s.db.Exec(ctx, `DELETE FROM todo_lists WHERE id = $1 AND tenant = $2`, id, tenant)
	return err
}

func (s *SqliteTodoStore) AddToList(ctx context.Context, tenant core.ID, list core.ID, item core.ID) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_AddToList, tenant, ""); err != nil {
		return err
	}

	// The foreign keys only enforce that the list and item exist somewhere;
	// they must also belong to the caller's tenant.
	var listOK, itemOK bool
	err := s.db.QueryRow(ctx, `
		SELECT
			EXISTS (SELECT 1 FROM todo_lists WHERE id = $1 AND tenant = $2),
			EXISTS (SELECT 1 FROM todo_items WHERE id = $3 AND tenant = $2)
	`, list, tenant, item).Scan(&listOK, &itemOK)
	if err != nil {
		return err
	}
	if !listOK || !itemOK {
		return core.ErrNotFound
	}

	_, err = s.db.Exec(ctx, `
		INSERT INTO todo_list_items (tenant, list, item) VALUES ($1, $2, $3)
		ON CONFLICT (list, item) DO NOTHING
	`, tenant, list, item)
	return err
}

func (s *SqliteTodoStore) RemoveFromList(ctx context.Context, tenant core.ID, list core.ID, item core.ID) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_RemoveFromList, tenant, ""); err != nil {
		return err
	}

	_, err := s.db.Exec(ctx, `
		DELETE FROM todo_list_items WHERE tenant = $1 AND list = $2 AND item = $3
	`, tenant, list, item)
	return err
}

func (s *SqliteTodoStore) CreateItem(ctx context.Context, item *todos.Item) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_CreateItem, item.Tenant, ""); err != nil {
		return err
	}

	if err := s.checkAssignee(ctx, item.Tenant, item.Assignee); err != nil {
		return err
	}

	_, err := s.db.Exec(ctx, `
		INSERT INTO todo_items (id, tenant, title, notes, status, assignee)
		VALUES ($1, $2, $3, $4, $5, $6)
	`, item.ID, item.Tenant, item.Title, item.Notes, item.Status, item.Assignee)
	return err
}

func (s *SqliteTodoStore) UpdateItem(ctx context.Context, item *todos.Item) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_UpdateItem, item.Tenant, ""); err != nil {
		return err
	}

	if err := s.checkAssignee(ctx, item.Tenant, item.Assignee); err != nil {
		return err
	}

	res, err := s.db.Exec(ctx, `
		UPDATE todo_items SET title = $1, notes = $2, status = $3, assignee = $4
		WHERE id = $5 AND tenant = $6
	`, item.Title, item.Notes, item.Status, item.Assignee, item.ID, item.Tenant)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return core.ErrNotFound
	}
	return nil
}

func (s *SqliteTodoStore) GetItem(ctx context.Context, tenant core.ID, id core.ID, out *todos.Item) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_ReadItem, tenant, ""); err != nil {
		return err
	}

	return dbi.Get(ctx, s.db, out, `
		SELECT id, tenant, title, notes, status, assignee
		FROM todo_items WHERE id = $1 AND tenant = $2
	`, id, tenant)
}

func (s *SqliteTodoStore) ListItems(ctx context.Context, tenant core.ID, list core.ID, page core.PageReq, out *core.Page[todos.Item]) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_ReadItem, tenant, ""); err != nil {
		return err
	}

	cursorFn := func(i todos.Item) string { return i.ID.String() }

	if list.IsEmpty() {
		return dbi.Paginate(ctx, s.db, page, out, cursorFn,
			`SELECT id, tenant, title, notes, status, assignee FROM todo_items
			 WHERE tenant = $1 AND id > $cursor ORDER BY id LIMIT $limit`,
			tenant)
	}

	return dbi.Paginate(ctx, s.db, page, out, cursorFn,
		`SELECT i.id, i.tenant, i.title, i.notes, i.status, i.assignee
		 FROM todo_items i
		 JOIN todo_list_items li ON li.item = i.id
		 WHERE i.tenant = $1 AND li.list = $2 AND i.id > $cursor
		 ORDER BY i.id LIMIT $limit`,
		tenant, list)
}

func (s *SqliteTodoStore) DeleteItem(ctx context.Context, tenant core.ID, id core.ID) error {

	if err := s.guard.Check(ctx, todos.Cap_Todos_DeleteItem, tenant, ""); err != nil {
		return err
	}

	_, err := s.db.Exec(ctx, `DELETE FROM todo_items WHERE id = $1 AND tenant = $2`, id, tenant)
	return err
}

// checkAssignee verifies that a non-empty assignee is a member of the tenant.
func (s *SqliteTodoStore) checkAssignee(ctx context.Context, tenant core.ID, assignee core.ID) error {

	if assignee.IsEmpty() {
		return nil
	}

	var ok bool
	err := s.db.QueryRow(ctx, `
		SELECT EXISTS (SELECT 1 FROM grants WHERE tenant = $1 AND type = 'user' AND principal = $2 AND role IN ('owner', 'member'))
	`, tenant, assignee).Scan(&ok)
	if err != nil {
		return err
	}
	if !ok {
		return todos.ErrAssigneeNotMember
	}
	return nil
}