package store

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

func (db *DB) GetUserByEmail(ctx context.Context, email string) (*User, error) {
	row := db.QueryRowContext(ctx, `
		SELECT id, global_user_id, email, name, role, status, created_at
		FROM users WHERE email = ?`, email)
	return scanUser(row)
}

func (db *DB) GetUserByID(ctx context.Context, id int64) (*User, error) {
	row := db.QueryRowContext(ctx, `
		SELECT id, global_user_id, email, name, role, status, created_at
		FROM users WHERE id = ?`, id)
	return scanUser(row)
}

func (db *DB) CreateUser(ctx context.Context, email, name, role string, globalUserID *string) (int64, error) {
	res, err := db.ExecContext(ctx, `
		INSERT INTO users (global_user_id, email, name, role, status)
		VALUES (?, ?, ?, ?, 'active')`,
		globalUserID, email, nullStr(name), role)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (db *DB) UpdateUserGlobalID(ctx context.Context, userID int64, globalUserID string) error {
	_, err := db.ExecContext(ctx, `UPDATE users SET global_user_id = ? WHERE id = ?`, globalUserID, userID)
	return err
}

func (db *DB) UpdateUserName(ctx context.Context, userID int64, name string) error {
	_, err := db.ExecContext(ctx, `UPDATE users SET name = ? WHERE id = ?`, name, userID)
	return err
}

func (db *DB) UpdateUserStatus(ctx context.Context, userID int64, status string) error {
	_, err := db.ExecContext(ctx, `UPDATE users SET status = ? WHERE id = ?`, status, userID)
	return err
}

func (db *DB) ListUsers(ctx context.Context, limit, offset int) ([]User, error) {
	rows, err := db.QueryContext(ctx, `
		SELECT id, global_user_id, email, name, role, status, created_at
		FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?`, limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []User
	for rows.Next() {
		u, err := scanUserRows(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *u)
	}
	return out, rows.Err()
}

func (db *DB) CountUsers(ctx context.Context) (int, error) {
	var n int
	err := db.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&n)
	return n, err
}

func (db *DB) GetActiveSubscription(ctx context.Context, userID int64) (*Subscription, error) {
	row := db.QueryRowContext(ctx, `
		SELECT s.id, s.user_id, s.plan_id, s.status, s.starts_at, s.ends_at,
		       p.id, p.slug, p.name, p.max_active_leads, p.max_add_per_day,
		       p.max_export_per_day, p.can_export, p.price_cents, p.is_active
		FROM subscriptions s
		JOIN plans p ON p.id = s.plan_id
		WHERE s.user_id = ? AND s.status IN ('trial','active')
		ORDER BY s.starts_at DESC LIMIT 1`, userID)

	var sub Subscription
	var plan Plan
	var maxAdd sql.NullInt64
	var endsAt sql.NullTime
	err := row.Scan(
		&sub.ID, &sub.UserID, &sub.PlanID, &sub.Status, &sub.StartsAt, &endsAt,
		&plan.ID, &plan.Slug, &plan.Name, &plan.MaxActiveLeads, &maxAdd,
		&plan.MaxExportPerDay, &plan.CanExport, &plan.PriceCents, &plan.IsActive,
	)
	if err != nil {
		return nil, err
	}
	if maxAdd.Valid {
		v := int(maxAdd.Int64)
		plan.MaxAddPerDay = &v
	}
	if endsAt.Valid {
		sub.EndsAt = &endsAt.Time
	}
	sub.Plan = &plan
	return &sub, nil
}

func (db *DB) CreateTrialSubscription(ctx context.Context, userID int64, planID int) error {
	_, err := db.ExecContext(ctx, `
		INSERT INTO subscriptions (user_id, plan_id, status, starts_at)
		VALUES (?, ?, 'trial', ?)`, userID, planID, time.Now())
	return err
}

func (db *DB) UpdateSubscription(ctx context.Context, userID int64, planID int, status string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE subscriptions SET plan_id = ?, status = ?, starts_at = ?
		WHERE user_id = ? AND status IN ('trial','active')`,
		planID, status, time.Now(), userID)
	return err
}

func scanUser(row *sql.Row) (*User, error) {
	var u User
	var globalID, name sql.NullString
	err := row.Scan(&u.ID, &globalID, &u.Email, &name, &u.Role, &u.Status, &u.CreatedAt)
	if err != nil {
		return nil, err
	}
	if globalID.Valid {
		u.GlobalUserID = &globalID.String
	}
	if name.Valid {
		u.Name = &name.String
	}
	return &u, nil
}

func scanUserRows(rows *sql.Rows) (*User, error) {
	var u User
	var globalID, name sql.NullString
	err := rows.Scan(&u.ID, &globalID, &u.Email, &name, &u.Role, &u.Status, &u.CreatedAt)
	if err != nil {
		return nil, err
	}
	if globalID.Valid {
		u.GlobalUserID = &globalID.String
	}
	if name.Valid {
		u.Name = &name.String
	}
	return &u, nil
}

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