-- ============================================================
-- COSMOS MERCH — SUPABASE SCHEMA
-- Run this in: Supabase Dashboard → SQL Editor → New Query
-- ============================================================

-- ── PROFILES (extends Supabase auth.users) ───────────────────
CREATE TABLE profiles (
  id       UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  name     TEXT NOT NULL,
  role     TEXT NOT NULL CHECK (role IN ('agent', 'manager', 'admin')),
  active   BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, name, role)
  VALUES (NEW.id, NEW.raw_user_meta_data->>'name', COALESCE(NEW.raw_user_meta_data->>'role', 'agent'));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- ── STORES ───────────────────────────────────────────────────
CREATE TABLE stores (
  code       TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  address    TEXT,
  city       TEXT,
  county     TEXT,
  category   TEXT CHECK (category IN ('A+','A','B','C','D')),
  store_type TEXT CHECK (store_type IN ('Own','Franchise')),
  agent_id   UUID REFERENCES profiles(id),
  lat        DOUBLE PRECISION,
  lng        DOUBLE PRECISION,
  active     BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ── STORE CONTACTS (staff per store) ─────────────────────────
CREATE TABLE store_contacts (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_code TEXT NOT NULL REFERENCES stores(code) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  position   TEXT,
  phone      TEXT,
  active     BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ── PRODUCT CATALOGUE ─────────────────────────────────────────
CREATE TABLE products (
  id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sku      TEXT,
  name     TEXT NOT NULL,
  category TEXT,
  brand    TEXT,
  status   TEXT DEFAULT 'active' CHECK (status IN ('active','eol','new')),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ── VISITS ────────────────────────────────────────────────────
CREATE TABLE visits (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id      UUID NOT NULL REFERENCES profiles(id),
  store_code    TEXT NOT NULL REFERENCES stores(code),
  visited_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  check_in_lat  DOUBLE PRECISION,
  check_in_lng  DOUBLE PRECISION,
  form_data     JSONB NOT NULL DEFAULT '{}',
  score         NUMERIC(5,2),
  status        TEXT DEFAULT 'submitted' CHECK (status IN ('draft','submitted')),
  manager_notes TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── VISIT PHOTOS ──────────────────────────────────────────────
CREATE TABLE visit_photos (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  visit_id     UUID NOT NULL REFERENCES visits(id) ON DELETE CASCADE,
  section_key  TEXT NOT NULL,
  field_id     TEXT NOT NULL,
  storage_path TEXT NOT NULL,
  public_url   TEXT,
  taken_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  lat          DOUBLE PRECISION,
  lng          DOUBLE PRECISION,
  created_at   TIMESTAMPTZ DEFAULT NOW()
);

-- ── ROW LEVEL SECURITY ────────────────────────────────────────
ALTER TABLE profiles      ENABLE ROW LEVEL SECURITY;
ALTER TABLE stores        ENABLE ROW LEVEL SECURITY;
ALTER TABLE store_contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE products      ENABLE ROW LEVEL SECURITY;
ALTER TABLE visits        ENABLE ROW LEVEL SECURITY;
ALTER TABLE visit_photos  ENABLE ROW LEVEL SECURITY;

-- Profiles
CREATE POLICY "profiles_read_all"    ON profiles FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "profiles_update_own"  ON profiles FOR UPDATE USING (auth.uid() = id);

-- Stores
CREATE POLICY "stores_read"          ON stores FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "stores_write_admin"   ON stores FOR ALL    USING (EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role IN ('admin','manager')));

-- Store contacts
CREATE POLICY "contacts_read"        ON store_contacts FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "contacts_write"       ON store_contacts FOR ALL    USING (EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role IN ('admin','manager')));

-- Products
CREATE POLICY "products_read"        ON products FOR SELECT USING (auth.role() = 'authenticated');
CREATE POLICY "products_write"       ON products FOR ALL    USING (EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role IN ('admin','manager')));

-- Visits: agents see own; managers/admins see all
CREATE POLICY "visits_read" ON visits FOR SELECT USING (
  agent_id = auth.uid() OR
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role IN ('manager','admin'))
);
CREATE POLICY "visits_insert" ON visits FOR INSERT WITH CHECK (agent_id = auth.uid());
CREATE POLICY "visits_update_manager" ON visits FOR UPDATE USING (
  EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role IN ('manager','admin'))
);

