-- Create companyNotes table (similar to dealNotes)
create table "public"."companyNotes" (
    "id" bigint generated by default as identity not null,
    "company_id" bigint not null,
    "text" text,
    "date" timestamp with time zone default now(),
    "sales_id" bigint,
    "attachments" jsonb[]
);

-- Primary key
CREATE UNIQUE INDEX "companyNotes_pkey" ON public."companyNotes" USING btree (id);
alter table "public"."companyNotes" add constraint "companyNotes_pkey" PRIMARY KEY using index "companyNotes_pkey";

-- Foreign keys with CASCADE
alter table "public"."companyNotes" add constraint "companyNotes_company_id_fkey"
  FOREIGN KEY (company_id) REFERENCES companies(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."companyNotes" validate constraint "companyNotes_company_id_fkey";

alter table "public"."companyNotes" add constraint "companyNotes_sales_id_fkey"
  FOREIGN KEY (sales_id) REFERENCES sales(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
alter table "public"."companyNotes" validate constraint "companyNotes_sales_id_fkey";

-- RLS policies (permissive)
alter table "public"."companyNotes" enable row level security;

create policy "Enable insert for authenticated users only"
on "public"."companyNotes"
as permissive
for insert
to authenticated
with check (true);

create policy "Enable read access for authenticated users"
on "public"."companyNotes"
as permissive
for select
to authenticated
using (true);

create policy "Company Notes Delete Policy"
on "public"."companyNotes"
as permissive
for delete
to authenticated
using (true);

create policy "Company Notes Update Policy"
on "public"."companyNotes"
as permissive
for update
to authenticated
using (true);

-- Grants
grant delete on table "public"."companyNotes" to "authenticated";
grant insert on table "public"."companyNotes" to "authenticated";
grant select on table "public"."companyNotes" to "authenticated";
grant update on table "public"."companyNotes" to "authenticated";

grant delete on table "public"."companyNotes" to "service_role";
grant insert on table "public"."companyNotes" to "service_role";
grant references on table "public"."companyNotes" to "service_role";
grant select on table "public"."companyNotes" to "service_role";
grant trigger on table "public"."companyNotes" to "service_role";
grant truncate on table "public"."companyNotes" to "service_role";
grant update on table "public"."companyNotes" to "service_role";
