package study.graphql;

import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * U50 真实 SQL 仓库：H2 内存库上的 books/authors 合成数据。
 * 每个查询方法都经 SqlCounter 计数，用于观察 N+1（每个父对象一次查询）。
 */
@Repository
public class LibraryRepository {

    private final JdbcTemplate jdbc;
    private final SqlCounter counter;

    public LibraryRepository(JdbcTemplate jdbc, SqlCounter counter) {
        this.jdbc = jdbc;
        this.counter = counter;
    }

    public List<Book> findBooks(String tenant, int limit) {
        counter.increment();
        return jdbc.query("select b.id, b.title, b.tenant, b.author_id from books b where b.tenant = ? order by b.id limit ?",
                (rs, rowNum) -> new Book(rs.getLong("id"), rs.getString("title"), rs.getString("tenant"), rs.getLong("author_id")),
                tenant, limit);
    }

    public Book findBook(String tenant, String id) {
        counter.increment();
        List<Book> rows = jdbc.query("select b.id, b.title, b.tenant, b.author_id from books b where b.tenant = ? and b.id = ?",
                (rs, rowNum) -> new Book(rs.getLong("id"), rs.getString("title"), rs.getString("tenant"), rs.getLong("author_id")),
                tenant, Long.parseLong(id));
        return rows.isEmpty() ? null : rows.get(0);
    }

    public Author findAuthorById(long id) {
        counter.increment();
        List<Author> rows = jdbc.query("select a.id, a.name from authors a where a.id = ?",
                (rs, rowNum) -> new Author(rs.getLong("id"), rs.getString("name")), id);
        return rows.isEmpty() ? null : rows.get(0);
    }

    public Map<Long, Author> findAuthorsByIds(Set<Long> ids) {
        counter.increment();
        if (ids.isEmpty()) {
            return Map.of();
        }
        String placeholders = String.join(",", ids.stream().map(x -> "?").toList());
        Map<Long, Author> byId = new java.util.HashMap<>();
        jdbc.query("select a.id, a.name from authors a where a.id in (" + placeholders + ")",
                rs -> {
                    byId.put(rs.getLong("id"), new Author(rs.getLong("id"), rs.getString("name")));
                }, ids.toArray());
        return byId;
    }

    public List<Author> findAllAuthors() {
        counter.increment();
        return jdbc.query("select a.id, a.name from authors a order by a.id",
                (rs, rowNum) -> new Author(rs.getLong("id"), rs.getString("name")));
    }

    public long insertBook(String tenant, String title, long authorId) {
        counter.increment();
        jdbc.update("insert into books(title, tenant, author_id) values (?, ?, ?)", title, tenant, authorId);
        Long id = jdbc.queryForObject("select max(id) from books where tenant = ?", Long.class, tenant);
        return id == null ? -1 : id;
    }

    public synchronized void reset() {
        jdbc.execute("drop table if exists books");
        jdbc.execute("drop table if exists authors");
        seed();
    }

    public void seed() {
        jdbc.execute("create table authors(id bigint primary key auto_increment, name varchar(80) not null)");
        jdbc.execute("create table books(id bigint primary key auto_increment, title varchar(120) not null, tenant varchar(40) not null, author_id bigint not null)");
        jdbc.update("insert into authors(name) values ('A1')");
        jdbc.update("insert into authors(name) values ('A2')");
        jdbc.update("insert into books(title, tenant, author_id) values ('T1', 'tenant-a', 1)");
        jdbc.update("insert into books(title, tenant, author_id) values ('T2', 'tenant-a', 2)");
        jdbc.update("insert into books(title, tenant, author_id) values ('T3', 'tenant-a', 1)");
        jdbc.update("insert into books(title, tenant, author_id) values ('SECRET', 'tenant-b', 2)");
    }
}