-- Photos: follow visit access
CREATE POLICY "photos_read" ON visit_photos FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM visits v WHERE v.id = visit_id AND (
      v.agent_id = auth.uid() OR
      EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role IN ('manager','admin'))
    )
  )
);
CREATE POLICY "photos_insert" ON visit_photos FOR INSERT WITH CHECK (
  EXISTS (SELECT 1 FROM visits v WHERE v.id = visit_id AND v.agent_id = auth.uid())
);

-- ── STORAGE BUCKET ────────────────────────────────────────────
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES ('visit-photos', 'visit-photos', false, 10485760, ARRAY['image/jpeg','image/png','image/webp'])
ON CONFLICT DO NOTHING;

CREATE POLICY "photos_upload_auth"  ON storage.objects FOR INSERT WITH CHECK (bucket_id = 'visit-photos' AND auth.role() = 'authenticated');
CREATE POLICY "photos_read_auth"    ON storage.objects FOR SELECT USING  (bucket_id = 'visit-photos' AND auth.role() = 'authenticated');
CREATE POLICY "photos_delete_admin" ON storage.objects FOR DELETE USING  (bucket_id = 'visit-photos' AND EXISTS (SELECT 1 FROM profiles WHERE id = auth.uid() AND role IN ('admin','manager')));

-- ── USEFUL VIEWS ─────────────────────────────────────────────
CREATE VIEW visit_summary AS
SELECT
  v.id,
  v.visited_at,
  v.score,
  v.status,
  v.check_in_lat,
  v.check_in_lng,
  p.name  AS agent_name,
  p.id    AS agent_id,
  s.code  AS store_code,
  s.name  AS store_name,
  s.city,
  s.county,
  s.category,
  s.store_type,
  (SELECT COUNT(*) FROM visit_photos vp WHERE vp.visit_id = v.id) AS photo_count
FROM visits v
JOIN profiles p ON p.id = v.agent_id
JOIN stores   s ON s.code = v.store_code;

-- ── INDEXES ───────────────────────────────────────────────────
CREATE INDEX idx_visits_agent     ON visits(agent_id);
CREATE INDEX idx_visits_store     ON visits(store_code);
CREATE INDEX idx_visits_date      ON visits(visited_at DESC);
CREATE INDEX idx_photos_visit     ON visit_photos(visit_id);
CREATE INDEX idx_contacts_store   ON store_contacts(store_code);

-- ============================================================
-- AFTER RUNNING THIS SCHEMA:
-- 1. Go to Authentication → Users → Add user
-- 2. Create users with these emails and set metadata:
--    { "name": "Michalis Kontos", "role": "admin" }
--    { "name": "Lavinia Vinatoru", "role": "manager" }
--    { "name": "Antonis Lourezos", "role": "manager" }
--    { "name": "Yiannis Hamilos", "role": "manager" }
--    { "name": "Adrian Timaru", "role": "agent" }
--    { "name": "Elena Mitru", "role": "agent" }
--    { "name": "Adrian Epatie", "role": "agent" }
--    { "name": "Andrei Ilinoiu", "role": "agent" }
--    { "name": "Catalin Botusan", "role": "agent" }
--    { "name": "Adrian Capatina", "role": "agent" }
--    { "name": "Roxana Oltean", "role": "agent" }
--    { "name": "Adriana Craciun", "role": "agent" }
--    { "name": "Fabian Ienci", "role": "agent" }
-- 3. Then import stores via Admin panel in the app (CSV upload)
-- ============================================================