// Package infra gère l'infrastructure (serveurs, containers, nginx).
package infra

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

// ============================================================================
// Servers
// ============================================================================

// CreateServer crée un nouveau serveur.
func (db *DB) CreateServer(s *Server) error {
	now := time.Now()
	result, err := db.Exec(`
		INSERT INTO servers (name, host, vpn_ip, ssh_port, ssh_user, ssh_key_file, has_incus, has_nginx, status, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		s.Name, s.Host, nullString(s.VpnIP), s.SSHPort, s.SSHUser, s.SSHKeyFile,
		boolToInt(s.HasIncus), boolToInt(s.HasNginx), s.Status, now, now)
	if err != nil {
		return fmt.Errorf("insert server: %w", err)
	}
	id, _ := result.LastInsertId()
	s.ID = id
	s.CreatedAt = now
	s.UpdatedAt = now
	return nil
}

// GetServer récupère un serveur par ID.
func (db *DB) GetServer(id int64) (*Server, error) {
	s := &Server{}
	err := db.QueryRow(`
		SELECT id, name, host, vpn_ip, ssh_port, ssh_user, ssh_key_file, has_incus, has_nginx, status, created_at, updated_at
		FROM servers WHERE id = ?`, id).Scan(
		&s.ID, &s.Name, &s.Host, &s.VpnIP, &s.SSHPort, &s.SSHUser, &s.SSHKeyFile,
		&s.HasIncus, &s.HasNginx, &s.Status, &s.CreatedAt, &s.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get server: %w", err)
	}
	return s, nil
}

// GetServerByName récupère un serveur par nom.
func (db *DB) GetServerByName(name string) (*Server, error) {
	s := &Server{}
	err := db.QueryRow(`
		SELECT id, name, host, vpn_ip, ssh_port, ssh_user, ssh_key_file, has_incus, has_nginx, status, created_at, updated_at
		FROM servers WHERE name = ?`, name).Scan(
		&s.ID, &s.Name, &s.Host, &s.VpnIP, &s.SSHPort, &s.SSHUser, &s.SSHKeyFile,
		&s.HasIncus, &s.HasNginx, &s.Status, &s.CreatedAt, &s.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get server by name: %w", err)
	}
	return s, nil
}

// ListServers retourne tous les serveurs.
func (db *DB) ListServers() ([]Server, error) {
	rows, err := db.Query(`
		SELECT id, name, host, vpn_ip, ssh_port, ssh_user, ssh_key_file, has_incus, has_nginx, status, created_at, updated_at
		FROM servers ORDER BY name`)
	if err != nil {
		return nil, fmt.Errorf("list servers: %w", err)
	}
	defer rows.Close()

	var servers []Server
	for rows.Next() {
		var s Server
		if err := rows.Scan(&s.ID, &s.Name, &s.Host, &s.VpnIP, &s.SSHPort, &s.SSHUser, &s.SSHKeyFile,
			&s.HasIncus, &s.HasNginx, &s.Status, &s.CreatedAt, &s.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan server: %w", err)
		}
		servers = append(servers, s)
	}
	return servers, nil
}

// UpdateServer met à jour un serveur.
func (db *DB) UpdateServer(s *Server) error {
	s.UpdatedAt = time.Now()
	_, err := db.Exec(`
		UPDATE servers SET name=?, host=?, vpn_ip=?, ssh_port=?, ssh_user=?, ssh_key_file=?,
		has_incus=?, has_nginx=?, status=?, updated_at=? WHERE id=?`,
		s.Name, s.Host, nullString(s.VpnIP), s.SSHPort, s.SSHUser, s.SSHKeyFile,
		boolToInt(s.HasIncus), boolToInt(s.HasNginx), s.Status, s.UpdatedAt, s.ID)
	if err != nil {
		return fmt.Errorf("update server: %w", err)
	}
	return nil
}

// UpdateServerStatus met à jour le statut d'un serveur.
func (db *DB) UpdateServerStatus(id int64, status ServerStatus) error {
	_, err := db.Exec(`UPDATE servers SET status=?, updated_at=? WHERE id=?`,
		status, time.Now(), id)
	if err != nil {
		return fmt.Errorf("update server status: %w", err)
	}
	return nil
}

// DeleteServer supprime un serveur.
func (db *DB) DeleteServer(id int64) error {
	_, err := db.Exec(`DELETE FROM servers WHERE id=?`, id)
	if err != nil {
		return fmt.Errorf("delete server: %w", err)
	}
	return nil
}

// ============================================================================
// Containers
// ============================================================================

// CreateContainer crée un nouveau container.
func (db *DB) CreateContainer(c *Container) error {
	now := time.Now()
	result, err := db.Exec(`
		INSERT INTO containers (server_id, name, incus_name, ip, vpn_ip, image, status, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		c.ServerID, c.Name, c.IncusName, nullString(c.IP), nullString(c.VpnIP), c.Image, c.Status, now, now)
	if err != nil {
		return fmt.Errorf("insert container: %w", err)
	}
	id, _ := result.LastInsertId()
	c.ID = id
	c.CreatedAt = now
	c.UpdatedAt = now
	return nil
}

// GetContainer récupère un container par ID.
func (db *DB) GetContainer(id int64) (*Container, error) {
	c := &Container{}
	err := db.QueryRow(`
		SELECT id, server_id, name, incus_name, ip, vpn_ip, image, status, created_at, updated_at
		FROM containers WHERE id = ?`, id).Scan(
		&c.ID, &c.ServerID, &c.Name, &c.IncusName, &c.IP, &c.VpnIP, &c.Image, &c.Status, &c.CreatedAt, &c.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get container: %w", err)
	}
	return c, nil
}

// ListContainersByServer retourne les containers d'un serveur.
func (db *DB) ListContainersByServer(serverID int64) ([]Container, error) {
	rows, err := db.Query(`
		SELECT id, server_id, name, incus_name, ip, vpn_ip, image, status, created_at, updated_at
		FROM containers WHERE server_id = ? ORDER BY name`, serverID)
	if err != nil {
		return nil, fmt.Errorf("list containers: %w", err)
	}
	defer rows.Close()

	var containers []Container
	for rows.Next() {
		var c Container
		if err := rows.Scan(&c.ID, &c.ServerID, &c.Name, &c.IncusName, &c.IP, &c.VpnIP, &c.Image, &c.Status, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan container: %w", err)
		}
		containers = append(containers, c)
	}
	return containers, nil
}

// ListAllContainers retourne tous les containers.
func (db *DB) ListAllContainers() ([]Container, error) {
	rows, err := db.Query(`
		SELECT id, server_id, name, incus_name, ip, vpn_ip, image, status, created_at, updated_at
		FROM containers ORDER BY server_id, name`)
	if err != nil {
		return nil, fmt.Errorf("list all containers: %w", err)
	}
	defer rows.Close()

	var containers []Container
	for rows.Next() {
		var c Container
		if err := rows.Scan(&c.ID, &c.ServerID, &c.Name, &c.IncusName, &c.IP, &c.VpnIP, &c.Image, &c.Status, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan container: %w", err)
		}
		containers = append(containers, c)
	}
	return containers, nil
}

// UpdateContainer met à jour un container.
func (db *DB) UpdateContainer(c *Container) error {
	c.UpdatedAt = time.Now()
	_, err := db.Exec(`
		UPDATE containers SET server_id=?, name=?, incus_name=?, ip=?, vpn_ip=?, image=?, status=?, updated_at=?
		WHERE id=?`,
		c.ServerID, c.Name, c.IncusName, nullString(c.IP), nullString(c.VpnIP), c.Image, c.Status, c.UpdatedAt, c.ID)
	if err != nil {
		return fmt.Errorf("update container: %w", err)
	}
	return nil
}

// UpdateContainerStatus met à jour le statut d'un container.
func (db *DB) UpdateContainerStatus(id int64, status ContainerStatus) error {
	_, err := db.Exec(`UPDATE containers SET status=?, updated_at=? WHERE id=?`,
		status, time.Now(), id)
	if err != nil {
		return fmt.Errorf("update container status: %w", err)
	}
	return nil
}

// DeleteContainer supprime un container.
func (db *DB) DeleteContainer(id int64) error {
	_, err := db.Exec(`DELETE FROM containers WHERE id=?`, id)
	if err != nil {
		return fmt.Errorf("delete container: %w", err)
	}
	return nil
}

// ============================================================================
// NginxConfigs
// ============================================================================

// CreateNginxConfig crée une nouvelle config Nginx.
func (db *DB) CreateNginxConfig(n *NginxConfig) error {
	now := time.Now()
	result, err := db.Exec(`
		INSERT INTO nginx_configs (server_id, domain, type, template, upstream, ssl_enabled, config_content, status, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		n.ServerID, n.Domain, n.Type, nullString(n.Template), nullString(n.Upstream),
		boolToInt(n.SSLEnabled), nullString(n.ConfigContent), n.Status, now, now)
	if err != nil {
		return fmt.Errorf("insert nginx config: %w", err)
	}
	id, _ := result.LastInsertId()
	n.ID = id
	n.CreatedAt = now
	n.UpdatedAt = now
	return nil
}

// GetNginxConfig récupère une config Nginx par ID.
func (db *DB) GetNginxConfig(id int64) (*NginxConfig, error) {
	n := &NginxConfig{}
	err := db.QueryRow(`
		SELECT id, server_id, domain, type, template, upstream, ssl_enabled, config_content, status, created_at, updated_at
		FROM nginx_configs WHERE id = ?`, id).Scan(
		&n.ID, &n.ServerID, &n.Domain, &n.Type, &n.Template, &n.Upstream,
		&n.SSLEnabled, &n.ConfigContent, &n.Status, &n.CreatedAt, &n.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get nginx config: %w", err)
	}
	return n, nil
}

// GetNginxConfigByDomain récupère une config par domaine.
func (db *DB) GetNginxConfigByDomain(serverID int64, domain string) (*NginxConfig, error) {
	n := &NginxConfig{}
	err := db.QueryRow(`
		SELECT id, server_id, domain, type, template, upstream, ssl_enabled, config_content, status, created_at, updated_at
		FROM nginx_configs WHERE server_id = ? AND domain = ?`, serverID, domain).Scan(
		&n.ID, &n.ServerID, &n.Domain, &n.Type, &n.Template, &n.Upstream,
		&n.SSLEnabled, &n.ConfigContent, &n.Status, &n.CreatedAt, &n.UpdatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get nginx config by domain: %w", err)
	}
	return n, nil
}

// ListNginxConfigsByServer retourne les configs d'un serveur.
func (db *DB) ListNginxConfigsByServer(serverID int64) ([]NginxConfig, error) {
	rows, err := db.Query(`
		SELECT id, server_id, domain, type, template, upstream, ssl_enabled, config_content, status, created_at, updated_at
		FROM nginx_configs WHERE server_id = ? ORDER BY domain`, serverID)
	if err != nil {
		return nil, fmt.Errorf("list nginx configs: %w", err)
	}
	defer rows.Close()

	var configs []NginxConfig
	for rows.Next() {
		var n NginxConfig
		if err := rows.Scan(&n.ID, &n.ServerID, &n.Domain, &n.Type, &n.Template, &n.Upstream,
			&n.SSLEnabled, &n.ConfigContent, &n.Status, &n.CreatedAt, &n.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan nginx config: %w", err)
		}
		configs = append(configs, n)
	}
	return configs, nil
}

// ListAllNginxConfigs retourne toutes les configs.
func (db *DB) ListAllNginxConfigs() ([]NginxConfig, error) {
	rows, err := db.Query(`
		SELECT id, server_id, domain, type, template, upstream, ssl_enabled, config_content, status, created_at, updated_at
		FROM nginx_configs ORDER BY server_id, domain`)
	if err != nil {
		return nil, fmt.Errorf("list all nginx configs: %w", err)
	}
	defer rows.Close()

	var configs []NginxConfig
	for rows.Next() {
		var n NginxConfig
		if err := rows.Scan(&n.ID, &n.ServerID, &n.Domain, &n.Type, &n.Template, &n.Upstream,
			&n.SSLEnabled, &n.ConfigContent, &n.Status, &n.CreatedAt, &n.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan nginx config: %w", err)
		}
		configs = append(configs, n)
	}
	return configs, nil
}

// UpdateNginxConfig met à jour une config.
func (db *DB) UpdateNginxConfig(n *NginxConfig) error {
	n.UpdatedAt = time.Now()
	_, err := db.Exec(`
		UPDATE nginx_configs SET server_id=?, domain=?, type=?, template=?, upstream=?,
		ssl_enabled=?, config_content=?, status=?, updated_at=? WHERE id=?`,
		n.ServerID, n.Domain, n.Type, nullString(n.Template), nullString(n.Upstream),
		boolToInt(n.SSLEnabled), nullString(n.ConfigContent), n.Status, n.UpdatedAt, n.ID)
	if err != nil {
		return fmt.Errorf("update nginx config: %w", err)
	}
	return nil
}

// DeleteNginxConfig supprime une config.
func (db *DB) DeleteNginxConfig(id int64) error {
	_, err := db.Exec(`DELETE FROM nginx_configs WHERE id=?`, id)
	if err != nil {
		return fmt.Errorf("delete nginx config: %w", err)
	}
	return nil
}

// ============================================================================
// AppBindings
// ============================================================================

// CreateAppBinding crée un nouveau binding.
func (db *DB) CreateAppBinding(b *AppBinding) error {
	now := time.Now()
	result, err := db.Exec(`
		INSERT INTO app_bindings (app_id, container_id, nginx_config_id, server_id, type, created_at)
		VALUES (?, ?, ?, ?, ?, ?)`,
		b.AppID, nullInt64(b.ContainerID), nullInt64(b.NginxConfigID), nullInt64(b.ServerID), b.Type, now)
	if err != nil {
		return fmt.Errorf("insert app binding: %w", err)
	}
	id, _ := result.LastInsertId()
	b.ID = id
	b.CreatedAt = now
	return nil
}

// ListAppBindings retourne les bindings d'une app.
func (db *DB) ListAppBindings(appID string) ([]AppBinding, error) {
	rows, err := db.Query(`
		SELECT id, app_id, container_id, nginx_config_id, server_id, type, created_at
		FROM app_bindings WHERE app_id = ?`, appID)
	if err != nil {
		return nil, fmt.Errorf("list app bindings: %w", err)
	}
	defer rows.Close()

	var bindings []AppBinding
	for rows.Next() {
		var b AppBinding
		if err := rows.Scan(&b.ID, &b.AppID, &b.ContainerID, &b.NginxConfigID, &b.ServerID, &b.Type, &b.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan app binding: %w", err)
		}
		bindings = append(bindings, b)
	}
	return bindings, nil
}

// DeleteAppBinding supprime un binding.
func (db *DB) DeleteAppBinding(id int64) error {
	_, err := db.Exec(`DELETE FROM app_bindings WHERE id=?`, id)
	if err != nil {
		return fmt.Errorf("delete app binding: %w", err)
	}
	return nil
}

// DeleteAppBindingsByApp supprime tous les bindings d'une app.
func (db *DB) DeleteAppBindingsByApp(appID string) error {
	_, err := db.Exec(`DELETE FROM app_bindings WHERE app_id=?`, appID)
	if err != nil {
		return fmt.Errorf("delete app bindings: %w", err)
	}
	return nil
}

// ============================================================================
// Helpers
// ============================================================================

func nullString(s string) interface{} {
	if s == "" {
		return nil
	}
	return s
}

func nullInt64(i *int64) interface{} {
	if i == nil {
		return nil
	}
	return *i
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

// ============================================================================
// Aggregate queries
// ============================================================================

// GetInfraOverview retourne une vue complète de l'infrastructure.
func (db *DB) GetInfraOverview() (*InfraOverview, error) {
	servers, err := db.ListServers()
	if err != nil {
		return nil, err
	}

	overview := &InfraOverview{
		Servers: make([]ServerWithContainers, len(servers)),
	}

	for i, s := range servers {
		overview.Servers[i].Server = s
		containers, err := db.ListContainersByServer(s.ID)
		if err != nil {
			return nil, err
		}
		overview.Servers[i].Containers = containers
	}

	overview.NginxConfigs, err = db.ListAllNginxConfigs()
	if err != nil {
		return nil, err
	}

	return overview, nil
}