insert into storage.buckets (id, name, public) values ('sites', 'sites', true) on conflict (id) do nothing;
insert into storage.buckets (id, name, public) values ('md', 'md', true) on conflict (id) do nothing;
insert into storage.buckets (id, name, public) values ('projects', 'projects', true) on conflict (id) do nothing;
insert into storage.buckets (id, name, public) values ('admin', 'admin', false) on conflict (id) do nothing;

create table if not exists public.gh_site (
    id uuid primary key default gen_random_uuid(),
    name text not null unique,
    type text not null check (type in ('html', 'md', 'project')),
    owner_id uuid not null,
    ip_address text,
    visit_count bigint default 0,
    macos_submit_id text,
    created_at timestamptz default now(),
    updated_at timestamptz default now()
);

create index if not exists idx_gh_site_owner_id on public.gh_site(owner_id);
create index if not exists idx_gh_site_name on public.gh_site(name);

create table if not exists public.gh_announcement (
    id uuid primary key default gen_random_uuid(),
    content text not null,
    is_active boolean default false,
    created_at timestamptz default now()
);

create index if not exists idx_gh_announcement_active on public.gh_announcement(is_active);

create table if not exists public.system_status (
    id int primary key default 1 check (id = 1),
    maintenance_mode boolean default false,
    maintenance_message text,
    services jsonb default '{}'::jsonb,
    updated_at timestamptz default now(),
    updated_by text
);

create or replace function public.get_sites_paginated(
    p_limit int,
    p_offset int,
    p_order_by text default 'updated_at',
    p_order_dir text default 'DESC'
)
returns table(
    id uuid,
    name text,
    owner_id uuid,
    ip_address text,
    created_at timestamptz,
    updated_at timestamptz,
    total_count bigint
) language plpgsql as $$
declare
    order_sql text;
begin
    order_sql := format('order by %I %s', p_order_by, p_order_dir);
    return query execute format('
        select
            s.id,
            s.name,
            s.owner_id,
            s.ip_address,
            s.created_at,
            s.updated_at,
            count(*) over() as total_count
        from public.gh_site s
        %s
        limit $1 offset $2
    ', order_sql) using p_limit, p_offset;
end;
$$;

create or replace function public.get_users_paginated(
    p_limit int,
    p_offset int
)
returns table(
    owner_id uuid,
    site_count bigint,
    created_at timestamptz,
    sites jsonb,
    total_count bigint
) language plpgsql as $$
begin
    return query
    with agg as (
        select
            s.owner_id,
            count(*) as site_count,
            min(s.created_at) as created_at,
            jsonb_agg(jsonb_build_object('name', s.name, 'type', s.type)) as sites
        from public.gh_site s
        group by s.owner_id
    ),
    total as (select count(*) from agg)
    select
        agg.owner_id,
        agg.site_count,
        agg.created_at,
        agg.sites,
        (select count from total) as total_count
    from agg
    order by agg.created_at desc
    limit p_limit offset p_offset;
end;
$$;

create or replace function public.get_site_stats()
returns table(
    total_sites bigint,
    total_users bigint,
    sites_today bigint
) language plpgsql as $$
begin
    return query
    select
        count(*) as total_sites,
        count(distinct owner_id) as total_users,
        count(*) filter (where created_at::date = current_date) as sites_today
    from public.gh_site;
end;
$$;

create or replace function public.increment_visit(p_name text)
returns void language plpgsql as $$
begin
    update public.gh_site
    set visit_count = visit_count + 1
    where name = p_name;
end;
$$;

alter table public.gh_site disable row level security;
alter table public.gh_announcement disable row level security;
alter table public.system_status disable row level security;

create or replace function public.update_updated_at()
returns trigger language plpgsql as $$
begin
    new.updated_at = now();
    return new;
end;
$$;

create trigger trigger_gh_site_updated_at
before update on public.gh_site
for each row execute function public.update_updated_at();