-- SEO Global Settings Table
CREATE TABLE IF NOT EXISTS seo_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_name TEXT NOT NULL DEFAULT 'Trip Planner',
  site_description TEXT,
  site_keywords TEXT,
  default_og_image TEXT,
  favicon_url TEXT,
  google_analytics_id TEXT,
  google_search_console_verification TEXT,
  facebook_app_id TEXT,
  twitter_handle TEXT,
  robots_txt TEXT DEFAULT 'User-agent: *
Allow: /',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Page SEO Settings Table
CREATE TABLE IF NOT EXISTS page_seo (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  page_path TEXT NOT NULL UNIQUE,
  page_title TEXT NOT NULL,
  meta_description TEXT,
  meta_keywords TEXT,
  og_title TEXT,
  og_description TEXT,
  og_image TEXT,
  og_type TEXT DEFAULT 'website',
  twitter_card TEXT DEFAULT 'summary_large_image',
  twitter_title TEXT,
  twitter_description TEXT,
  twitter_image TEXT,
  canonical_url TEXT,
  structured_data JSONB,
  noindex BOOLEAN DEFAULT FALSE,
  nofollow BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- URL Redirects Table
CREATE TABLE IF NOT EXISTS url_redirects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  from_path TEXT NOT NULL UNIQUE,
  to_path TEXT NOT NULL,
  redirect_type INTEGER DEFAULT 301,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert default SEO settings
INSERT INTO seo_settings (site_name, site_description, site_keywords)
VALUES (
  'Trip Planner',
  'Seyahatlerinizi planlayın, keşfedin ve unutulmaz anılar biriktirin. Yapay zeka destekli seyahat planlama platformu.',
  'seyahat, tatil, gezi, planlama, tur, kapadokya, türkiye'
)
ON CONFLICT DO NOTHING;

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_page_seo_path ON page_seo(page_path);
CREATE INDEX IF NOT EXISTS idx_url_redirects_from ON url_redirects(from_path);
CREATE INDEX IF NOT EXISTS idx_url_redirects_active ON url_redirects(is_active);

-- Enable RLS
ALTER TABLE seo_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE page_seo ENABLE ROW LEVEL SECURITY;
ALTER TABLE url_redirects ENABLE ROW LEVEL SECURITY;

-- RLS Policies for seo_settings (public read, admin write)
CREATE POLICY "Anyone can read SEO settings"
  ON seo_settings FOR SELECT
  TO public
  USING (true);

CREATE POLICY "Admins can update SEO settings"
  ON seo_settings FOR UPDATE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- RLS Policies for page_seo (public read, admin write)
CREATE POLICY "Anyone can read page SEO"
  ON page_seo FOR SELECT
  TO public
  USING (true);

CREATE POLICY "Admins can insert page SEO"
  ON page_seo FOR INSERT
  TO authenticated
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

CREATE POLICY "Admins can update page SEO"
  ON page_seo FOR UPDATE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

CREATE POLICY "Admins can delete page SEO"
  ON page_seo FOR DELETE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- RLS Policies for url_redirects (public read active, admin manage)
CREATE POLICY "Anyone can read active redirects"
  ON url_redirects FOR SELECT
  TO public
  USING (is_active = true);

CREATE POLICY "Admins can manage redirects"
  ON url_redirects FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );