/*
# Queneva core schema: leads, blog posts, portfolio, testimonials, careers, visitors

1. New Tables
- `leads` — contact-form submissions / CRM leads. Public INSERT, authenticated
  SELECT/UPDATE/DELETE. Columns: lead_id, full_name, company_name, email, phone,
  city, country, service_required, budget, message, preferred_contact_time,
  status, priority, notes, assigned_to, ip_address, user_agent, submitted_at.
- `blog_posts` — CMS blog content with draft/publish/schedule. Columns: title,
  slug, excerpt, content, cover_image, category, tags, author, status, featured,
  published_at, seo_title, seo_description, views.
- `portfolios` — case-study showcase. Columns: title, slug, client, category,
  description, image_url, technologies, results (jsonb), url, featured.
- `testimonials` — client testimonials. Columns: client_name, company, role,
  content, rating, avatar_url, featured.
- `career_applications` — job applications. Columns: full_name, email, phone,
  position, cover_letter, resume_url, experience, status.
- `site_visitors` — single-row visitor counter (id fixed at gen_random_uuid(),
  constrained to one row via a sentinel column).

2. Security (RLS)
- `leads`: anon + authenticated INSERT; authenticated-only SELECT/UPDATE/DELETE.
- `blog_posts`, `portfolios`, `testimonials`: SELECT public; writes authenticated.
- `career_applications`: anon + authenticated INSERT; reads/updates authenticated.
- `site_visitors`: SELECT public; UPDATE authenticated. Increment via
  SECURITY DEFINER RPC `increment_visitor_count()`.

3. Functions
- `increment_visitor_count()` — SECURITY DEFINER so anon can bump the counter.
- `set_updated_at()` — trigger helper for updated_at columns.

4. Notes
- No auth.users linkage; admin auth handled separately via Supabase Auth.
- 4 policies per table (no FOR ALL).
*/

