package db

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"time"

	"instance-manager/internal/config"
	"instance-manager/internal/models"

	"golang.org/x/crypto/bcrypt"
	_ "github.com/mattn/go-sqlite3"
)

type Database struct {
	db *sql.DB
}

func New(cfg config.DatabaseConfig) (*Database, error) {
	dir := filepath.Dir(cfg.Path)
	if err := os.MkdirAll(dir, 0755); err != nil {
		return nil, fmt.Errorf("creating database directory: %w", err)
	}

	sqlDB, err := sql.Open("sqlite3", cfg.Path+"?_journal_mode=WAL&_foreign_keys=on")
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	sqlDB.SetMaxOpenConns(1)

	database := &Database{db: sqlDB}
	if err := database.init(); err != nil {
		sqlDB.Close()
		return nil, fmt.Errorf("initializing database: %w", err)
	}

	return database, nil
}

func (d *Database) init() error {
	schema := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT UNIQUE NOT NULL,
		password_hash TEXT NOT NULL,
		role TEXT NOT NULL DEFAULT 'user',
		is_active INTEGER NOT NULL DEFAULT 1,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
	);

	CREATE TABLE IF NOT EXISTS instances (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL UNIQUE,
		port INTEGER NOT NULL UNIQUE,
		url TEXT NOT NULL,
		status TEXT NOT NULL DEFAULT 'stopped',
		pid INTEGER NOT NULL DEFAULT 0,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
	);

	CREATE INDEX IF NOT EXISTS idx_instances_status ON instances(status);
	CREATE INDEX IF NOT EXISTS idx_instances_port ON instances(port);
	`

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

	if err := d.seedAdmin(); err != nil {
		return fmt.Errorf("seeding admin user: %w", err)
	}

	return nil
}

func (d *Database) seedAdmin() error {
	var count int
	err := d.db.QueryRow("SELECT COUNT(*) FROM users WHERE role = 'admin'").Scan(&count)
	if err != nil {
		return err
	}

	if count > 0 {
		return nil
	}

	hash, err := hashPassword("admin")
	if err != nil {
		return err
	}

	_, err = d.db.Exec(
		"INSERT INTO users (username, password_hash, role, is_active) VALUES (?, ?, 'admin', 1)",
		"admin", hash,
	)
	return err
}

func (d *Database) GetUserByID(id int64) (*models.User, error) {
	var user models.User
	err := d.db.QueryRow(
		"SELECT id, username, password_hash, role, is_active, created_at, updated_at FROM users WHERE id = ?", id,
	).Scan(&user.ID, &user.Username, &user.PasswordHash, &user.Role, &user.IsActive, &user.CreatedAt, &user.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &user, nil
}

func (d *Database) GetUserByUsername(username string) (*models.User, error) {
	var user models.User
	err := d.db.QueryRow(
		"SELECT id, username, password_hash, role, is_active, created_at, updated_at FROM users WHERE username = ?", username,
	).Scan(&user.ID, &user.Username, &user.PasswordHash, &user.Role, &user.IsActive, &user.CreatedAt, &user.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &user, nil
}

func (d *Database) CreateUser(username, passwordHash string) (*models.User, error) {
	result, err := d.db.Exec(
		"INSERT INTO users (username, password_hash) VALUES (?, ?)",
		username, passwordHash,
	)
	if err != nil {
		return nil, err
	}

	id, _ := result.LastInsertId()
	return d.GetUserByID(id)
}

func (d *Database) UpdateUserPassword(userID int64, passwordHash string) error {
	_, err := d.db.Exec(
		"UPDATE users SET password_hash = ?, updated_at = ? WHERE id = ?",
		passwordHash, time.Now(), userID,
	)
	return err
}

func (d *Database) UpdateUserActive(userID int64, active bool) error {
	_, err := d.db.Exec(
		"UPDATE users SET is_active = ?, updated_at = ? WHERE id = ?",
		active, time.Now(), userID,
	)
	return err
}

func (d *Database) GetUsers() ([]models.User, error) {
	rows, err := d.db.Query(
		"SELECT id, username, password_hash, role, is_active, created_at, updated_at FROM users ORDER BY id",
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []models.User
	for rows.Next() {
		var u models.User
		if err := rows.Scan(&u.ID, &u.Username, &u.PasswordHash, &u.Role, &u.IsActive, &u.CreatedAt, &u.UpdatedAt); err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, rows.Err()
}

func (d *Database) GetActiveUsers() ([]models.User, error) {
	rows, err := d.db.Query(
		"SELECT id, username, password_hash, role, is_active, created_at, updated_at FROM users WHERE is_active = 1 ORDER BY id",
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []models.User
	for rows.Next() {
		var u models.User
		if err := rows.Scan(&u.ID, &u.Username, &u.PasswordHash, &u.Role, &u.IsActive, &u.CreatedAt, &u.UpdatedAt); err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, rows.Err()
}

func (d *Database) GetInstanceByUserID(userID int64) (*models.Instance, error) {
	var inst models.Instance
	err := d.db.QueryRow(
		"SELECT id, user_id, port, url, status, pid, created_at, updated_at FROM instances WHERE user_id = ?", userID,
	).Scan(&inst.ID, &inst.UserID, &inst.Port, &inst.URL, &inst.Status, &inst.Pid, &inst.CreatedAt, &inst.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &inst, nil
}

func (d *Database) CreateInstance(userID int64, port int, url string) (*models.Instance, error) {
	_, err := d.db.Exec(
		"INSERT INTO instances (user_id, port, url, status) VALUES (?, ?, ?, 'starting')",
		userID, port, url,
	)
	if err != nil {
		return nil, err
	}

	return d.GetInstanceByUserID(userID)
}

func (d *Database) UpdateInstanceStatus(instID int64, status string) error {
	_, err := d.db.Exec(
		"UPDATE instances SET status = ?, pid = 0, updated_at = ? WHERE id = ?",
		status, time.Now(), instID,
	)
	return err
}

func (d *Database) UpdateInstancePort(instID int64, port int) error {
	_, err := d.db.Exec(
		"UPDATE instances SET port = ?, updated_at = ? WHERE id = ?",
		port, time.Now(), instID,
	)
	return err
}

func (d *Database) UpdateInstanceRunning(instID, pid int64) error {
	_, err := d.db.Exec(
		"UPDATE instances SET status = 'running', pid = ?, updated_at = ? WHERE id = ?",
		pid, time.Now(), instID,
	)
	return err
}

func (d *Database) GetInstanceByPort(port int) (*models.Instance, error) {
	var inst models.Instance
	err := d.db.QueryRow(
		"SELECT id, user_id, port, url, status, pid, created_at, updated_at FROM instances WHERE port = ?", port,
	).Scan(&inst.ID, &inst.UserID, &inst.Port, &inst.URL, &inst.Status, &inst.Pid, &inst.CreatedAt, &inst.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &inst, nil
}

func (d *Database) UpdateInstanceURL(instID int64, url string) error {
	_, err := d.db.Exec(
		"UPDATE instances SET url = ?, updated_at = ? WHERE id = ?",
		url, time.Now(), instID,
	)
	return err
}

func (d *Database) Close() error {
	return d.db.Close()
}

func hashPassword(password string) (string, error) {
	hash, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
	return string(hash), err
}

func HashPassword(password string) (string, error) {
	hash, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
	return string(hash), err
}

func CheckPasswordHash(password, hash string) bool {
	err := bcrypt.CompareHashAndPassword([]byte(hash), []byte(password))
	return err == nil
}