/*
  # Contact and Newsletter Schema

  1. New Tables
    - `contact_submissions` (already exists, checking structure)
    - `newsletter_subscribers` (already exists, checking structure)

  2. API Functions
    - Contact form submission endpoint
    - Newsletter subscription endpoint

  3. Security
    - Ensure proper RLS policies are in place
    - Allow public submissions for both forms
*/

-- Ensure contact_submissions table exists with proper structure
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'contact_submissions') THEN
    CREATE TABLE contact_submissions (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      name text NOT NULL,
      email text NOT NULL,
      subject text,
      message text NOT NULL,
      phone text,
      organization text,
      inquiry_type text DEFAULT 'general',
      status text DEFAULT 'new' CHECK (status IN ('new', 'read', 'replied', 'archived')),
      created_at timestamptz DEFAULT now()
    );
  END IF;
END $$;

-- Ensure newsletter_subscribers table exists with proper structure
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'newsletter_subscribers') THEN
    CREATE TABLE newsletter_subscribers (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      email text UNIQUE NOT NULL,
      name text,
      interests text[],
      subscribed boolean DEFAULT true,
      confirmed boolean DEFAULT false,
      confirmation_token text,
      subscribed_at timestamptz DEFAULT now(),
      unsubscribed_at timestamptz,
      created_at timestamptz DEFAULT now()
    );
  END IF;
END $$;

-- Ensure RLS is enabled
ALTER TABLE contact_submissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE newsletter_subscribers ENABLE ROW LEVEL SECURITY;

-- Contact submissions policies
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'contact_submissions' 
    AND policyname = 'Anyone can submit contact forms'
  ) THEN
    CREATE POLICY "Anyone can submit contact forms"
      ON contact_submissions
      FOR INSERT
      TO public
      WITH CHECK (true);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'contact_submissions' 
    AND policyname = 'Authenticated users can manage contact submissions'
  ) THEN
    CREATE POLICY "Authenticated users can manage contact submissions"
      ON contact_submissions
      FOR ALL
      TO authenticated
      USING (true)
      WITH CHECK (true);
  END IF;
END $$;

-- Newsletter subscribers policies
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'newsletter_subscribers' 
    AND policyname = 'Anyone can subscribe to newsletter'
  ) THEN
    CREATE POLICY "Anyone can subscribe to newsletter"
      ON newsletter_subscribers
      FOR INSERT
      TO public
      WITH CHECK (true);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'newsletter_subscribers' 
    AND policyname = 'Authenticated users can manage newsletter subscribers'
  ) THEN
    CREATE POLICY "Authenticated users can manage newsletter subscribers"
      ON newsletter_subscribers
      FOR ALL
      TO authenticated
      USING (true)
      WITH CHECK (true);
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_policies 
    WHERE tablename = 'newsletter_subscribers' 
    AND policyname = 'Subscribers can update their own subscription'
  ) THEN
    CREATE POLICY "Subscribers can update their own subscription"
      ON newsletter_subscribers
      FOR UPDATE
      TO public
      USING (true)
      WITH CHECK (true);
  END IF;
END $$;