388 lines
14 KiB
PL/PgSQL
388 lines
14 KiB
PL/PgSQL
-- Düğün Salonu Yönetim Sistemi - Full Database Schema & Data
|
||
-- Generated by Antigravity
|
||
|
||
-- ==========================================
|
||
-- 1. BASE SCHEMA (Core Tables)
|
||
-- ==========================================
|
||
|
||
-- Enable UUID extension
|
||
create extension if not exists "uuid-ossp";
|
||
|
||
-- Create Profiles Table (Extends Auth)
|
||
create table if not exists profiles (
|
||
id uuid references auth.users on delete cascade not null primary key,
|
||
role text check (role in ('admin', 'staff')) default 'staff',
|
||
full_name text,
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- Create Customers Table (CRM)
|
||
create table if not exists customers (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
full_name text not null,
|
||
phone text,
|
||
email text,
|
||
city text,
|
||
district text,
|
||
address text,
|
||
notes text,
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- Create Halls Table (Salons)
|
||
create table if not exists halls (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
name text not null,
|
||
capacity int,
|
||
description text,
|
||
features text[],
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- Create Packages Table (Pricing)
|
||
create table if not exists packages (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
name text not null,
|
||
description text,
|
||
price decimal(10,2) not null,
|
||
is_active boolean default true,
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- Create Reservations Table
|
||
create table if not exists reservations (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
hall_id uuid references halls(id) on delete set null,
|
||
customer_id uuid references customers(id) on delete set null,
|
||
package_id uuid references packages(id) on delete set null,
|
||
start_time timestamp with time zone not null,
|
||
end_time timestamp with time zone not null,
|
||
status text check (status in ('pending', 'confirmed', 'cancelled', 'completed')) default 'pending',
|
||
notes text,
|
||
created_by uuid references auth.users(id),
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- Create Payments Table
|
||
create table if not exists payments (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
reservation_id uuid references reservations(id) on delete cascade,
|
||
amount decimal(10,2) not null,
|
||
payment_type text check (payment_type in ('deposit', 'full', 'remaining')),
|
||
payment_method text check (payment_method in ('cash', 'credit_card', 'transfer')),
|
||
status text check (status in ('pending', 'paid', 'refunded')) default 'pending',
|
||
paid_at timestamp with time zone,
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- RLS Policies for Base Tables
|
||
alter table profiles enable row level security;
|
||
alter table customers enable row level security;
|
||
alter table halls enable row level security;
|
||
alter table packages enable row level security;
|
||
alter table reservations enable row level security;
|
||
alter table payments enable row level security;
|
||
|
||
-- Drop existing policies to avoid conflicts if re-running
|
||
drop policy if exists "Enable all access for authenticated users" on profiles;
|
||
drop policy if exists "Enable all access for authenticated users" on customers;
|
||
drop policy if exists "Enable all access for authenticated users" on halls;
|
||
drop policy if exists "Enable all access for authenticated users" on packages;
|
||
drop policy if exists "Enable all access for authenticated users" on reservations;
|
||
drop policy if exists "Enable all access for authenticated users" on payments;
|
||
|
||
create policy "Enable all access for authenticated users" on profiles for all using (auth.role() = 'authenticated');
|
||
create policy "Enable all access for authenticated users" on customers for all using (auth.role() = 'authenticated');
|
||
create policy "Enable all access for authenticated users" on halls for all using (auth.role() = 'authenticated');
|
||
create policy "Enable all access for authenticated users" on packages for all using (auth.role() = 'authenticated');
|
||
create policy "Enable all access for authenticated users" on reservations for all using (auth.role() = 'authenticated');
|
||
create policy "Enable all access for authenticated users" on payments for all using (auth.role() = 'authenticated');
|
||
|
||
-- Auth Trigger
|
||
create or replace function public.handle_new_user()
|
||
returns trigger as $$
|
||
begin
|
||
insert into public.profiles (id, full_name, role)
|
||
values (new.id, new.raw_user_meta_data->>'full_name', 'staff')
|
||
on conflict (id) do nothing;
|
||
return new;
|
||
end;
|
||
$$ language plpgsql security definer;
|
||
|
||
-- Drop trigger if exists
|
||
drop trigger if exists on_auth_user_created on auth.users;
|
||
create trigger on_auth_user_created
|
||
after insert on auth.users
|
||
for each row execute procedure public.handle_new_user();
|
||
|
||
-- Create Audit Logs Table
|
||
create table if not exists audit_logs (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
user_id uuid references auth.users(id),
|
||
action text not null,
|
||
entity_type text not null,
|
||
entity_id uuid,
|
||
details jsonb,
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- RLS for Audit Logs
|
||
alter table audit_logs enable row level security;
|
||
|
||
drop policy if exists "Admins can read all logs" on audit_logs;
|
||
create policy "Admins can read all logs" on audit_logs
|
||
for select using (
|
||
exists (
|
||
select 1 from profiles
|
||
where profiles.id = auth.uid() and profiles.role = 'admin'
|
||
)
|
||
);
|
||
|
||
drop policy if exists "Users can insert logs" on audit_logs;
|
||
create policy "Users can insert logs" on audit_logs
|
||
for insert with check (auth.uid() = user_id);
|
||
|
||
|
||
-- ==========================================
|
||
-- 2. CMS Tables & Seeds
|
||
-- ==========================================
|
||
|
||
-- 1. Site Contents Table (Genel İçerikler)
|
||
CREATE TABLE IF NOT EXISTS public.site_contents (
|
||
key TEXT PRIMARY KEY,
|
||
value TEXT,
|
||
type TEXT DEFAULT 'text', -- 'text', 'image_url', 'html', 'json'
|
||
section TEXT, -- 'home', 'about', 'contact', 'footer'
|
||
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
|
||
);
|
||
|
||
-- RLS: Public read, Admin write
|
||
ALTER TABLE public.site_contents ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Allow public read access on site_contents" ON public.site_contents;
|
||
CREATE POLICY "Allow public read access on site_contents"
|
||
ON public.site_contents FOR SELECT TO anon, authenticated
|
||
USING (true);
|
||
|
||
DROP POLICY IF EXISTS "Allow authenticated update on site_contents" ON public.site_contents;
|
||
CREATE POLICY "Allow authenticated update on site_contents"
|
||
ON public.site_contents FOR UPDATE TO authenticated
|
||
USING (true)
|
||
WITH CHECK (true);
|
||
|
||
DROP POLICY IF EXISTS "Allow authenticated insert on site_contents" ON public.site_contents;
|
||
CREATE POLICY "Allow authenticated insert on site_contents"
|
||
ON public.site_contents FOR INSERT TO authenticated
|
||
WITH CHECK (true);
|
||
|
||
-- 2. Services Table (Hizmetler)
|
||
CREATE TABLE IF NOT EXISTS public.services (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
title TEXT NOT NULL,
|
||
description TEXT,
|
||
image_url TEXT,
|
||
"order" INTEGER DEFAULT 0,
|
||
is_active BOOLEAN DEFAULT true,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
||
);
|
||
|
||
ALTER TABLE public.services ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Allow public read on services" ON public.services;
|
||
CREATE POLICY "Allow public read on services"
|
||
ON public.services FOR SELECT TO anon, authenticated
|
||
USING (is_active = true OR auth.role() = 'authenticated');
|
||
|
||
DROP POLICY IF EXISTS "Allow admin all on services" ON public.services;
|
||
CREATE POLICY "Allow admin all on services"
|
||
ON public.services FOR ALL TO authenticated
|
||
USING (true)
|
||
WITH CHECK (true);
|
||
|
||
-- 3. Gallery Table (Galeri)
|
||
CREATE TABLE IF NOT EXISTS public.gallery (
|
||
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
|
||
image_url TEXT NOT NULL,
|
||
caption TEXT,
|
||
category TEXT DEFAULT 'Genel',
|
||
"order" INTEGER DEFAULT 0,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
||
);
|
||
|
||
ALTER TABLE public.gallery ENABLE ROW LEVEL SECURITY;
|
||
|
||
DROP POLICY IF EXISTS "Allow public read on gallery" ON public.gallery;
|
||
CREATE POLICY "Allow public read on gallery"
|
||
ON public.gallery FOR SELECT TO anon, authenticated
|
||
USING (true);
|
||
|
||
DROP POLICY IF EXISTS "Allow admin all on gallery" ON public.gallery;
|
||
CREATE POLICY "Allow admin all on gallery"
|
||
ON public.gallery FOR ALL TO authenticated
|
||
USING (true)
|
||
WITH CHECK (true);
|
||
|
||
-- Seed Data (Initial Content)
|
||
INSERT INTO public.site_contents (key, value, type, section) VALUES
|
||
('site_title', 'Rüya Düğün Salonu', 'text', 'general'),
|
||
('hero_title', 'Hayallerinizdeki Düğün İçin', 'text', 'home'),
|
||
('hero_subtitle', 'Unutulmaz anlar, profesyonel hizmet ve şık atmosfer.', 'text', 'home'),
|
||
('contact_phone', '+90 555 123 45 67', 'text', 'contact'),
|
||
('contact_address', 'Atatürk Mah. Karanfil Sok. No:5, İstanbul', 'text', 'contact'),
|
||
('site_logo', '', 'image_url', 'general')
|
||
ON CONFLICT (key) DO NOTHING;
|
||
|
||
-- ==========================================
|
||
-- 3. Expenses Module
|
||
-- ==========================================
|
||
|
||
-- Create Expense Categories Table
|
||
create table if not exists expense_categories (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
name text not null,
|
||
description text,
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- Create Expenses Table
|
||
create table if not exists expenses (
|
||
id uuid default uuid_generate_v4() primary key,
|
||
category_id uuid references expense_categories(id) on delete set null,
|
||
amount decimal(10,2) not null,
|
||
description text,
|
||
date timestamp with time zone default timezone('utc'::text, now()) not null,
|
||
created_by uuid references auth.users(id),
|
||
created_at timestamp with time zone default timezone('utc'::text, now()) not null
|
||
);
|
||
|
||
-- RLS
|
||
alter table expense_categories enable row level security;
|
||
alter table expenses enable row level security;
|
||
|
||
drop policy if exists "Enable all access for authenticated users" on expense_categories;
|
||
drop policy if exists "Enable all access for authenticated users" on expenses;
|
||
|
||
create policy "Enable all access for authenticated users" on expense_categories for all using (auth.role() = 'authenticated');
|
||
create policy "Enable all access for authenticated users" on expenses for all using (auth.role() = 'authenticated');
|
||
|
||
|
||
-- ==========================================
|
||
-- 4. Storage Setup
|
||
-- ==========================================
|
||
|
||
-- 1. Create the storage bucket
|
||
insert into storage.buckets (id, name, public)
|
||
values ('hall-logos', 'hall-logos', true)
|
||
on conflict (id) do nothing;
|
||
|
||
insert into storage.buckets (id, name, public)
|
||
values ('public-site', 'public-site', true)
|
||
on conflict (id) do nothing;
|
||
|
||
-- 2. Enable RLS
|
||
-- Hall Logos Policies
|
||
drop policy if exists "Public Access" on storage.objects;
|
||
create policy "Public Access"
|
||
on storage.objects for select
|
||
using ( bucket_id = 'hall-logos' );
|
||
|
||
drop policy if exists "Authenticated Uploads" on storage.objects;
|
||
create policy "Authenticated Uploads"
|
||
on storage.objects for insert
|
||
with check ( bucket_id = 'hall-logos' and auth.role() = 'authenticated' );
|
||
|
||
drop policy if exists "Authenticated Updates" on storage.objects;
|
||
create policy "Authenticated Updates"
|
||
on storage.objects for update
|
||
with check ( bucket_id = 'hall-logos' and auth.role() = 'authenticated' );
|
||
|
||
drop policy if exists "Authenticated Deletes" on storage.objects;
|
||
create policy "Authenticated Deletes"
|
||
on storage.objects for delete
|
||
using ( bucket_id = 'hall-logos' and auth.role() = 'authenticated' );
|
||
|
||
|
||
-- Public Site Bucket Policies
|
||
-- Allow public read SPECIFIC to this bucket
|
||
DROP POLICY IF EXISTS "Public Access public-site" ON storage.objects;
|
||
CREATE POLICY "Public Access public-site"
|
||
ON storage.objects FOR SELECT
|
||
TO public
|
||
USING ( bucket_id = 'public-site' );
|
||
|
||
-- Allow authenticated upload/delete SPECIFIC to this bucket
|
||
DROP POLICY IF EXISTS "Authenticated Insert public-site" ON storage.objects;
|
||
CREATE POLICY "Authenticated Insert public-site"
|
||
ON storage.objects FOR INSERT
|
||
TO authenticated
|
||
WITH CHECK ( bucket_id = 'public-site' );
|
||
|
||
DROP POLICY IF EXISTS "Authenticated Update public-site" ON storage.objects;
|
||
CREATE POLICY "Authenticated Update public-site"
|
||
ON storage.objects FOR UPDATE
|
||
TO authenticated
|
||
USING ( bucket_id = 'public-site' );
|
||
|
||
DROP POLICY IF EXISTS "Authenticated Delete public-site" ON storage.objects;
|
||
CREATE POLICY "Authenticated Delete public-site"
|
||
ON storage.objects FOR DELETE
|
||
TO authenticated
|
||
USING ( bucket_id = 'public-site' );
|
||
|
||
|
||
-- ==========================================
|
||
-- 5. Schema Updates (Halls)
|
||
-- ==========================================
|
||
|
||
-- Add logo_url column to halls table
|
||
alter table halls
|
||
add column if not exists logo_url text;
|
||
|
||
|
||
-- ==========================================
|
||
-- 6. Schema Updates (Reservations)
|
||
-- ==========================================
|
||
|
||
-- Add price column to reservations table
|
||
alter table reservations add column if not exists price decimal(10,2);
|
||
|
||
-- Update existing reservations to set price from their package (snapshotting the price)
|
||
update reservations
|
||
set price = packages.price
|
||
from packages
|
||
where reservations.package_id = packages.id
|
||
and reservations.price is null;
|
||
|
||
-- Add groom_region and bride_region to reservations table
|
||
ALTER TABLE reservations
|
||
ADD COLUMN IF NOT EXISTS groom_region text,
|
||
ADD COLUMN IF NOT EXISTS bride_region text;
|
||
|
||
|
||
-- ==========================================
|
||
-- 7. Schema Updates (Gallery)
|
||
-- ==========================================
|
||
|
||
ALTER TABLE public.gallery ADD COLUMN IF NOT EXISTS video_url TEXT;
|
||
ALTER TABLE public.gallery ADD COLUMN IF NOT EXISTS is_hero BOOLEAN DEFAULT false;
|
||
|
||
|
||
-- ==========================================
|
||
-- 8. Additional Data (Site Contents)
|
||
-- ==========================================
|
||
|
||
INSERT INTO public.site_contents (key, value, type, section)
|
||
VALUES ('contact_email', 'info@ruyadugun.com', 'text', 'contact')
|
||
ON CONFLICT (key) DO NOTHING;
|
||
|
||
INSERT INTO public.site_contents (key, value, type, section) VALUES
|
||
('social_instagram', 'https://instagram.com/', 'text', 'contact'),
|
||
('social_facebook', 'https://facebook.com/', 'text', 'contact'),
|
||
('social_twitter', 'https://twitter.com/', 'text', 'contact')
|
||
ON CONFLICT (key) DO NOTHING;
|
||
|
||
INSERT INTO public.site_contents (key, value, type, section)
|
||
VALUES ('contact_map_embed', '<iframe src="..."></iframe>', 'html', 'contact')
|
||
ON CONFLICT (key) DO NOTHING;
|