-- Initial schema migration (schema-only)
-- Idempotent where possible. Apply on a fresh Supabase project.
-- NOTE: Review and adjust before applying to production.

BEGIN;

-- Ensure pgcrypto for gen_random_uuid
CREATE EXTENSION IF NOT EXISTS pgcrypto;

---------------------------------------------------------------------
-- PUBLIC SCHEMA: Core application tables
---------------------------------------------------------------------

-- ideas
CREATE TABLE IF NOT EXISTS public.ideas (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid,
  author_id uuid,
  title text,
  description text,
  status text DEFAULT 'draft',
  metadata jsonb DEFAULT '{}'::jsonb,
  created_at timestamptz DEFAULT now()
);

-- plans
CREATE TABLE IF NOT EXISTS public.plans (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid,
  idea_id uuid,
  title text,
  description text,
  outcome text,
  owner_id uuid,
  status text,
  priority integer,
  start_date date,
  due_date date,
  metadata jsonb DEFAULT '{}'::jsonb,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz
);

-- plan_artifacts
CREATE TABLE IF NOT EXISTS public.plan_artifacts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id uuid,
  artifact_type text,
  content jsonb,
  created_at timestamptz DEFAULT now()
);

-- plan_updates
CREATE TABLE IF NOT EXISTS public.plan_updates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id uuid,
  author_id uuid,
  body text,
  metadata jsonb DEFAULT '{}'::jsonb,
  created_at timestamptz DEFAULT now()
);

-- schedules
CREATE TABLE IF NOT EXISTS public.schedules (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id uuid,
  cadence text,
  next_run timestamptz,
  metadata jsonb DEFAULT '{}'::jsonb,
  created_at timestamptz DEFAULT now()
);

---------------------------------------------------------------------
-- OPTIONAL: Foreign keys (commented out by default)
-- Enable if referenced tables exist and you want enforced constraints.
---------------------------------------------------------------------
-- ALTER TABLE public.plans
--   ADD CONSTRAINT fk_plans_ideas FOREIGN KEY (idea_id) REFERENCES public.ideas(id) ON DELETE SET NULL;

-- ALTER TABLE public.plan_artifacts
--   ADD CONSTRAINT fk_artifacts_plans FOREIGN KEY (plan_id) REFERENCES public.plans(id) ON DELETE CASCADE;

-- ALTER TABLE public.plan_updates
--   ADD CONSTRAINT fk_updates_plans FOREIGN KEY (plan_id) REFERENCES public.plans(id) ON DELETE CASCADE;

-- ALTER TABLE public.schedules
--   ADD CONSTRAINT fk_schedules_plans FOREIGN KEY (plan_id) REFERENCES public.plans(id) ON DELETE CASCADE;

---------------------------------------------------------------------
-- Indexes
---------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_plans_tenant ON public.plans (tenant_id);
CREATE INDEX IF NOT EXISTS idx_ideas_tenant ON public.ideas (tenant_id);
CREATE INDEX IF NOT EXISTS idx_plan_artifacts_plan ON public.plan_artifacts (plan_id);
CREATE INDEX IF NOT EXISTS idx_plan_updates_plan ON public.plan_updates (plan_id);
CREATE INDEX IF NOT EXISTS idx_schedules_plan ON public.schedules (plan_id);

---------------------------------------------------------------------
-- EXAMPLE: Row Level Security templates (disabled; enable when ready)
---------------------------------------------------------------------
-- ALTER TABLE public.ideas ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY ideas_owner_select ON public.ideas
--   FOR SELECT TO authenticated
--   USING ((SELECT auth.uid()) = author_id);

-- ALTER TABLE public.plans ENABLE ROW LEVEL SECURITY;
-- CREATE POLICY plans_tenant_access ON public.plans
--   FOR ALL TO authenticated
--   USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
--   WITH CHECK (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

---------------------------------------------------------------------
-- Example helper function (security definer) for complex checks
---------------------------------------------------------------------
/* If you plan to use helper functions in RLS, create them as SECURITY DEFINER
   and then REVOKE EXECUTE from anon/authenticated as appropriate.
*/
CREATE OR REPLACE FUNCTION public.get_user_tenant()
RETURNS uuid
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT tenant_id FROM public.user_profiles WHERE auth_user_id = auth.uid() LIMIT 1;
$$;

-- Don't allow public execution by default (run after you create user_profiles)
-- REVOKE EXECUTE ON FUNCTION public.get_user_tenant() FROM anon, authenticated;

---------------------------------------------------------------------
-- Example: functions, triggers, or realtime helpers can be added below
---------------------------------------------------------------------
-- Example trigger function template for broadcasting changes:
-- CREATE OR REPLACE FUNCTION public.broadcast_changes_trigger()
-- RETURNS TRIGGER AS $$
-- BEGIN
--   PERFORM realtime.broadcast_changes(
--     'plans:' || COALESCE(NEW.id, OLD.id)::text,
--     TG_OP,
--     TG_OP,
--     TG_TABLE_NAME,
--     TG_TABLE_SCHEMA,
--     NEW,
--     OLD
--   );
--   RETURN COALESCE(NEW, OLD);
-- END;
-- $$ LANGUAGE plpgsql SECURITY DEFINER;

COMMIT;
