-- Etsy Workflowmanager Schema mit Login-Schutz (RLS)

create table if not exists products (
  id bigint primary key,
  name text not null,
  cat text,
  status text default 'entwurf',
  price text,
  beschreibung text,
  url text, -- öffentlicher Etsy-Listing-Link (Kaufen-Link, für Pins/Marketing)
  etsy_edit_url text, -- privater Etsy-Shop-Manager-Link (nur zum Bearbeiten des Listings, nicht teilen)
  eigene_url text,
  has_img boolean default false,
  bewertungen integer default 0,
  preis_history jsonb default '[]',
  workflow_steps jsonb default '{}',
  ab_notiz text,
  etsy_stats jsonb default '{}',
  markt_analyse jsonb default '[]',
  ai jsonb default '{}',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table if not exists plan (
  id bigserial primary key,
  date text,
  title text,
  board text,
  type text,
  url text,
  status text default 'Entwurf',
  prod_id bigint,
  image_id bigint, -- bei Carousel-Pins: erstes/Titel-Bild, weitere in plan_bilder
  pin_titel text,
  pin_beschreibung text,
  etsy_url text,
  created_at timestamptz default now()
);

-- F32 (9.7.2026): mehrere Bilder pro Pin für Carousel-Pins. Bewusst als eigene
-- Zwischentabelle statt image_id in plan zum Array umzubauen — image_id wird an
-- vielen bestehenden Stellen als Einzelwert erwartet (Tutorial/Lifestyle-Pins etc.),
-- die sollen unverändert funktionieren. Nur Carousel-Pins nutzen diese Tabelle zusätzlich.
create table if not exists plan_bilder (
  id bigserial primary key,
  plan_id bigint not null references plan(id) on delete cascade,
  image_id bigint not null,
  reihenfolge integer default 0,
  created_at timestamptz default now()
);

create table if not exists boards (
  id bigserial primary key,
  name text not null,
  beschreibung text, -- z.B. "Hauptboard · Priorität 1"
  sortierung integer default 0,
  -- pinterest_status: 'ungeprueft'|'existiert'|'fehlt' — Live-Check folgt mit OAuth (F24b, Teil 2)
  pinterest_status text default 'ungeprueft',
  pinterest_geprueft_am timestamptz,
  created_at timestamptz default now()
);

create table if not exists settings (
  key text primary key,
  value text
);

create table if not exists etsy_links (
  id bigserial primary key,
  name text,
  url text,
  created_at timestamptz default now()
);

create table if not exists images (
  id bigserial primary key,
  prod_id bigint references products(id) on delete cascade,
  storage_path text not null,
  public_url text,
  typ text default 'titelbild',
  breite integer,
  hoehe integer,
  ratio_ok boolean default false,
  motiv_bezeichnung text,
  notiz text,
  created_at timestamptz default now()
);

-- Falls die Tabelle schon vor diesem Update existierte: Spalten nachträglich ergänzen
alter table images add column if not exists motiv_bezeichnung text;
alter table images add column if not exists notiz text;
alter table products add column if not exists saison_key text; -- z.B. 'weihnachten', 'schulanfang'

create table if not exists ideas (
  id bigint primary key,
  name text not null,
  notiz text,
  status text default 'neu',
  bewertung jsonb default '{}',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

create table if not exists tasks (
  id bigserial primary key,
  datum text not null,
  zeit text,
  titel text not null,
  typ text default 'aufgabe',
  dauer_min integer default 30,
  erledigt boolean default false,
  ref_id bigint,
  notiz text,
  created_at timestamptz default now()
);

-- ===== VEREINHEITLICHTER KALENDER: events ersetzt plan+tasks =====
-- Eine zentrale Tabelle für ALLE Termine: Pins, Aufgaben, Steuertermine, Produktentwicklung,
-- Wartung, feste Termine, Saisonanlässe. Filter über "kategorie" statt getrennter Tabellen.
create table if not exists events (
  id bigserial primary key,
  titel text not null,
  kategorie text not null default 'task', -- pin|task|steuer|produktentwicklung|wartung|fix_termin|saison_anlass
  datum_start date not null,
  datum_ende date, -- leer = Einzeltermin, gesetzt = Zeitraum
  zeit_start text, -- z.B. "14:00", leer = ganztägig
  zeit_ende text,
  dauer_min integer,
  dringlichkeit text default 'verschiebbar', -- fix|wichtig|verschiebbar
  status text default 'offen', -- offen|in_arbeit|erledigt
  quelle text default 'manuell', -- manuell|berechnet|ki_vorschlag
  prod_id bigint,
  image_id bigint,
  board text,
  pin_titel text,
  pin_beschreibung text,
  etsy_url text,
  betrag numeric,
  vorlauf_tage integer,
  wiederholt_jaehrlich boolean default false,
  ref_key text, -- eindeutiger Schlüssel für berechnete Einträge (z.B. 'ostern_2026'), verhindert Duplikate
  notiz text,
  wf_step integer, -- F22: Verknüpfung zum Produkt-Workflow-Schritt (1-7), null = kein Workflow-Bezug
  -- Google Calendar Sync (für späteren Schritt vorbereitet, aktuell ungenutzt)
  external_source text, -- google_calendar|ics_import|null
  external_id text,
  external_updated_at timestamptz,
  sync_status text default 'nur_lokal', -- synced|lokal_geaendert|konflikt|nur_lokal
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);
create unique index if not exists events_ref_key_unique on events(ref_key); -- KEIN partial index (WHERE) — supabase-js/PostgREST kann WHERE-Klauseln bei upsert(onConflict) nicht mitsenden (bekannter Bug: github.com/supabase/postgrest-js/issues/403)

create table if not exists event_reminders (
  id bigserial primary key,
  event_id bigint references events(id) on delete cascade,
  vorlauf_minuten integer not null,
  kanal text default 'app_notification', -- app_notification|google_sync
  ausgeloest boolean default false,
  created_at timestamptz default now()
);

create table if not exists event_attachments (
  id bigserial primary key,
  event_id bigint references events(id) on delete cascade,
  storage_path text not null, -- bei typ='link': die URL direkt
  dateiname text,
  typ text default 'pdf', -- pdf|bild|link
  created_at timestamptz default now()
);

-- Storage Bucket fuer Bilder (oeffentlich, damit Pinterest sie laden kann)
insert into storage.buckets (id, name, public)
values ('product-images', 'product-images', true)
on conflict (id) do update set public = true;

-- ===== ROW LEVEL SECURITY: nur eingeloggte Nutzer =====
alter table products enable row level security;
alter table plan enable row level security;
alter table settings enable row level security;
alter table etsy_links enable row level security;
alter table images enable row level security;
alter table ideas enable row level security;
alter table tasks enable row level security;
alter table events enable row level security;
alter table event_reminders enable row level security;
alter table event_attachments enable row level security;

-- Je eine Policy pro Tabelle: erlaubt alles fuer jeden eingeloggten Nutzer
create policy "Eingeloggte Nutzer: alles erlaubt" on products
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on plan
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on settings
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on etsy_links
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on images
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on ideas
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on tasks
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on events
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on event_reminders
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on event_attachments
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');

-- Storage: nur eingeloggte Nutzer duerfen hochladen/loeschen, Lesen bleibt oeffentlich (fuer Pinterest)
create policy "Oeffentlich lesbar" on storage.objects
  for select using (bucket_id = 'product-images');
create policy "Eingeloggte Nutzer duerfen hochladen" on storage.objects
  for insert with check (bucket_id = 'product-images' and auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer duerfen loeschen" on storage.objects
  for delete using (bucket_id = 'product-images' and auth.role() = 'authenticated');

-- ===== NEUE TABELLEN (Kalender-Update) =====
-- NUR ausführen wenn diese Tabellen noch nicht existieren.
-- Falls du das komplette Schema oben bereits ausgeführt hast und den Fehler
-- "policy already exists" bekommst, führe NUR diesen Abschnitt aus.

create table if not exists events (
  id bigserial primary key,
  titel text not null,
  kategorie text not null default 'task',
  datum_start date not null,
  datum_ende date,
  zeit_start text,
  zeit_ende text,
  dauer_min integer,
  dringlichkeit text default 'verschiebbar',
  status text default 'offen',
  quelle text default 'manuell',
  prod_id bigint,
  image_id bigint,
  board text,
  pin_titel text,
  pin_beschreibung text,
  etsy_url text,
  betrag numeric,
  vorlauf_tage integer,
  wiederholt_jaehrlich boolean default false,
  ref_key text,
  notiz text,
  external_source text,
  external_id text,
  external_updated_at timestamptz,
  sync_status text default 'nur_lokal',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);
create unique index if not exists events_ref_key_unique on events(ref_key); -- KEIN partial index (WHERE) — supabase-js/PostgREST kann WHERE-Klauseln bei upsert(onConflict) nicht mitsenden (bekannter Bug: github.com/supabase/postgrest-js/issues/403)

create table if not exists event_reminders (
  id bigserial primary key,
  event_id bigint references events(id) on delete cascade,
  vorlauf_minuten integer not null,
  kanal text default 'app_notification',
  ausgeloest boolean default false,
  created_at timestamptz default now()
);

create table if not exists event_attachments (
  id bigserial primary key,
  event_id bigint references events(id) on delete cascade,
  storage_path text not null,
  dateiname text,
  typ text default 'pdf',
  created_at timestamptz default now()
);

alter table images add column if not exists motiv_bezeichnung text;
alter table images add column if not exists notiz text;

alter table events enable row level security;
alter table event_reminders enable row level security;
alter table event_attachments enable row level security;

create policy "Eingeloggte Nutzer: alles erlaubt" on events
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on event_reminders
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on event_attachments
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');

-- Optimierung 5: Indizes für schnelle Kalender-Abfragen
-- Ohne diese macht Supabase bei jeder Kalenderansicht einen Full-Table-Scan.
create index if not exists events_datum_start_idx on events(datum_start);
create index if not exists events_kategorie_idx on events(kategorie);
create index if not exists events_datum_kategorie_idx on events(datum_start, kategorie);

-- === Update v1.1.0 → v1.2.0 ===
-- Vereinheitlichter Kalender: events, event_reminders, event_attachments

create table if not exists events (
  id bigserial primary key,
  titel text not null,
  kategorie text not null default 'task',
  datum_start date not null,
  datum_ende date, zeit_start text, zeit_ende text, dauer_min integer,
  dringlichkeit text default 'verschiebbar',
  status text default 'offen', quelle text default 'manuell',
  prod_id bigint, image_id bigint, board text, pin_titel text,
  pin_beschreibung text, etsy_url text, betrag numeric, vorlauf_tage integer,
  wiederholt_jaehrlich boolean default false, ref_key text, notiz text,
  external_source text, external_id text, external_updated_at timestamptz,
  sync_status text default 'nur_lokal',
  created_at timestamptz default now(), updated_at timestamptz default now()
);
create unique index if not exists events_ref_key_unique on events(ref_key); -- KEIN partial index (WHERE) — supabase-js/PostgREST kann WHERE-Klauseln bei upsert(onConflict) nicht mitsenden (bekannter Bug: github.com/supabase/postgrest-js/issues/403)
create table if not exists event_reminders (
  id bigserial primary key,
  event_id bigint references events(id) on delete cascade,
  vorlauf_minuten integer not null, kanal text default 'app_notification',
  ausgeloest boolean default false, created_at timestamptz default now()
);
create table if not exists event_attachments (
  id bigserial primary key,
  event_id bigint references events(id) on delete cascade,
  storage_path text not null, dateiname text, typ text default 'pdf',
  created_at timestamptz default now()
);
alter table images add column if not exists motiv_bezeichnung text;
alter table images add column if not exists notiz text;
alter table events enable row level security;
alter table event_reminders enable row level security;
alter table event_attachments enable row level security;
create policy "Eingeloggte Nutzer: alles erlaubt" on events
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on event_reminders
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create policy "Eingeloggte Nutzer: alles erlaubt" on event_attachments
  for all using (auth.role() = 'authenticated') with check (auth.role() = 'authenticated');
create index if not exists events_datum_start_idx on events(datum_start);
create index if not exists events_kategorie_idx on events(kategorie);
create index if not exists events_datum_kategorie_idx on events(datum_start, kategorie);

-- === Update v1.2.0 → v1.3.0 ===
-- Saison-Key für Produkte (Kategorie als Standard, manuell überschreibbar)
alter table products add column if not exists saison_key text;

-- === Update v2.1.2 → v2.1.3 (KRITISCHER FIX) ===
-- Ursache für leeren Kalender gefunden: events_ref_key_unique war ein PARTIAL INDEX
-- (mit WHERE ref_key is not null). supabase-js/PostgREST kann bei upsert(onConflict:...)
-- keine WHERE-Klausel mitsenden — das ist ein seit 2023 bekannter, ungelöster Bug im
-- Supabase-Client selbst (github.com/supabase/postgrest-js/issues/403), kein Fehler in
-- deiner Datenbank. Lösung: Index OHNE WHERE-Klausel neu anlegen. Das ist unbedenklich:
-- Postgres behandelt NULL-Werte in UNIQUE-Indizes standardmäßig immer als verschieden
-- voneinander, ein normaler Index erlaubt also weiterhin beliebig viele Events ohne
-- ref_key (z.B. deine manuell angelegten Termine).
drop index if exists events_ref_key_unique;
create unique index if not exists events_ref_key_unique on events(ref_key);

-- === Update v2.2.1 → v2.3.0 (F22) ===
-- Bidirektionale Workflow-Kalender-Synchronisation: Termin ↔ Produkt-Workflow-Schritt
alter table events add column if not exists wf_step integer;

-- === Update v2.4.0 → v2.5.0 (F24b Teil 1) ===
-- Eigene, editierbare Board-Verwaltung statt hartcodierter Beispiel-Boardnamen
create table if not exists boards (
  id bigserial primary key,
  name text not null,
  beschreibung text,
  sortierung integer default 0,
  pinterest_status text default 'ungeprueft',
  pinterest_geprueft_am timestamptz,
  created_at timestamptz default now()
);

-- === Update v2.5.1 → v2.6.0 ===
-- Trennung privater Bearbeiten-Link / öffentlicher Etsy-Listing-Link,
-- Status wechselt automatisch auf 'live' sobald der öffentliche Link gesetzt wird
alter table products add column if not exists etsy_edit_url text;

-- === Update v2.6.0 → v2.7.0 (F32) ===
-- Carousel-Pins: mehrere Bilder pro Pin, eigene Tagesziel-Kennzahl
create table if not exists plan_bilder (
  id bigserial primary key,
  plan_id bigint not null references plan(id) on delete cascade,
  image_id bigint not null,
  reihenfolge integer default 0,
  created_at timestamptz default now()
);