package db

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"

	"github.com/lib/pq"

	"sourcecraft.dev/bigbes/sr-ht-spec/core"
)

// Project is a named set of spaces: one search scope, one MCP view, one
// wikilink namespace.
//
// A project is **pure metadata — a saved filter, not a container**. It owns no
// index and no storage, so this row is a name and project_space is the filter.
// Nothing here is a parent of a space: a space belongs to any number of
// projects, or none, and deleting a project deletes no content.
//
// In particular a project does not scope document IDs. Those are global, and
// deliberately so: projects are edited after merges, so a per-project registry
// could juxtapose two already-merged documents claiming one identity with no
// merge left to reject.
type Project struct {
	ID      int
	Ref     core.ProjectRef
	Created time.Time
}

const projectSelect = `SELECT id, owner, name, created FROM project`

func scanProject(sc rowScanner) (*Project, error) {
	var p Project
	if err := sc.Scan(&p.ID, &p.Ref.Owner, &p.Ref.Name, &p.Created); err != nil {
		return nil, err
	}
	return &p, nil
}

// CreateProject inserts a project row with no member spaces.
//
// The name is validated with core first, which also refuses the reserved
// meta-project name: "~owner/+everything" is an address that resolves to a
// filter excluding nothing, so a row claiming it could only shadow it — and
// keeping it a row would need the sync job the design says the meta-project
// does not have.
//
// A uq_project_owner_name violation maps to ErrProjectExists.
func (s *Store) CreateProject(ctx context.Context, ref core.ProjectRef) (*Project, error) {
	if err := core.ValidateOwner(ref.Owner); err != nil {
		return nil, err
	}
	if err := core.ValidateProjectName(ref.Name); err != nil {
		return nil, err
	}
	const q = `
INSERT INTO project (owner, name, created)
VALUES ($1, $2, $3)
RETURNING id, created`
	var p Project
	p.Ref = ref
	err := s.q.QueryRowContext(ctx, q, ref.Owner, ref.Name, time.Now().UTC()).
		Scan(&p.ID, &p.Created)
	if err != nil {
		var pqErr *pq.Error
		if errors.As(err, &pqErr) && pqErr.Code == "23505" {
			return nil, fmt.Errorf("%w: %s", ErrProjectExists, ref)
		}
		return nil, fmt.Errorf("insert project %s: %w", ref, err)
	}
	return &p, nil
}

// GetProject resolves a project by owner and name. Returns ErrNotFound if no
// such project exists.
func (s *Store) GetProject(ctx context.Context, ref core.ProjectRef) (*Project, error) {
	q := projectSelect + ` WHERE owner = $1 AND name = $2`
	p, err := scanProject(s.q.QueryRowContext(ctx, q, ref.Owner, ref.Name))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get project %s: %w", ref, err)
	}
	return p, nil
}

// ListProjects returns every project, ordered by owner then name.
//
// The meta-project is not among them, and must not be: it has no row. A caller
// rendering a project list adds it as the degenerate filter it is.
func (s *Store) ListProjects(ctx context.Context) ([]*Project, error) {
	q := projectSelect + ` ORDER BY owner, name`
	rows, err := s.q.QueryContext(ctx, q)
	if err != nil {
		return nil, fmt.Errorf("list projects: %w", err)
	}
	defer rows.Close()
	var projects []*Project
	for rows.Next() {
		p, err := scanProject(rows)
		if err != nil {
			return nil, fmt.Errorf("scan project: %w", err)
		}
		projects = append(projects, p)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate projects: %w", err)
	}
	return projects, nil
}

// DeleteProject removes a project and, by cascade, its membership rows. No
// space, document or index entry is touched — a project is a saved filter, so
// deleting one deletes a name and a query, never content.
//
// Returns ErrNotFound if no such project exists.
func (s *Store) DeleteProject(ctx context.Context, projectID int) error {
	res, err := s.q.ExecContext(ctx, `DELETE FROM project WHERE id = $1`, projectID)
	if err != nil {
		return fmt.Errorf("delete project %d: %w", projectID, err)
	}
	return requireOne(res, "delete project")
}