-- ---------- leads ----------
CREATE TABLE IF NOT EXISTS leads (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id text NOT NULL UNIQUE,
  full_name text NOT NULL,
  company_name text,
  email text NOT NULL,
  phone text,
  city text,
  country text,
  service_required text,
  budget text,
  message text,
  preferred_contact_time text,
  status text NOT NULL DEFAULT 'new' CHECK (status IN ('new','contacted','qualified','proposal_sent','won','lost','follow_up')),
  priority text NOT NULL DEFAULT 'medium' CHECK (priority IN ('low','medium','high','urgent')),
  notes text,
  assigned_to text,
  ip_address text,
  user_agent text,
  submitted_at timestamptz DEFAULT now(),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_created_at ON leads(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_leads_email ON leads(email);

ALTER TABLE leads ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "public_insert_leads" ON leads;
CREATE POLICY "public_insert_leads" ON leads FOR INSERT
  TO anon, authenticated WITH CHECK (true);

DROP POLICY IF EXISTS "auth_select_leads" ON leads;
CREATE POLICY "auth_select_leads" ON leads FOR SELECT
  TO authenticated USING (true);

DROP POLICY IF EXISTS "auth_update_leads" ON leads;
CREATE POLICY "auth_update_leads" ON leads FOR UPDATE
  TO authenticated USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "auth_delete_leads" ON leads;
CREATE POLICY "auth_delete_leads" ON leads FOR DELETE
  TO authenticated USING (true);

-- ---------- blog_posts ----------
CREATE TABLE IF NOT EXISTS blog_posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  slug text NOT NULL UNIQUE,
  excerpt text,
  content text NOT NULL DEFAULT '',
  cover_image text,
  category text,
  tags text[] DEFAULT '{}',
  author text NOT NULL DEFAULT 'Queneva Team',
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','published','scheduled')),
  featured boolean NOT NULL DEFAULT false,
  published_at timestamptz,
  seo_title text,
  seo_description text,
  views integer NOT NULL DEFAULT 0,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_blog_posts_status ON blog_posts(status);
CREATE INDEX IF NOT EXISTS idx_blog_posts_slug ON blog_posts(slug);
CREATE INDEX IF NOT EXISTS idx_blog_posts_published_at ON blog_posts(published_at DESC);

ALTER TABLE blog_posts ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "public_select_blog_posts" ON blog_posts;
CREATE POLICY "public_select_blog_posts" ON blog_posts FOR SELECT
  TO anon, authenticated USING (true);

DROP POLICY IF EXISTS "auth_insert_blog_posts" ON blog_posts;
CREATE POLICY "auth_insert_blog_posts" ON blog_posts FOR INSERT
  TO authenticated WITH CHECK (true);

DROP POLICY IF EXISTS "auth_update_blog_posts" ON blog_posts;
CREATE POLICY "auth_update_blog_posts" ON blog_posts FOR UPDATE
  TO authenticated USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "auth_delete_blog_posts" ON blog_posts;
CREATE POLICY "auth_delete_blog_posts" ON blog_posts FOR DELETE
  TO authenticated USING (true);

-- ---------- portfolios ----------
CREATE TABLE IF NOT EXISTS portfolios (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  slug text NOT NULL UNIQUE,
  client text,
  category text,
  description text,
  image_url text,
  technologies text[] DEFAULT '{}',
  results jsonb DEFAULT '{}',
  url text,
  featured boolean NOT NULL DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_portfolios_featured ON portfolios(featured);

ALTER TABLE portfolios ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "public_select_portfolios" ON portfolios;
CREATE POLICY "public_select_portfolios" ON portfolios FOR SELECT
  TO anon, authenticated USING (true);

DROP POLICY IF EXISTS "auth_insert_portfolios" ON portfolios;
CREATE POLICY "auth_insert_portfolios" ON portfolios FOR INSERT
  TO authenticated WITH CHECK (true);

DROP POLICY IF EXISTS "auth_update_portfolios" ON portfolios;
CREATE POLICY "auth_update_portfolios" ON portfolios FOR UPDATE
  TO authenticated USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "auth_delete_portfolios" ON portfolios;
CREATE POLICY "auth_delete_portfolios" ON portfolios FOR DELETE
  TO authenticated USING (true);

-- ---------- testimonials ----------
CREATE TABLE IF NOT EXISTS testimonials (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  client_name text NOT NULL,
  company text,
  role text,
  content text NOT NULL,
  rating integer NOT NULL DEFAULT 5 CHECK (rating BETWEEN 1 AND 5),
  avatar_url text,
  featured boolean NOT NULL DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE testimonials ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "public_select_testimonials" ON testimonials;
CREATE POLICY "public_select_testimonials" ON testimonials FOR SELECT
  TO anon, authenticated USING (true);

DROP POLICY IF EXISTS "auth_insert_testimonials" ON testimonials;
CREATE POLICY "auth_insert_testimonials" ON testimonials FOR INSERT
  TO authenticated WITH CHECK (true);

DROP POLICY IF EXISTS "auth_update_testimonials" ON testimonials;
CREATE POLICY "auth_update_testimonials" ON testimonials FOR UPDATE
  TO authenticated USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "auth_delete_testimonials" ON testimonials;
CREATE POLICY "auth_delete_testimonials" ON testimonials FOR DELETE
  TO authenticated USING (true);

-- ---------- career_applications ----------
CREATE TABLE IF NOT EXISTS career_applications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name text NOT NULL,
  email text NOT NULL,
  phone text,
  position text NOT NULL,
  cover_letter text,
  resume_url text,
  experience text,
  status text NOT NULL DEFAULT 'new' CHECK (status IN ('new','reviewing','interviewed','offered','rejected')),
  created_at timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_career_apps_status ON career_applications(status);

ALTER TABLE career_applications ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "public_insert_career_apps" ON career_applications;
CREATE POLICY "public_insert_career_apps" ON career_applications FOR INSERT
  TO anon, authenticated WITH CHECK (true);

DROP POLICY IF EXISTS "auth_select_career_apps" ON career_applications;
CREATE POLICY "auth_select_career_apps" ON career_applications FOR SELECT
  TO authenticated USING (true);

DROP POLICY IF EXISTS "auth_update_career_apps" ON career_applications;
CREATE POLICY "auth_update_career_apps" ON career_applications FOR UPDATE
  TO authenticated USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "auth_delete_career_apps" ON career_applications;
CREATE POLICY "auth_delete_career_apps" ON career_applications FOR DELETE
  TO authenticated USING (true);

-- ---------- site_visitors (single row via is_singleton flag) ----------
CREATE TABLE IF NOT EXISTS site_visitors (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  is_singleton boolean NOT NULL DEFAULT true UNIQUE,
  count bigint NOT NULL DEFAULT 0,
  updated_at timestamptz DEFAULT now()
);

INSERT INTO site_visitors (is_singleton, count) VALUES (true, 0) ON CONFLICT (is_singleton) DO NOTHING;

ALTER TABLE site_visitors ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "public_select_visitors" ON site_visitors;
CREATE POLICY "public_select_visitors" ON site_visitors FOR SELECT
  TO anon, authenticated USING (true);

DROP POLICY IF EXISTS "auth_update_visitors" ON site_visitors;
CREATE POLICY "auth_update_visitors" ON site_visitors FOR UPDATE
  TO authenticated USING (true) WITH CHECK (true);

-- ---------- functions ----------
CREATE OR REPLACE FUNCTION increment_visitor_count()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO site_visitors (is_singleton, count) VALUES (true, 1)
  ON CONFLICT (is_singleton) DO UPDATE SET count = site_visitors.count + 1, updated_at = now();
END;
$$;

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS leads_updated_at ON leads;
CREATE TRIGGER leads_updated_at BEFORE UPDATE ON leads
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

DROP TRIGGER IF EXISTS blog_posts_updated_at ON blog_posts;
CREATE TRIGGER blog_posts_updated_at BEFORE UPDATE ON blog_posts
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

DROP TRIGGER IF EXISTS portfolios_updated_at ON portfolios;
CREATE TRIGGER portfolios_updated_at BEFORE UPDATE ON portfolios
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

DROP TRIGGER IF EXISTS testimonials_updated_at ON testimonials;
CREATE TRIGGER testimonials_updated_at BEFORE UPDATE ON testimonials
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();