package storage

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

	_ "github.com/mattn/go-sqlite3"

	"larc.wejust.rest/larc/internal/core"
)

/* SQLite storage for larc metadata.
 * Handles revisions, branches, trees, and state.
 * All operations use single connection with WAL mode for speed. */

var (
	ErrRevisionNotFound = errors.New("revision not found")
	ErrBranchNotFound   = errors.New("branch not found")
	ErrTreeNotFound     = errors.New("tree not found")
)

const schema = `
CREATE TABLE IF NOT EXISTS revisions (
    number INTEGER PRIMARY KEY,
    timestamp INTEGER NOT NULL,
    author TEXT NOT NULL,
    message TEXT NOT NULL,
    branch TEXT NOT NULL,
    parent INTEGER,
    merge_parent INTEGER,
    tree_hash TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS branches (
    name TEXT PRIMARY KEY,
    head_rev INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    created_from INTEGER
);

CREATE TABLE IF NOT EXISTS trees (
    hash TEXT PRIMARY KEY,
    data BLOB NOT NULL
);

CREATE TABLE IF NOT EXISTS state (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_revisions_branch ON revisions(branch, number DESC);
CREATE INDEX IF NOT EXISTS idx_revisions_timestamp ON revisions(timestamp);
`

// MetaStore manages SQLite metadata storage
type MetaStore struct {
	db *sql.DB
}

// NewMetaStore opens or creates a metadata store
func NewMetaStore(path string) (*MetaStore, error) {
	db, err := sql.Open("sqlite3", path+"?_journal_mode=WAL&_synchronous=NORMAL&_busy_timeout=5000")
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}

	/* single connection for consistency */
	db.SetMaxOpenConns(1)

	if _, err := db.Exec(schema); err != nil {
		db.Close()
		return nil, fmt.Errorf("init schema: %w", err)
	}

	return &MetaStore{db: db}, nil
}

// Close closes the database connection
func (m *MetaStore) Close() error {
	return m.db.Close()
}

// NextRevision returns the next revision number and increments counter
func (m *MetaStore) NextRevision() (int64, error) {
	tx, err := m.db.Begin()
	if err != nil {
		return 0, fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback()

	var next int64
	row := tx.QueryRow("SELECT COALESCE(MAX(number), 0) + 1 FROM revisions")
	if err := row.Scan(&next); err != nil {
		return 0, fmt.Errorf("get max revision: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return 0, fmt.Errorf("commit: %w", err)
	}

	return next, nil
}

// CreateRevision creates a new revision
func (m *MetaStore) CreateRevision(rev *core.Revision) error {
	_, err := m.db.Exec(`
		INSERT INTO revisions (number, timestamp, author, message, branch, parent, merge_parent, tree_hash)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)
	`, rev.Number, rev.Timestamp, rev.Author, rev.Message, rev.Branch, rev.Parent, rev.MergeParent, rev.TreeHash)

	if err != nil {
		return fmt.Errorf("insert revision: %w", err)
	}

	return nil
}

// GetRevision retrieves a revision by number
func (m *MetaStore) GetRevision(number int64) (*core.Revision, error) {
	rev := &core.Revision{}
	err := m.db.QueryRow(`
		SELECT number, timestamp, author, message, branch, parent, merge_parent, tree_hash
		FROM revisions WHERE number = ?
	`, number).Scan(&rev.Number, &rev.Timestamp, &rev.Author, &rev.Message, &rev.Branch, &rev.Parent, &rev.MergeParent, &rev.TreeHash)

	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrRevisionNotFound
		}
		return nil, fmt.Errorf("get revision: %w", err)
	}

	return rev, nil
}

// GetLatestRevision returns the most recent revision number
func (m *MetaStore) GetLatestRevision() (int64, error) {
	var latest int64
	err := m.db.QueryRow("SELECT COALESCE(MAX(number), 0) FROM revisions").Scan(&latest)
	if err != nil {
		return 0, fmt.Errorf("get latest revision: %w", err)
	}
	return latest, nil
}

// ListRevisions returns revisions in descending order
func (m *MetaStore) ListRevisions(branch string, limit, offset int) ([]*core.Revision, error) {
	var rows *sql.Rows
	var err error

	if branch == "" {
		rows, err = m.db.Query(`
			SELECT number, timestamp, author, message, branch, parent, merge_parent, tree_hash
			FROM revisions ORDER BY number DESC LIMIT ? OFFSET ?
		`, limit, offset)
	} else {
		rows, err = m.db.Query(`
			SELECT number, timestamp, author, message, branch, parent, merge_parent, tree_hash
			FROM revisions WHERE branch = ? ORDER BY number DESC LIMIT ? OFFSET ?
		`, branch, limit, offset)
	}

	if err != nil {
		return nil, fmt.Errorf("list revisions: %w", err)
	}
	defer rows.Close()

	var revs []*core.Revision
	for rows.Next() {
		rev := &core.Revision{}
		if err := rows.Scan(&rev.Number, &rev.Timestamp, &rev.Author, &rev.Message, &rev.Branch, &rev.Parent, &rev.MergeParent, &rev.TreeHash); err != nil {
			return nil, fmt.Errorf("scan revision: %w", err)
		}
		revs = append(revs, rev)
	}

	return revs, nil
}

