package push

import (
	"context"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type Repository interface {
	Save(ctx context.Context, sub *Subscription) error
	Delete(ctx context.Context, userID int64, endpoint string) error
	DeleteByEndpoint(ctx context.Context, endpoint string) error
	FindByUser(ctx context.Context, userID int64) ([]Subscription, error)
	FindByRoles(ctx context.Context, roles []string) ([]Subscription, error)
	FindAll(ctx context.Context) ([]Subscription, error)
}

type pgRepository struct {
	pool *pgxpool.Pool
}

func NewRepository(pool *pgxpool.Pool) Repository {
	return &pgRepository{pool: pool}
}

func (r *pgRepository) Save(ctx context.Context, sub *Subscription) error {
	const q = `
		INSERT INTO push_subscriptions (user_id, endpoint, p256dh, auth, device_token, app, user_agent, created_at, updated_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, NOW(), NOW())
		ON CONFLICT (user_id, endpoint) DO UPDATE
		SET p256dh = EXCLUDED.p256dh,
		    auth = EXCLUDED.auth,
		    device_token = EXCLUDED.device_token,
		    app = EXCLUDED.app,
		    user_agent = EXCLUDED.user_agent,
		    updated_at = NOW()
		RETURNING id, created_at, updated_at
	`
	return r.pool.QueryRow(ctx, q,
		sub.UserID, sub.Endpoint, sub.P256dh, sub.Auth, sub.DeviceToken, sub.App, sub.UserAgent,
	).Scan(&sub.ID, &sub.CreatedAt, &sub.UpdatedAt)
}

func (r *pgRepository) Delete(ctx context.Context, userID int64, endpoint string) error {
	const q = `DELETE FROM push_subscriptions WHERE user_id = $1 AND endpoint = $2`
	_, err := r.pool.Exec(ctx, q, userID, endpoint)
	return err
}

func (r *pgRepository) DeleteByEndpoint(ctx context.Context, endpoint string) error {
	const q = `DELETE FROM push_subscriptions WHERE endpoint = $1`
	_, err := r.pool.Exec(ctx, q, endpoint)
	return err
}

func (r *pgRepository) FindByUser(ctx context.Context, userID int64) ([]Subscription, error) {
	const q = `
		SELECT id, user_id, endpoint, p256dh, auth, device_token, app, user_agent, created_at, updated_at
		FROM push_subscriptions
		WHERE user_id = $1
		ORDER BY id DESC
	`
	rows, err := r.pool.Query(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanSubscriptions(rows)
}

func (r *pgRepository) FindByRoles(ctx context.Context, roles []string) ([]Subscription, error) {
	const q = `
		SELECT ps.id, ps.user_id, ps.endpoint, ps.p256dh, ps.auth, ps.device_token, ps.app, ps.user_agent, ps.created_at, ps.updated_at
		FROM push_subscriptions ps
		JOIN users u ON u.id = ps.user_id
		WHERE u.role = ANY($1) AND u.is_active = true
		ORDER BY ps.id DESC
	`
	rows, err := r.pool.Query(ctx, q, roles)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanSubscriptions(rows)
}

func (r *pgRepository) FindAll(ctx context.Context) ([]Subscription, error) {
	const q = `
		SELECT id, user_id, endpoint, p256dh, auth, device_token, app, user_agent, created_at, updated_at
		FROM push_subscriptions
		ORDER BY id DESC LIMIT 500
	`
	rows, err := r.pool.Query(ctx, q)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanSubscriptions(rows)
}

func scanSubscriptions(rows pgx.Rows) ([]Subscription, error) {
	var subs []Subscription
	for rows.Next() {
		var s Subscription
		if err := rows.Scan(
			&s.ID, &s.UserID, &s.Endpoint, &s.P256dh, &s.Auth, &s.DeviceToken, &s.App, &s.UserAgent, &s.CreatedAt, &s.UpdatedAt,
		); err != nil {
			return nil, err
		}
		subs = append(subs, s)
	}
	return subs, rows.Err()
}