-- API Keys table
create table "public"."api_keys" (
    "id" bigint generated by default as identity not null,
    "created_at" timestamp with time zone not null default now(),
    "sales_id" bigint not null,
    "name" text not null,
    "key_hash" text not null,
    "key_prefix" text not null,
    "scopes" text[] not null default '{}',
    "is_active" boolean not null default true,
    "expires_at" timestamp with time zone,
    "last_used_at" timestamp with time zone,
    "created_by_sales_id" bigint not null
);

alter table "public"."api_keys" enable row level security;

CREATE UNIQUE INDEX api_keys_pkey ON public.api_keys USING btree (id);
CREATE UNIQUE INDEX api_keys_key_hash_unique ON public.api_keys USING btree (key_hash);
CREATE INDEX api_keys_sales_id_idx ON public.api_keys USING btree (sales_id);

alter table "public"."api_keys" add constraint "api_keys_pkey" PRIMARY KEY using index "api_keys_pkey";
alter table "public"."api_keys" add constraint "api_keys_sales_id_fkey"
    FOREIGN KEY (sales_id) REFERENCES sales(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."api_keys" validate constraint "api_keys_sales_id_fkey";
alter table "public"."api_keys" add constraint "api_keys_created_by_sales_id_fkey"
    FOREIGN KEY (created_by_sales_id) REFERENCES sales(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."api_keys" validate constraint "api_keys_created_by_sales_id_fkey";

-- Webhooks table
create table "public"."webhooks" (
    "id" bigint generated by default as identity not null,
    "created_at" timestamp with time zone not null default now(),
    "sales_id" bigint not null,
    "name" text not null,
    "url" text not null,
    "events" text[] not null default '{}',
    "headers" jsonb,
    "is_active" boolean not null default true,
    "secret" text not null,
    "last_triggered_at" timestamp with time zone,
    "failure_count" int not null default 0,
    "created_by_sales_id" bigint not null
);

alter table "public"."webhooks" enable row level security;

CREATE UNIQUE INDEX webhooks_pkey ON public.webhooks USING btree (id);
CREATE INDEX webhooks_sales_id_idx ON public.webhooks USING btree (sales_id);
CREATE INDEX webhooks_events_idx ON public.webhooks USING gin (events);

alter table "public"."webhooks" add constraint "webhooks_pkey" PRIMARY KEY using index "webhooks_pkey";
alter table "public"."webhooks" add constraint "webhooks_sales_id_fkey"
    FOREIGN KEY (sales_id) REFERENCES sales(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."webhooks" validate constraint "webhooks_sales_id_fkey";
alter table "public"."webhooks" add constraint "webhooks_created_by_sales_id_fkey"
    FOREIGN KEY (created_by_sales_id) REFERENCES sales(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."webhooks" validate constraint "webhooks_created_by_sales_id_fkey";

-- API Request Logs table
create table "public"."api_logs" (
    "id" bigint generated by default as identity not null,
    "created_at" timestamp with time zone not null default now(),
    "api_key_id" bigint not null,
    "endpoint" text not null,
    "method" text not null,
    "status_code" int not null,
    "response_time_ms" int,
    "ip_address" text,
    "user_agent" text,
    "error_message" text
);

alter table "public"."api_logs" enable row level security;

CREATE UNIQUE INDEX api_logs_pkey ON public.api_logs USING btree (id);
CREATE INDEX api_logs_api_key_id_idx ON public.api_logs USING btree (api_key_id);
CREATE INDEX api_logs_created_at_idx ON public.api_logs USING btree (created_at DESC);

alter table "public"."api_logs" add constraint "api_logs_pkey" PRIMARY KEY using index "api_logs_pkey";
alter table "public"."api_logs" add constraint "api_logs_api_key_id_fkey"
    FOREIGN KEY (api_key_id) REFERENCES api_keys(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."api_logs" validate constraint "api_logs_api_key_id_fkey";

-- Webhook Queue table (for async delivery)
create table "public"."webhook_queue" (
    "id" bigint generated by default as identity not null,
    "created_at" timestamp with time zone not null default now(),
    "webhook_id" bigint not null,
    "event_type" text not null,
    "payload" jsonb not null,
    "status" text not null default 'pending',
    "attempts" int not null default 0,
    "max_attempts" int not null default 3,
    "next_retry_at" timestamp with time zone,
    "delivered_at" timestamp with time zone,
    "error_message" text
);

alter table "public"."webhook_queue" enable row level security;

CREATE UNIQUE INDEX webhook_queue_pkey ON public.webhook_queue USING btree (id);
CREATE INDEX webhook_queue_status_idx ON public.webhook_queue USING btree (status, next_retry_at);
CREATE INDEX webhook_queue_webhook_id_idx ON public.webhook_queue USING btree (webhook_id);

alter table "public"."webhook_queue" add constraint "webhook_queue_pkey" PRIMARY KEY using index "webhook_queue_pkey";
alter table "public"."webhook_queue" add constraint "webhook_queue_webhook_id_fkey"
    FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."webhook_queue" validate constraint "webhook_queue_webhook_id_fkey";

-- Grant permissions
grant select, insert, update, delete on table "public"."api_keys" to "authenticated";
grant select, insert, update, delete on table "public"."webhooks" to "authenticated";
grant select on table "public"."api_logs" to "authenticated";
grant select on table "public"."webhook_queue" to "authenticated";

grant all on table "public"."api_keys" to "service_role";
grant all on table "public"."webhooks" to "service_role";
grant all on table "public"."api_logs" to "service_role";
grant all on table "public"."webhook_queue" to "service_role";

-- RLS Policies (permissive for authenticated users)
create policy "Enable all for authenticated users" on "public"."api_keys"
    for all to authenticated using (true) with check (true);

create policy "Enable all for authenticated users" on "public"."webhooks"
    for all to authenticated using (true) with check (true);

create policy "Enable read for authenticated users" on "public"."api_logs"
    for select to authenticated using (true);

create policy "Enable read for authenticated users" on "public"."webhook_queue"
    for select to authenticated using (true);