// CreateBranch creates a new branch
func (m *MetaStore) CreateBranch(branch *core.Branch) error {
	_, err := m.db.Exec(`
		INSERT INTO branches (name, head_rev, created_at, created_from)
		VALUES (?, ?, ?, ?)
	`, branch.Name, branch.HeadRev, branch.CreatedAt, branch.CreatedFrom)

	if err != nil {
		return fmt.Errorf("insert branch: %w", err)
	}

	return nil
}

// GetBranch retrieves a branch by name
func (m *MetaStore) GetBranch(name string) (*core.Branch, error) {
	branch := &core.Branch{}
	err := m.db.QueryRow(`
		SELECT name, head_rev, created_at, created_from
		FROM branches WHERE name = ?
	`, name).Scan(&branch.Name, &branch.HeadRev, &branch.CreatedAt, &branch.CreatedFrom)

	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrBranchNotFound
		}
		return nil, fmt.Errorf("get branch: %w", err)
	}

	return branch, nil
}

// UpdateBranchHead updates the head revision of a branch
func (m *MetaStore) UpdateBranchHead(name string, headRev int64) error {
	result, err := m.db.Exec("UPDATE branches SET head_rev = ? WHERE name = ?", headRev, name)
	if err != nil {
		return fmt.Errorf("update branch head: %w", err)
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrBranchNotFound
	}

	return nil
}

// ListBranches returns all branches
func (m *MetaStore) ListBranches() ([]*core.Branch, error) {
	rows, err := m.db.Query("SELECT name, head_rev, created_at, created_from FROM branches ORDER BY name")
	if err != nil {
		return nil, fmt.Errorf("list branches: %w", err)
	}
	defer rows.Close()

	var branches []*core.Branch
	for rows.Next() {
		branch := &core.Branch{}
		if err := rows.Scan(&branch.Name, &branch.HeadRev, &branch.CreatedAt, &branch.CreatedFrom); err != nil {
			return nil, fmt.Errorf("scan branch: %w", err)
		}
		branches = append(branches, branch)
	}

	return branches, nil
}

// DeleteBranch removes a branch
func (m *MetaStore) DeleteBranch(name string) error {
	result, err := m.db.Exec("DELETE FROM branches WHERE name = ?", name)
	if err != nil {
		return fmt.Errorf("delete branch: %w", err)
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrBranchNotFound
	}

	return nil
}

// StoreTree stores a tree manifest
func (m *MetaStore) StoreTree(hash string, data []byte) error {
	_, err := m.db.Exec("INSERT OR IGNORE INTO trees (hash, data) VALUES (?, ?)", hash, data)
	if err != nil {
		return fmt.Errorf("store tree: %w", err)
	}
	return nil
}

// GetTree retrieves a tree manifest
func (m *MetaStore) GetTree(hash string) ([]byte, error) {
	var data []byte
	err := m.db.QueryRow("SELECT data FROM trees WHERE hash = ?", hash).Scan(&data)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrTreeNotFound
		}
		return nil, fmt.Errorf("get tree: %w", err)
	}
	return data, nil
}

// SetState sets a key-value pair in state table
func (m *MetaStore) SetState(key, value string) error {
	_, err := m.db.Exec("INSERT OR REPLACE INTO state (key, value) VALUES (?, ?)", key, value)
	if err != nil {
		return fmt.Errorf("set state: %w", err)
	}
	return nil
}

// GetState gets a value from state table
func (m *MetaStore) GetState(key string) (string, error) {
	var value string
	err := m.db.QueryRow("SELECT value FROM state WHERE key = ?", key).Scan(&value)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return "", nil
		}
		return "", fmt.Errorf("get state: %w", err)
	}
	return value, nil
}

// InitRepo initializes a new repository with default branch
func (m *MetaStore) InitRepo() error {
	branch := &core.Branch{
		Name:        core.DefaultBranchName,
		HeadRev:     0,
		CreatedAt:   time.Now().Unix(),
		CreatedFrom: 0,
	}

	if err := m.CreateBranch(branch); err != nil {
		return fmt.Errorf("create default branch: %w", err)
	}

	return nil
}