-- Organization table for Norwegian AS companies and international markets
create table public.organization_details (
  -- Primary key and metadata
  id uuid primary key default gen_random_uuid(),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
  
  -- Ownership
  owner_id uuid not null references auth.users(id) on delete cascade,
  
  -- Core organization information
  name text not null,
  organization_number text not null, -- Norwegian organization number (9 digits)
  organization_type text not null default 'AS', -- AS, ASA, NUF, ENK, DA, SA, LLC, Corp, Ltd, etc.
  mva_registered boolean not null default false, -- Company is registered for MVA in Norway
  
  -- Location and jurisdiction
  country_code text not null default 'NO', -- ISO 3166-1 alpha-2 country code
  currency text not null default 'NOK', -- ISO 4217 currency code

  -- Address information
  address_line_1 text,
  address_line_2 text,
  
  -- Additional metadata
  notes text,
  
  -- Constraints
  constraint organization_details_name_length check (char_length(name) >= 1 and char_length(name) <= 255),
  constraint organization_details_organization_number_format check (
    organization_number is null or 
    (country_code = 'NO' and organization_number ~ '^[0-9]{9}$') or
    country_code != 'NO'
  ),
  constraint organization_details_unique_registration unique (organization_number, country_code),
  constraint organization_details_country_code_format check (country_code ~* '^[A-Z]{2}$')
);

-- Indexes for performance
create index idx_organization_details_owner_id on public.organization_details(owner_id);
create index idx_organization_details_organization_number 
  on public.organization_details(organization_number) 
  where organization_number is not null;

-- Trigger to automatically update updated_at timestamp
create or replace function update_updated_at_column()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  new.updated_at = timezone('utc'::text, now());
  return new;
end;
$$;

create trigger update_organization_details_updated_at
  before update on public.organization_details
  for each row
  execute function update_updated_at_column();

-- Row Level Security (RLS) policies
alter table public.organization_details enable row level security;

-- Policy: Users can view their own organization details
create policy "Users can view their own organization details" 
  on public.organization_details
  for select
  to authenticated
  using ((select auth.uid()) = owner_id);

-- Policy: Authenticated users can insert organization details
create policy "Users can create their own organization details" 
  on public.organization_details
  for insert
  to authenticated
  with check ((select auth.uid()) = owner_id);

-- Policy: Users can update their own organization details
create policy "Users can update their own organization details" 
  on public.organization_details
  for update
  to authenticated
  using ((select auth.uid()) = owner_id);

-- Policy: Users can delete their own organization details
create policy "Users can delete their own organization details" 
  on public.organization_details
  for delete
  to authenticated
  using ((select auth.uid()) = owner_id);

-- Comments for documentation
comment on table public.organization_details is 'Organization information for Norwegian AS companies and international markets';
comment on column public.organization_details.owner_id is 'Reference to the authenticated user who owns this organization';
comment on column public.organization_details.organization_number is 'Norwegian organization number (9 digits) or equivalent registration number for other countries';
comment on column public.organization_details.mva_registered is 'MVA registered is used to determine if the company is registered for MVA in Norway';