PR-Helper/database/db.go

package database

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"

	"github.com/HoHD/PR-Helper/models"
)

type DB struct {
	conn *sql.DB
}

func New(dsn string) (*DB, error) {
	conn, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	if err := conn.Ping(); err != nil {
		return nil, err
	}
	db := &DB{conn: conn}
	if err := db.migrate(); err != nil {
		return nil, err
	}
	if err := db.seedDefaults(); err != nil {
		return nil, err
	}
	return db, nil
}

func (db *DB) Close() error {
	return db.conn.Close()
}

func (db *DB) Conn() *sql.DB {
	return db.conn
}

func (db *DB) migrate() error {
	stmts := []string{
		"CREATE TABLE IF NOT EXISTS settings (" +
			"`key`   VARCHAR(255) PRIMARY KEY," +
			"value TEXT NOT NULL" +
			") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
		`CREATE TABLE IF NOT EXISTS users (
			id            BIGINT AUTO_INCREMENT PRIMARY KEY,
			email         VARCHAR(255) NOT NULL UNIQUE,
			password_hash TEXT NOT NULL,
			created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,
		"CREATE TABLE IF NOT EXISTS user_settings (" +
			"user_id BIGINT NOT NULL," +
			"`key`   VARCHAR(255) NOT NULL," +
			"value   TEXT NOT NULL," +
			"PRIMARY KEY (user_id, `key`)," +
			"FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE" +
			") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
		`CREATE TABLE IF NOT EXISTS repositories (
			id         BIGINT AUTO_INCREMENT PRIMARY KEY,
			user_id    BIGINT,
			url        TEXT NOT NULL,
			local_path TEXT NOT NULL,
			size_bytes BIGINT DEFAULT 0,
			cloned_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
			last_used  DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,
		`CREATE TABLE IF NOT EXISTS analyses (
			id          BIGINT AUTO_INCREMENT PRIMARY KEY,
			user_id     BIGINT,
			repo_id     BIGINT,
			type        VARCHAR(50) NOT NULL,
			base_ref    TEXT NOT NULL,
			head_ref    TEXT NOT NULL,
			result      LONGTEXT,
			created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
			FOREIGN KEY (repo_id) REFERENCES repositories(id)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,
		`CREATE TABLE IF NOT EXISTS review_notes (
			id          BIGINT AUTO_INCREMENT PRIMARY KEY,
			user_id     BIGINT,
			analysis_id BIGINT,
			scope       VARCHAR(50) NOT NULL,
			scope_key   TEXT NOT NULL,
			content     TEXT NOT NULL,
			created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
			FOREIGN KEY (analysis_id) REFERENCES analyses(id)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`,
	}
	for _, s := range stmts {
		if _, err := db.conn.Exec(s); err != nil {
			return err
		}
	}
	// Incremental migrations — ignore "duplicate column" errors for idempotency.
	alterStmts := []string{
		"ALTER TABLE repositories ADD COLUMN auth_type VARCHAR(20) NOT NULL DEFAULT 'none'",
		"ALTER TABLE repositories ADD COLUMN credential TEXT",
	}
	for _, s := range alterStmts {
		db.conn.Exec(s) // ignore error (column already exists)
	}
	return nil
}

func (db *DB) seedDefaults() error {
	for key, val := range models.DefaultSettings {
		_, err := db.conn.Exec(
			"INSERT IGNORE INTO settings (`key`, value) VALUES (?, ?)", key, val,
		)
		if err != nil {
			log.Printf("warning: failed to seed setting %s: %v", key, err)
		}
	}
	return nil
}