346 lines
14 KiB
SQL
346 lines
14 KiB
SQL
-- ParaKasa Consolidated Database Schema
|
|
-- Generated on 2026-01-29
|
|
-- This file contains the entire database structure, RLS policies, and storage setup.
|
|
|
|
-- 1. Enable Extensions
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- 2. Tables
|
|
|
|
-- PROFILES
|
|
CREATE TABLE IF NOT EXISTS public.profiles (
|
|
id UUID REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
|
|
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('admin', 'user')),
|
|
full_name TEXT,
|
|
phone TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- SITE SETTINGS
|
|
CREATE TABLE IF NOT EXISTS public.site_settings (
|
|
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
site_title TEXT NOT NULL DEFAULT 'ParaKasa',
|
|
site_description TEXT,
|
|
contact_email TEXT,
|
|
contact_phone TEXT,
|
|
logo_url TEXT,
|
|
currency TEXT DEFAULT 'TRY',
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- SITE CONTENTS (Dynamic CMS)
|
|
CREATE TABLE IF NOT EXISTS public.site_contents (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT,
|
|
type TEXT CHECK (type IN ('text', 'image_url', 'html', 'long_text', 'json')),
|
|
section TEXT NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- CATEGORIES
|
|
CREATE TABLE IF NOT EXISTS public.categories (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
image_url TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- PRODUCTS
|
|
CREATE TABLE IF NOT EXISTS public.products (
|
|
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
name TEXT NOT NULL,
|
|
category TEXT NOT NULL, -- Legacy text field, kept for compatibility
|
|
category_id UUID REFERENCES public.categories(id) ON DELETE SET NULL, -- Foreign key relation
|
|
description TEXT,
|
|
image_url TEXT,
|
|
price DECIMAL(10,2),
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_products_category_id ON public.products(category_id);
|
|
|
|
-- PRODUCT IMAGES (Multi-image support)
|
|
CREATE TABLE IF NOT EXISTS public.product_images (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
product_id BIGINT REFERENCES public.products(id) ON DELETE CASCADE NOT NULL,
|
|
image_url TEXT NOT NULL,
|
|
display_order INT DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_product_images_product_id ON public.product_images(product_id);
|
|
|
|
-- SLIDERS
|
|
CREATE TABLE IF NOT EXISTS public.sliders (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
image_url TEXT NOT NULL,
|
|
link TEXT,
|
|
"order" INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- CUSTOMERS
|
|
CREATE TABLE IF NOT EXISTS public.customers (
|
|
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
|
|
full_name TEXT NOT NULL,
|
|
email TEXT,
|
|
phone TEXT,
|
|
address TEXT,
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- SMS SETTINGS
|
|
CREATE TABLE IF NOT EXISTS public.sms_settings (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
provider TEXT DEFAULT 'netgsm',
|
|
api_url TEXT DEFAULT 'https://api.netgsm.com.tr/sms/send/get',
|
|
username TEXT,
|
|
password TEXT,
|
|
header TEXT,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
|
|
);
|
|
|
|
-- SMS LOGS
|
|
CREATE TABLE IF NOT EXISTS public.sms_logs (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
phone TEXT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
status TEXT, -- 'success' or 'error'
|
|
response_code TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now())
|
|
);
|
|
|
|
-- AUTH CODES (2FA / Verification)
|
|
CREATE TABLE IF NOT EXISTS public.auth_codes (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
email TEXT NOT NULL,
|
|
code TEXT NOT NULL,
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_auth_codes_email ON public.auth_codes(email);
|
|
|
|
|
|
-- 3. Row Level Security (RLS) & Policies
|
|
|
|
-- Enable RLS
|
|
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.site_settings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.site_contents ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.categories ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.product_images ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.sliders ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.sms_settings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.sms_logs ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.auth_codes ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Helper function for admin check (optional, but cleaner if used commonly)
|
|
-- For this script, we'll use the EXISTS subquery pattern directly to ensure portability.
|
|
|
|
-- PROFILES POLICIES
|
|
CREATE POLICY "Public profiles are viewable by everyone."
|
|
ON public.profiles FOR SELECT USING ( true );
|
|
|
|
CREATE POLICY "Users can insert their own profile."
|
|
ON public.profiles FOR INSERT WITH CHECK ( auth.uid() = id );
|
|
|
|
CREATE POLICY "Users can update own profile."
|
|
ON public.profiles FOR UPDATE USING ( auth.uid() = id );
|
|
|
|
-- SITE SETTINGS POLICIES
|
|
CREATE POLICY "Site settings are viewable by everyone."
|
|
ON public.site_settings FOR SELECT USING ( true );
|
|
|
|
CREATE POLICY "Admins can update site settings."
|
|
ON public.site_settings FOR UPDATE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
-- SITE CONTENTS POLICIES
|
|
CREATE POLICY "Public read access"
|
|
ON public.site_contents FOR SELECT USING (true);
|
|
|
|
CREATE POLICY "Admins can insert site contents"
|
|
ON public.site_contents FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can update site contents"
|
|
ON public.site_contents FOR UPDATE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
-- CATEGORIES POLICIES
|
|
CREATE POLICY "Public categories are viewable by everyone."
|
|
ON public.categories FOR SELECT USING ( true );
|
|
|
|
CREATE POLICY "Admins can insert categories."
|
|
ON public.categories FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can update categories."
|
|
ON public.categories FOR UPDATE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can delete categories."
|
|
ON public.categories FOR DELETE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
-- PRODUCTS POLICIES
|
|
CREATE POLICY "Public products are viewable by everyone."
|
|
ON public.products FOR SELECT USING ( true );
|
|
|
|
CREATE POLICY "Admins can insert products"
|
|
ON public.products FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can update products"
|
|
ON public.products FOR UPDATE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can delete products"
|
|
ON public.products FOR DELETE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
-- PRODUCT IMAGES POLICIES (Inherit from products basically, or admin only)
|
|
CREATE POLICY "Public product images are viewable."
|
|
ON public.product_images FOR SELECT USING ( true );
|
|
|
|
CREATE POLICY "Admins can insert product images"
|
|
ON public.product_images FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can delete product images"
|
|
ON public.product_images FOR DELETE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
-- SLIDERS POLICIES
|
|
CREATE POLICY "Public sliders are viewable by everyone."
|
|
ON public.sliders FOR SELECT USING ( true );
|
|
|
|
CREATE POLICY "Admins can insert sliders."
|
|
ON public.sliders FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can update sliders."
|
|
ON public.sliders FOR UPDATE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can delete sliders."
|
|
ON public.sliders FOR DELETE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
-- CUSTOMERS POLICIES
|
|
CREATE POLICY "Admins can view customers"
|
|
ON public.customers FOR SELECT USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can insert customers"
|
|
ON public.customers FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can update customers"
|
|
ON public.customers FOR UPDATE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
CREATE POLICY "Admins can delete customers"
|
|
ON public.customers FOR DELETE USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
-- SMS SETTINGS/LOGS POLICIES
|
|
CREATE POLICY "Admins can full access sms" ON public.sms_settings USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
CREATE POLICY "Admins can full access sms logs" ON public.sms_logs USING (
|
|
EXISTS (SELECT 1 FROM public.profiles WHERE profiles.id = auth.uid() AND profiles.role = 'admin')
|
|
);
|
|
|
|
|
|
-- 4. Initial Data
|
|
|
|
-- Site Settings Default
|
|
INSERT INTO public.site_settings (site_title, contact_email)
|
|
SELECT 'ParaKasa', 'info@parakasa.com'
|
|
WHERE NOT EXISTS (SELECT 1 FROM public.site_settings);
|
|
|
|
-- Site Contents Defaults
|
|
INSERT INTO public.site_contents (key, value, type, section) VALUES
|
|
('site_title', 'ParaKasa', 'text', 'general'),
|
|
('site_description', 'ParaKasa Yönetim Paneli', 'long_text', 'general'),
|
|
('site_logo', '', 'image_url', 'general'),
|
|
('contact_phone', '', 'text', 'contact'),
|
|
('contact_email', '', 'text', 'contact'),
|
|
('contact_address', '', 'long_text', 'contact'),
|
|
('social_instagram', '', 'text', 'contact'),
|
|
('social_youtube', '', 'text', 'contact'),
|
|
('social_tiktok', '', 'text', 'contact'),
|
|
('contact_map_embed', '', 'html', 'contact')
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
|
|
-- 5. Storage Buckets & Policies
|
|
|
|
-- Buckets
|
|
INSERT INTO storage.buckets (id, name, public) VALUES ('products', 'products', true) ON CONFLICT (id) DO NOTHING;
|
|
INSERT INTO storage.buckets (id, name, public) VALUES ('categories', 'categories', true) ON CONFLICT (id) DO NOTHING;
|
|
INSERT INTO storage.buckets (id, name, public) VALUES ('sliders', 'sliders', true) ON CONFLICT (id) DO NOTHING;
|
|
|
|
-- Drop existing policies to avoid duplicates if re-running
|
|
DROP POLICY IF EXISTS "Public Access Products" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Upload Products" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Update Products" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Delete Products" ON storage.objects;
|
|
|
|
-- Products Policies
|
|
CREATE POLICY "Public Access Products" ON storage.objects FOR SELECT USING ( bucket_id = 'products' );
|
|
CREATE POLICY "Auth Upload Products" ON storage.objects FOR INSERT WITH CHECK ( bucket_id = 'products' AND auth.role() = 'authenticated' );
|
|
CREATE POLICY "Auth Update Products" ON storage.objects FOR UPDATE USING ( bucket_id = 'products' AND auth.role() = 'authenticated' );
|
|
CREATE POLICY "Auth Delete Products" ON storage.objects FOR DELETE USING ( bucket_id = 'products' AND auth.role() = 'authenticated' );
|
|
|
|
-- Categories Policies
|
|
DROP POLICY IF EXISTS "Public Access Categories" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Upload Categories" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Update Categories" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Delete Categories" ON storage.objects;
|
|
|
|
CREATE POLICY "Public Access Categories" ON storage.objects FOR SELECT USING ( bucket_id = 'categories' );
|
|
CREATE POLICY "Auth Upload Categories" ON storage.objects FOR INSERT WITH CHECK ( bucket_id = 'categories' AND auth.role() = 'authenticated' );
|
|
CREATE POLICY "Auth Update Categories" ON storage.objects FOR UPDATE USING ( bucket_id = 'categories' AND auth.role() = 'authenticated' );
|
|
CREATE POLICY "Auth Delete Categories" ON storage.objects FOR DELETE USING ( bucket_id = 'categories' AND auth.role() = 'authenticated' );
|
|
|
|
-- Sliders Policies
|
|
DROP POLICY IF EXISTS "Public Access Sliders" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Upload Sliders" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Update Sliders" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Auth Delete Sliders" ON storage.objects;
|
|
|
|
CREATE POLICY "Public Access Sliders" ON storage.objects FOR SELECT USING ( bucket_id = 'sliders' );
|
|
CREATE POLICY "Auth Upload Sliders" ON storage.objects FOR INSERT WITH CHECK ( bucket_id = 'sliders' AND auth.role() = 'authenticated' );
|
|
CREATE POLICY "Auth Update Sliders" ON storage.objects FOR UPDATE USING ( bucket_id = 'sliders' AND auth.role() = 'authenticated' );
|
|
CREATE POLICY "Auth Delete Sliders" ON storage.objects FOR DELETE USING ( bucket_id = 'sliders' AND auth.role() = 'authenticated' );
|
|
|