-- CL6: auto-sync worker scheduling fields + per-instance lock helpers

alter table public.plesk_instances
  add column if not exists auto_sync_enabled boolean not null default false,
  add column if not exists auto_sync_frequency_minutes integer not null default 60,
  add column if not exists last_auto_sync_at timestamptz,
  add column if not exists next_auto_sync_at timestamptz,
  add column if not exists auto_sync_lock_until timestamptz,
  add column if not exists auto_sync_lock_token text;

alter table public.plesk_instances
  drop constraint if exists plesk_instances_auto_sync_frequency_minutes_check;

alter table public.plesk_instances
  add constraint plesk_instances_auto_sync_frequency_minutes_check
  check (auto_sync_frequency_minutes between 5 and 10080);

create index if not exists idx_plesk_instances_auto_sync_due
  on public.plesk_instances (auto_sync_enabled, next_auto_sync_at);

create or replace function public.acquire_instance_auto_sync_lock(
  p_instance_id uuid,
  p_lock_token text,
  p_ttl_seconds integer default 900
)
returns boolean
language plpgsql
security definer
set search_path = public
as $$
declare
  v_acquired boolean := false;
begin
  update public.plesk_instances
  set
    auto_sync_lock_until = now() + make_interval(secs => p_ttl_seconds),
    auto_sync_lock_token = p_lock_token
  where id = p_instance_id
    and auto_sync_enabled = true
    and (next_auto_sync_at is null or next_auto_sync_at <= now())
    and (auto_sync_lock_until is null or auto_sync_lock_until < now());

  get diagnostics v_acquired = row_count;
  return v_acquired;
end;
$$;

create or replace function public.release_instance_auto_sync_lock(
  p_instance_id uuid,
  p_lock_token text
)
returns void
language sql
security definer
set search_path = public
as $$
  update public.plesk_instances
  set auto_sync_lock_until = null,
      auto_sync_lock_token = null
  where id = p_instance_id
    and auto_sync_lock_token = p_lock_token;
$$;