ASK_Business_Manager/supabase_setup.sql at main · aditisang7-bit/ASK_Business_Manager · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
-- 1. Business Profiles
create table if not exists public.business_profiles (
  id text primary key,
  name text,
  email text,
  phone text,
  type text,
  subscription_plan text,
  is_subscribed boolean,
  password text,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.business_profiles disable row level security;

-- 2. Services
create table if not exists public.services (
  id text primary key,
  business_id text references public.business_profiles(id),
  name text,
  price numeric,
  duration_minutes integer,
  description text,
  image text,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.services disable row level security;

-- 3. Staff
create table if not exists public.staff (
  id text primary key,
  business_id text references public.business_profiles(id),
  name text,
  role text,
  phone text,
  commission_rate numeric,
  status text,
  avatar text,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.staff disable row level security;

-- 4. Customers (Global List)
create table if not exists public.all_customers (
  id text primary key,
  business_id text references public.business_profiles(id),
  name text,
  phone text,
  email text,
  total_visits integer default 0,
  loyalty_points integer default 0,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.all_customers disable row level security;

-- 5. Inventory
create table if not exists public.inventory (
  id text primary key,
  business_id text references public.business_profiles(id),
  name text,
  category text,
  stock integer,
  unit text,
  min_stock_alert integer,
  vendor text,
  image text,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.inventory disable row level security;

-- 6. Appointments
create table if not exists public.appointments (
  id text primary key,
  business_id text references public.business_profiles(id),
  customer_id text references public.all_customers(id),
  staff_id text references public.staff(id),
  service_id text references public.services(id),
  date text,
  time text,
  status text,
  notes text,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.appointments disable row level security;

-- 7. Invoices
create table if not exists public.invoices (
  id text primary key,
  business_id text references public.business_profiles(id),
  appointment_id text references public.appointments(id),
  customer_id text references public.all_customers(id),
  date text,
  amount numeric,
  tax numeric,
  total numeric,
  method text,
  generated_at timestamp with time zone,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.invoices disable row level security;

-- 8. AI Consultations
create table if not exists public.ai_consultations (
  id bigint generated by default as identity primary key,
  business_id text references public.business_profiles(id),
  customer_name text,
  recommended_services jsonb,
  skin_type text,
  age_group text,
  created_at timestamp with time zone default timezone('utc'::text, now())
);
alter table public.ai_consultations disable row level security;