// AddProjectSpace adds a space to a project's filter.
//
// Membership is a set, so adding a space that is already a member succeeds and
// changes nothing: "the space is in the project" is true either way, and there
// is no state for a second copy to occupy. This is not a swallowed conflict —
// the composite primary key is what makes the duplicate unrepresentable, and
// this method says so out loud.
//
// A project_id or space_id with no row behind it violates a foreign key and is
// reported as ErrNotFound: a filter term pointing at nothing would silently
// narrow every query made through it.
func (s *Store) AddProjectSpace(ctx context.Context, projectID, spaceID int) error {
	const q = `
INSERT INTO project_space (project_id, space_id)
VALUES ($1, $2)
ON CONFLICT (project_id, space_id) DO NOTHING`
	if _, err := s.q.ExecContext(ctx, q, projectID, spaceID); err != nil {
		var pqErr *pq.Error
		if errors.As(err, &pqErr) && pqErr.Code == "23503" {
			return fmt.Errorf("%w: project %d or space %d", ErrNotFound, projectID, spaceID)
		}
		return fmt.Errorf("add space %d to project %d: %w", spaceID, projectID, err)
	}
	return nil
}

// RemoveProjectSpace drops a space from a project's filter. The space itself is
// untouched; only the filter term goes.
//
// Returns ErrNotFound when the space was not a member. Unlike adding, removing
// is not idempotent: "remove what is not there" is either a caller working from
// a stale membership list or a concurrent edit, and both are worth hearing
// about, whereas a silent success reports a filter change that did not happen.
func (s *Store) RemoveProjectSpace(ctx context.Context, projectID, spaceID int) error {
	const q = `DELETE FROM project_space WHERE project_id = $1 AND space_id = $2`
	res, err := s.q.ExecContext(ctx, q, projectID, spaceID)
	if err != nil {
		return fmt.Errorf("remove space %d from project %d: %w", spaceID, projectID, err)
	}
	return requireOne(res, "remove project space")
}

// ProjectSpaces resolves a project to its member spaces, ordered by owner then
// name. This is the filter, and it is the whole of what a project *is*: a
// project query is the one global index restricted to these spaces.
//
// The rows are returned rather than bare ids because both forms are needed at
// once — the index filters by space reference, while document_id, proposal and
// index_stamp all key off the id — and one join is cheaper than resolving each
// id afterwards.
//
// An empty result means the project selects no spaces. It emphatically does not
// mean "everything": that would turn an empty saved filter into the whole
// corpus, which is the opposite of what its author asked for. Callers must not
// collapse the two.
func (s *Store) ProjectSpaces(ctx context.Context, projectID int) ([]*Space, error) {
	const q = `
SELECT s.id, s.owner, s.name, s.created
FROM space s
JOIN project_space ps ON ps.space_id = s.id
WHERE ps.project_id = $1
ORDER BY s.owner, s.name`
	rows, err := s.q.QueryContext(ctx, q, projectID)
	if err != nil {
		return nil, fmt.Errorf("list spaces of project %d: %w", projectID, err)
	}
	defer rows.Close()
	var spaces []*Space
	for rows.Next() {
		sp, err := scanSpace(rows)
		if err != nil {
			return nil, fmt.Errorf("scan project space: %w", err)
		}
		spaces = append(spaces, sp)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate project spaces: %w", err)
	}
	return spaces, nil
}

// ProjectsBySpace returns every project a space belongs to, ordered by owner
// then name. A space belongs to any number of them, including none.
//
// This is the direction the write side asks about: after a merge, "which saved
// filters just changed" is this query — though nothing is reindexed per project,
// because there is one index and every project containing the space sees the
// change for free.
func (s *Store) ProjectsBySpace(ctx context.Context, spaceID int) ([]*Project, error) {
	const q = `
SELECT p.id, p.owner, p.name, p.created
FROM project p
JOIN project_space ps ON ps.project_id = p.id
WHERE ps.space_id = $1
ORDER BY p.owner, p.name`
	rows, err := s.q.QueryContext(ctx, q, spaceID)
	if err != nil {
		return nil, fmt.Errorf("list projects of space %d: %w", spaceID, err)
	}
	defer rows.Close()
	var projects []*Project
	for rows.Next() {
		p, err := scanProject(rows)
		if err != nil {
			return nil, fmt.Errorf("scan project: %w", err)
		}
		projects = append(projects, p)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate projects: %w", err)
	}
	return projects, nil
}