-- Create table
create table if not exists urls (
  id uuid default uuid_generate_v4() primary key,
  url text not null unique,
  title text not null,
  description text
);

create table if not exists logs (
  id uuid default uuid_generate_v4() primary key,
  url_id uuid references urls (id) on delete cascade,
  time timestamp with time zone default timezone('utc'::text, now()) not null,
  status text not null
);

create table if not exists incidents (
  id uuid default uuid_generate_v4() primary key,
  url_id uuid references urls (id) on delete cascade,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  resolved boolean,
  content text
);

-- Enable RLS
alter table urls enable row level security;
alter table logs enable row level security;
alter table incidents enable row level security;

-- Create policy to allow public to read
create policy allow_public_to_read on urls for select using (true);
create policy allow_public_to_read on logs for select using (true);
create policy allow_public_to_read on incidents for select using (true);

-- CRON Job
create extension if not exists pg_cron;
create extension if not exists http;
grant usage on schema cron to postgres;
grant all privileges on all tables in schema cron to postgres;

select
  cron.schedule(
    'health-check-every-15-minute', -- name of the cron job
    '*/15 * * * *', -- every 15 minute
    $$
    select content::json
    from
      http((
        'POST',
        'https://<your-project-ref>.functions.supabase.co/health_check',
        ARRAY[http_header('Authorization', 'Bearer <supabase-anon-key>')],
        'application/json',
        '{"hello": "world"}'
      )::http_request)
    $$
  );