-- ═══════════════════════════════════════════════════════════
-- TABLE: helpmate_email_templates
-- Reusable HTML email templates for the Helpmate Admin Panel.
-- Shared across API 510, 570 and 653 — scoped by the `app`
-- column (NULL = available to all apps).
-- Images are stored inline as base64 data URIs inside `html`.
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS helpmate_email_templates (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT        NOT NULL,
  subject     TEXT,
  description TEXT,
  html        TEXT        NOT NULL DEFAULT '',
  app         TEXT,       -- NULL = all apps | 'API 510 Helpmate' | etc.
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ── Auto-update updated_at ───────────────────────────────────
CREATE OR REPLACE FUNCTION trg_set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;

DROP TRIGGER IF EXISTS trg_templates_updated_at ON helpmate_email_templates;
CREATE TRIGGER trg_templates_updated_at
  BEFORE UPDATE ON helpmate_email_templates
  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();

-- ── Indexes ──────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_templates_app
  ON helpmate_email_templates (app);
CREATE INDEX IF NOT EXISTS idx_templates_name
  ON helpmate_email_templates (name);

-- ── RLS (permissive — admin panel only) ──────────────────────
ALTER TABLE helpmate_email_templates ENABLE ROW LEVEL SECURITY;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'helpmate_email_templates'
      AND policyname = 'allow_all_helpmate_email_templates'
  ) THEN
    CREATE POLICY allow_all_helpmate_email_templates
      ON helpmate_email_templates FOR ALL USING (true) WITH CHECK (true);
  END IF;
END $$;

-- ── Grants ───────────────────────────────────────────────────
GRANT ALL ON TABLE helpmate_email_templates TO anon;
GRANT ALL ON TABLE helpmate_email_templates TO authenticated;
GRANT ALL ON TABLE helpmate_email_templates TO service_role;

-- ═══════════════════════════════════════════════════════════
-- TABLE: helpmate_tasks
-- Eisenhower Matrix task list for the Helpmate Admin Panel.
-- Shared across all three apps; scoped by the `app` column.
-- ═══════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS helpmate_tasks (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  app         TEXT        NOT NULL,   -- 'API 510 Helpmate' | 'API 570 Helpmate' | 'API 653 Helpmate'
  quadrant    TEXT        NOT NULL    -- 'do' | 'plan' | 'dele' | 'elim'
                CHECK (quadrant IN ('do','plan','dele','elim')),
  text        TEXT        NOT NULL,
  done        BOOLEAN     NOT NULL DEFAULT false,
  sort_order  INTEGER     NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Index for fast per-app lookups
CREATE INDEX IF NOT EXISTS idx_helpmate_tasks_app
  ON helpmate_tasks (app, quadrant, sort_order);

-- Grant table access to Supabase anon and authenticated roles
-- (RLS alone is not enough — Postgres-level GRANT is also required)
GRANT ALL ON TABLE helpmate_tasks TO anon;
GRANT ALL ON TABLE helpmate_tasks TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE helpmate_tasks_id_seq TO anon;
GRANT USAGE, SELECT ON SEQUENCE helpmate_tasks_id_seq TO authenticated;

-- RLS (permissive — matches other Helpmate tables)
ALTER TABLE helpmate_tasks ENABLE ROW LEVEL SECURITY;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies
    WHERE tablename = 'helpmate_tasks' AND policyname = 'allow_all_helpmate_tasks'
  ) THEN
    CREATE POLICY allow_all_helpmate_tasks
      ON helpmate_tasks FOR ALL USING (true) WITH CHECK (true);
  END IF;
END $$;