-- Organizations: team collaboration model
-- - Every user belongs to exactly one organization
-- - Tablos are created by users but owned by organizations
-- - Organization members automatically get admin access to organization tablos

-- ============================================================================
-- Organizations
-- ============================================================================

CREATE TABLE IF NOT EXISTS public.organizations (
  id SERIAL PRIMARY KEY,
  internal_uuid uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  name text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

COMMENT ON TABLE public.organizations IS 'Organizations grouping users from the same company for shared tablo access.';
COMMENT ON COLUMN public.organizations.id IS 'Primary key (SERIAL), external references should use this value.';
COMMENT ON COLUMN public.organizations.internal_uuid IS 'Internal backup UUID for organization records.';

DROP TRIGGER IF EXISTS update_organizations_updated_at ON public.organizations;
CREATE TRIGGER update_organizations_updated_at
  BEFORE UPDATE ON public.organizations
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

-- Random two-word English organization names
CREATE OR REPLACE FUNCTION public.generate_cool_organization_name() RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  adjectives text[] := ARRAY[
    'Amber', 'Arctic', 'Atlas', 'Azure', 'Bold', 'Bright', 'Cobalt', 'Cosmic',
    'Crisp', 'Dynamic', 'Electric', 'Emerald', 'Epic', 'Golden', 'Grand', 'Iron',
    'Ivory', 'Lunar', 'Modern', 'Nova', 'Ocean', 'Orbit', 'Prime', 'Quantum',
    'Rapid', 'Royal', 'Silver', 'Solar', 'Summit', 'Swift', 'Ultra', 'Urban', 'Velvet'
  ];
  nouns text[] := ARRAY[
    'Bridge', 'Cloud', 'Collective', 'Compass', 'Craft', 'Forge', 'Frontier', 'Harbor',
    'Horizon', 'House', 'Labs', 'Line', 'Logic', 'Matrix', 'Minds', 'Network',
    'Nexus', 'Peak', 'Pioneer', 'Pulse', 'Reach', 'Rocket', 'Scope', 'Signal',
    'Spark', 'Sphere', 'Stack', 'Studio', 'Systems', 'Tower', 'Ventures', 'Works', 'Yard'
  ];
BEGIN
  RETURN
    adjectives[1 + floor(random() * array_length(adjectives, 1))::integer]
    || ' ' ||
    nouns[1 + floor(random() * array_length(nouns, 1))::integer];
END;
$$;

COMMENT ON FUNCTION public.generate_cool_organization_name() IS 'Returns a random two-word English organization name.';

CREATE OR REPLACE FUNCTION public.create_personal_organization() RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  new_org_id integer;
BEGIN
  INSERT INTO public.organizations (name)
  VALUES (public.generate_cool_organization_name())
  RETURNING id INTO new_org_id;

  RETURN new_org_id;
END;
$$;

ALTER FUNCTION public.create_personal_organization() OWNER TO postgres;

-- ============================================================================
-- Profiles: one organization per user
-- ============================================================================

ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS organization_id integer;

-- Existing users: create one personal organization each
UPDATE public.profiles
SET organization_id = public.create_personal_organization()
WHERE organization_id IS NULL;

-- Ensure future profile inserts always receive an organization
CREATE OR REPLACE FUNCTION public.ensure_profile_organization() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  IF NEW.organization_id IS NULL THEN
    NEW.organization_id := public.create_personal_organization();
  END IF;

  RETURN NEW;
END;
$$;

ALTER FUNCTION public.ensure_profile_organization() OWNER TO postgres;

DROP TRIGGER IF EXISTS trigger_ensure_profile_organization ON public.profiles;
CREATE TRIGGER trigger_ensure_profile_organization
  BEFORE INSERT ON public.profiles
  FOR EACH ROW
  EXECUTE FUNCTION public.ensure_profile_organization();

ALTER TABLE public.profiles
  ALTER COLUMN organization_id SET NOT NULL;

ALTER TABLE public.profiles
  DROP CONSTRAINT IF EXISTS profiles_organization_id_fkey;

ALTER TABLE public.profiles
  ADD CONSTRAINT profiles_organization_id_fkey
  FOREIGN KEY (organization_id)
  REFERENCES public.organizations(id)
  ON DELETE RESTRICT;

CREATE INDEX IF NOT EXISTS profiles_organization_id_idx ON public.profiles (organization_id);

COMMENT ON COLUMN public.profiles.organization_id IS 'Organization the user belongs to. A user can only belong to one organization.';

-- ============================================================================
-- Tablos: owned by organizations
-- ============================================================================

ALTER TABLE public.tablos
  ADD COLUMN IF NOT EXISTS organization_id integer;

-- Backfill existing tablos using owner's organization
UPDATE public.tablos t
SET organization_id = p.organization_id
FROM public.profiles p
WHERE p.id = t.owner_id
  AND t.organization_id IS NULL;

-- Safety net for legacy inconsistencies: some historical tablos can reference
-- owners that do not have a profile row anymore. Assign these orphaned rows to
-- a dedicated recovery organization so the NOT NULL constraint can be applied.
DO $$
DECLARE
  recovery_organization_id integer;
  has_notify_trigger boolean;
BEGIN
  IF EXISTS (SELECT 1 FROM public.tablos WHERE organization_id IS NULL) THEN
    INSERT INTO public.organizations (name)
    VALUES ('Recovered Legacy Workspace')
    RETURNING id INTO recovery_organization_id;

    SELECT EXISTS (
      SELECT 1
      FROM pg_trigger
      WHERE tgrelid = 'public.tablos'::regclass
        AND tgname = 'notify_users_on_tablos'
        AND NOT tgisinternal
    ) INTO has_notify_trigger;

    BEGIN
      IF has_notify_trigger THEN
        EXECUTE 'ALTER TABLE public.tablos DISABLE TRIGGER notify_users_on_tablos';
      END IF;

      UPDATE public.tablos t
      SET organization_id = recovery_organization_id
      WHERE t.organization_id IS NULL;

      IF has_notify_trigger THEN
        EXECUTE 'ALTER TABLE public.tablos ENABLE TRIGGER notify_users_on_tablos';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        IF has_notify_trigger THEN
          EXECUTE 'ALTER TABLE public.tablos ENABLE TRIGGER notify_users_on_tablos';
        END IF;
        RAISE;
    END;
  END IF;
END;
$$;

-- Ensure future tablos inherit owner's organization when omitted
CREATE OR REPLACE FUNCTION public.set_tablo_organization_from_owner() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  owner_organization_id integer;
BEGIN
  IF NEW.organization_id IS NULL THEN
    SELECT p.organization_id
    INTO owner_organization_id
    FROM public.profiles p
    WHERE p.id = NEW.owner_id
    LIMIT 1;

    IF owner_organization_id IS NULL THEN
      RAISE EXCEPTION USING
        ERRCODE = '23503',
        MESSAGE = 'Tablo owner has no organization';
    END IF;

    NEW.organization_id := owner_organization_id;
  END IF;

  RETURN NEW;
END;
$$;

ALTER FUNCTION public.set_tablo_organization_from_owner() OWNER TO postgres;

DROP TRIGGER IF EXISTS trigger_set_tablo_organization ON public.tablos;
CREATE TRIGGER trigger_set_tablo_organization
  BEFORE INSERT ON public.tablos
  FOR EACH ROW
  EXECUTE FUNCTION public.set_tablo_organization_from_owner();

ALTER TABLE public.tablos
  ALTER COLUMN organization_id SET NOT NULL;

ALTER TABLE public.tablos
  DROP CONSTRAINT IF EXISTS tablos_organization_id_fkey;

ALTER TABLE public.tablos
  ADD CONSTRAINT tablos_organization_id_fkey
  FOREIGN KEY (organization_id)
  REFERENCES public.organizations(id)
  ON DELETE RESTRICT;

CREATE INDEX IF NOT EXISTS tablos_organization_id_idx ON public.tablos (organization_id);

COMMENT ON COLUMN public.tablos.organization_id IS 'Organization that owns the tablo.';

-- ============================================================================
-- Access synchronization for organization members
-- ============================================================================

-- Replace owner-only access creation with org-wide admin access creation
CREATE OR REPLACE FUNCTION public.create_tablo_access_for_owner() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO public.tablo_access (
    tablo_id,
    user_id,
    granted_by,
    is_active,
    is_admin
  )
  SELECT
    NEW.id,
    p.id,
    NEW.owner_id,
    TRUE,
    TRUE
  FROM public.profiles p
  WHERE p.organization_id = NEW.organization_id
  ON CONFLICT (tablo_id, user_id)
  DO UPDATE SET
    is_active = TRUE,
    is_admin = TRUE,
    granted_by = EXCLUDED.granted_by;

  RETURN NEW;
END;
$$;

ALTER FUNCTION public.create_tablo_access_for_owner() OWNER TO postgres;

-- Existing data: ensure every org member has admin access to org tablos
INSERT INTO public.tablo_access (
  tablo_id,
  user_id,
  granted_by,
  is_active,
  is_admin
)
SELECT
  t.id,
  p.id,
  t.owner_id,
  TRUE,
  TRUE
FROM public.tablos t
JOIN public.profiles p
  ON p.organization_id = t.organization_id
WHERE t.deleted_at IS NULL
ON CONFLICT (tablo_id, user_id)
DO UPDATE SET
  is_active = TRUE,
  is_admin = TRUE,
  granted_by = EXCLUDED.granted_by;

-- When a user joins/moves organization, sync tablo_access automatically
CREATE OR REPLACE FUNCTION public.sync_org_member_tablo_access() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  IF TG_OP = 'UPDATE' AND NEW.organization_id IS DISTINCT FROM OLD.organization_id THEN
    UPDATE public.tablo_access ta
    SET is_active = FALSE
    WHERE ta.user_id = NEW.id
      AND ta.is_admin = TRUE
      AND EXISTS (
        SELECT 1
        FROM public.tablos t
        WHERE t.id = ta.tablo_id
          AND t.organization_id = OLD.organization_id
      );
  END IF;

  INSERT INTO public.tablo_access (
    tablo_id,
    user_id,
    granted_by,
    is_active,
    is_admin
  )
  SELECT
    t.id,
    NEW.id,
    NEW.id,
    TRUE,
    TRUE
  FROM public.tablos t
  WHERE t.organization_id = NEW.organization_id
    AND t.deleted_at IS NULL
  ON CONFLICT (tablo_id, user_id)
  DO UPDATE SET
    is_active = TRUE,
    is_admin = TRUE,
    granted_by = EXCLUDED.granted_by;

  RETURN NEW;
END;
$$;

ALTER FUNCTION public.sync_org_member_tablo_access() OWNER TO postgres;

DROP TRIGGER IF EXISTS trigger_sync_org_member_tablo_access ON public.profiles;
CREATE TRIGGER trigger_sync_org_member_tablo_access
  AFTER INSERT OR UPDATE OF organization_id ON public.profiles
  FOR EACH ROW
  EXECUTE FUNCTION public.sync_org_member_tablo_access();

-- ============================================================================
-- RLS updates
-- ============================================================================

CREATE OR REPLACE FUNCTION public.current_user_organization_id() RETURNS integer
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT p.organization_id
  FROM public.profiles p
  WHERE p.id = auth.uid();
$$;

ALTER FUNCTION public.current_user_organization_id() OWNER TO postgres;

-- Organizations RLS
ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view their own organization" ON public.organizations;
CREATE POLICY "Users can view their own organization"
  ON public.organizations
  FOR SELECT
  TO authenticated
  USING (id = public.current_user_organization_id());

DROP POLICY IF EXISTS "Users can update their own organization" ON public.organizations;
CREATE POLICY "Users can update their own organization"
  ON public.organizations
  FOR UPDATE
  TO authenticated
  USING (id = public.current_user_organization_id())
  WITH CHECK (id = public.current_user_organization_id());

-- Tablos RLS: organization members have owner-level access
DROP POLICY IF EXISTS "Users can view tablos they have access to" ON public.tablos;
CREATE POLICY "Users can view tablos they have access to"
  ON public.tablos
  FOR SELECT
  TO authenticated
  USING (
    organization_id = public.current_user_organization_id()
    OR EXISTS (
      SELECT 1
      FROM public.tablo_access ta
      WHERE ta.tablo_id = tablos.id
        AND ta.user_id = auth.uid()
        AND ta.is_active = TRUE
    )
  );

DROP POLICY IF EXISTS "Users can insert own tablos" ON public.tablos;
CREATE POLICY "Users can insert own tablos"
  ON public.tablos
  FOR INSERT
  TO authenticated
  WITH CHECK (
    owner_id = auth.uid()
    AND (
      organization_id = public.current_user_organization_id()
      OR organization_id IS NULL
    )
  );

DROP POLICY IF EXISTS "Users can update own tablos" ON public.tablos;
CREATE POLICY "Users can update own tablos"
  ON public.tablos
  FOR UPDATE
  TO authenticated
  USING (organization_id = public.current_user_organization_id())
  WITH CHECK (organization_id = public.current_user_organization_id());

-- Tasks RLS already relies on tablo_access membership. Org-level tablo_access sync above
-- gives all organization members full access (admin) to organization tasks.

-- ============================================================================
-- Plan aliases for new billing model
-- ============================================================================

ALTER TYPE public.subscription_plan ADD VALUE IF NOT EXISTS 'solo';
ALTER TYPE public.subscription_plan ADD VALUE IF NOT EXISTS 'team';
ALTER TYPE public.subscription_plan ADD VALUE IF NOT EXISTS 'annual';

COMMENT ON TYPE public.subscription_plan IS 'Billing plans: solo (1 user, 10 tablos), team (3 users, unlimited tablos), annual (unlimited). Legacy values are kept for compatibility.';

GRANT SELECT, UPDATE ON public.organizations TO authenticated;
GRANT EXECUTE ON FUNCTION public.current_user_organization_id() TO authenticated;
