143 lines
3.8 KiB
SQL
143 lines
3.8 KiB
SQL
-- 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'
|
||
)
|
||
); |