-- =====================================================================
-- Raavi's Wellness and Style - database (PostgreSQL 13 or newer)
-- Upload/run this ONCE on an empty database:
--   psql "$DATABASE_URL" -f sql/database.sql
-- Section A = tables and rules (required)
-- Section B = demo data (staff, services, images are DEMO; edit from admin panel)
-- You can delete Section B if you want to start completely empty.
-- =====================================================================

-- ---------------------- SECTION A: STRUCTURE -------------------------
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE users (
  id            serial PRIMARY KEY,
  email         text NOT NULL,
  password_hash text NOT NULL,
  name          text NOT NULL,
  role          text NOT NULL CHECK (role IN ('owner','manager','receptionist','staff')),
  staff_id      int,
  active        boolean NOT NULL DEFAULT true,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_email_uq ON users (lower(email));

CREATE TABLE sessions (
  token_hash text PRIMARY KEY,
  user_id    int NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at timestamptz NOT NULL
);
CREATE INDEX sessions_user_idx ON sessions(user_id);

CREATE TABLE categories (
  id         serial PRIMARY KEY,
  name       text NOT NULL UNIQUE,
  sort_order int NOT NULL DEFAULT 0,
  active     boolean NOT NULL DEFAULT true
);

CREATE TABLE services (
  id              serial PRIMARY KEY,
  category_id     int NOT NULL REFERENCES categories(id),
  name            text NOT NULL,
  description     text,
  duration_min    int NOT NULL CHECK (duration_min BETWEEN 5 AND 600),
  buffer_min      int NOT NULL DEFAULT 0 CHECK (buffer_min BETWEEN 0 AND 120),
  price_inr       int NOT NULL CHECK (price_inr >= 0),
  image_url       text,
  online_bookable boolean NOT NULL DEFAULT true,
  active          boolean NOT NULL DEFAULT true,
  is_demo         boolean NOT NULL DEFAULT false,
  sort_order      int NOT NULL DEFAULT 0
);
CREATE INDEX services_cat_idx ON services(category_id, active);

CREATE TABLE staff (
  id                  serial PRIMARY KEY,
  display_name        text NOT NULL,
  specialty           text NOT NULL,
  experience_years    int CHECK (experience_years IS NULL OR experience_years BETWEEN 0 AND 60),
  bio                 text,
  photo_url           text,
  is_demo             boolean NOT NULL DEFAULT false,
  active              boolean NOT NULL DEFAULT true,
  sort_order          int NOT NULL DEFAULT 0,
  attendance_token    text NOT NULL UNIQUE DEFAULT replace(gen_random_uuid()::text,'-','') || replace(gen_random_uuid()::text,'-',''),
  attendance_pin_hash text
);
ALTER TABLE users ADD CONSTRAINT users_staff_fk FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE SET NULL;

CREATE TABLE staff_services (
  staff_id   int NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
  service_id int NOT NULL REFERENCES services(id) ON DELETE CASCADE,
  PRIMARY KEY (staff_id, service_id)
);

-- weekday: 0 = Sunday ... 6 = Saturday. Times are salon local time (IST).
CREATE TABLE staff_schedules (
  id         serial PRIMARY KEY,
  staff_id   int NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
  weekday    int NOT NULL CHECK (weekday BETWEEN 0 AND 6),
  start_time time NOT NULL,
  end_time   time NOT NULL,
  CHECK (end_time > start_time)
);
CREATE INDEX staff_sched_idx ON staff_schedules(staff_id, weekday);

CREATE TABLE staff_time_off (
  id       serial PRIMARY KEY,
  staff_id int NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
  start_at timestamptz NOT NULL,
  end_at   timestamptz NOT NULL,
  reason   text,
  CHECK (end_at > start_at)
);
CREATE INDEX staff_off_idx ON staff_time_off(staff_id, start_at);

CREATE TABLE business_hours (
  weekday    int PRIMARY KEY CHECK (weekday BETWEEN 0 AND 6),
  open_time  time,
  close_time time,
  closed     boolean NOT NULL DEFAULT false
);

CREATE TABLE holidays (
  date  date PRIMARY KEY,
  label text NOT NULL
);

CREATE TABLE customers (
  id         serial PRIMARY KEY,
  name       text NOT NULL,
  phone      text NOT NULL UNIQUE,          -- normalised +91XXXXXXXXXX
  email      text,
  source     text,
  tags       text[] NOT NULL DEFAULT '{}',
  consent_at timestamptz,                   -- when customer agreed to data use at booking
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE customer_notes (
  id          serial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  author_id   int REFERENCES users(id) ON DELETE SET NULL,
  body        text NOT NULL,                -- do not store medical details here
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE bookings (
  id             serial PRIMARY KEY,
  reference      text NOT NULL UNIQUE,
  customer_id    int NOT NULL REFERENCES customers(id),
  service_id     int NOT NULL REFERENCES services(id),
  staff_id       int NOT NULL REFERENCES staff(id),
  start_at       timestamptz NOT NULL,
  end_at         timestamptz NOT NULL,      -- includes buffer
  status         text NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending','confirmed','checked_in','in_service','completed','cancelled','no_show')),
  price_inr      int NOT NULL,              -- price snapshot taken on the server
  payment_status text NOT NULL DEFAULT 'unpaid' CHECK (payment_status IN ('unpaid','partial','paid','refunded')),
  internal_note  text,
  source         text NOT NULL DEFAULT 'website',
  created_at     timestamptz NOT NULL DEFAULT now(),
  CHECK (end_at > start_at),
  -- THE database refuses two active bookings that overlap for the same staff member
  CONSTRAINT booking_no_overlap EXCLUDE USING gist (
    staff_id WITH =,
    tstzrange(start_at, end_at, '[)') WITH &&
  ) WHERE (status IN ('pending','confirmed','checked_in','in_service','completed'))
);
CREATE INDEX bookings_staff_idx    ON bookings(staff_id, start_at);
CREATE INDEX bookings_customer_idx ON bookings(customer_id);
CREATE INDEX bookings_status_idx   ON bookings(status, start_at);

CREATE TABLE booking_history (
  id          serial PRIMARY KEY,
  booking_id  int NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  from_status text,
  to_status   text NOT NULL,
  changed_by  int REFERENCES users(id) ON DELETE SET NULL,
  note        text,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX booking_hist_idx ON booking_history(booking_id);

CREATE TABLE slot_holds (
  id         serial PRIMARY KEY,
  token      text NOT NULL UNIQUE,
  staff_id   int NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
  service_id int NOT NULL REFERENCES services(id) ON DELETE CASCADE,
  start_at   timestamptz NOT NULL,
  end_at     timestamptz NOT NULL,
  expires_at timestamptz NOT NULL,
  CHECK (end_at > start_at),
  CONSTRAINT hold_no_overlap EXCLUDE USING gist (
    staff_id WITH =,
    tstzrange(start_at, end_at, '[)') WITH &&
  )
);
CREATE INDEX slot_holds_exp_idx ON slot_holds(expires_at);

-- Membership packages (tables ready; admin screens for selling/using packages come later)
CREATE TABLE membership_plans (
  id         serial PRIMARY KEY,
  name       text NOT NULL,
  audience   text NOT NULL CHECK (audience IN ('men','women')),
  price_inr  int NOT NULL,
  inclusions jsonb NOT NULL DEFAULT '[]',
  active     boolean NOT NULL DEFAULT true
);
CREATE TABLE customer_memberships (
  id          serial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id),
  plan_id     int NOT NULL REFERENCES membership_plans(id),
  purchased_at timestamptz NOT NULL DEFAULT now(),
  expires_at  timestamptz,
  balance     jsonb NOT NULL DEFAULT '[]'
);
CREATE TABLE membership_usage (
  id            serial PRIMARY KEY,
  membership_id int NOT NULL REFERENCES customer_memberships(id),
  booking_id    int REFERENCES bookings(id) ON DELETE SET NULL,
  label         text NOT NULL,
  used_at       timestamptz NOT NULL DEFAULT now()
);

-- Staff QR attendance
CREATE TABLE attendance_records (
  id         serial PRIMARY KEY,
  staff_id   int NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
  kind       text NOT NULL CHECK (kind IN ('check_in','check_out')),
  scanned_at timestamptz NOT NULL DEFAULT now(),
  lat        double precision,
  lng        double precision,
  accuracy_m double precision,
  distance_m double precision,
  status     text NOT NULL CHECK (status IN ('verified','suspicious','rejected')),
  user_agent text,
  admin_note text,
  entered_by int REFERENCES users(id) ON DELETE SET NULL   -- set when admin added/edited manually
);
CREATE INDEX attendance_idx ON attendance_records(staff_id, scanned_at);

CREATE TABLE gallery_images (
  id         serial PRIMARY KEY,
  image_url  text,
  alt        text NOT NULL,
  is_demo    boolean NOT NULL DEFAULT false,
  published  boolean NOT NULL DEFAULT true,
  sort_order int NOT NULL DEFAULT 0
);

CREATE TABLE settings (
  key   text PRIMARY KEY,
  value jsonb NOT NULL
);

CREATE TABLE audit_logs (
  id         bigserial PRIMARY KEY,
  actor_id   int,
  action     text NOT NULL,
  entity     text NOT NULL,
  entity_id  text,
  detail     jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_idx ON audit_logs(entity, entity_id);

-- Settings that must exist (values editable from Admin > Settings)
INSERT INTO settings(key,value) VALUES
 ('salon_name',          '"Raavi''s Wellness and Style"'),
 ('tagline',             '"Look Good. Feel Good. Be You."'),
 ('phone',               '"7727890029"'),
 ('whatsapp_number',     '"917727890029"'),
 ('email',               '""'),
 ('address',             '"Hanumangarh Road, near Jain College, opposite Jodhpur Sweet, Sri Ganganagar"'),
 ('instagram_url',       '"https://www.instagram.com/raaviswellnessandstyle2026"'),
 ('about_text',          '"Add your salon story here from Admin > Settings."'),
 ('salon_lat',           'null'),
 ('salon_lng',           'null'),
 ('attendance_radius_m', '100'),
 ('slot_interval_min',   '30'),
 ('min_lead_min',        '60'),
 ('max_advance_days',    '30'),
 ('hold_minutes',        '5'),
 ('auto_confirm',        'true');

INSERT INTO business_hours(weekday,open_time,close_time,closed)
SELECT d, '10:00', '20:00', false FROM generate_series(0,6) d;

-- Membership plans: prices from the owner's flyers. Inclusions left empty - fill in from the full flyers.
INSERT INTO membership_plans(name,audience,price_inr) VALUES
 ('Men - Basic','men',3000), ('Men - Classic','men',5000), ('Men - Premium','men',10000),
 ('Women - Basic','women',3300), ('Women - Classic','women',7000), ('Women - Premium','women',13000);

-- ---------------------- SECTION B: DEMO DATA -------------------------
-- Everything below is placeholder content marked is_demo = true.
INSERT INTO categories(name,sort_order) VALUES
 ('Hair & Grooming',1),('Skin Care',2),('Nails',3),('Wellness & Spa',4);

INSERT INTO services(category_id,name,description,duration_min,buffer_min,price_inr,is_demo,sort_order)
SELECT c.id, v.name, 'Demo description - edit from admin panel.', v.dur, 10, v.price, true, v.ord
FROM (VALUES
 ('Hair & Grooming','Haircut',30,300,1),
 ('Hair & Grooming','Hair Spa',60,800,2),
 ('Hair & Grooming','Beard Trim',20,150,3),
 ('Skin Care','Facial',60,1200,1),
 ('Skin Care','D-Tan Treatment',30,600,2),
 ('Skin Care','Cleanup',40,500,3),
 ('Nails','Basic Manicure',45,500,1),
 ('Nails','Basic Pedicure',45,600,2),
 ('Wellness & Spa','Full Body Massage',60,1500,1),
 ('Wellness & Spa','Face Massage',30,500,2),
 ('Wellness & Spa','Wellness Counseling Session',45,800,3)
) AS v(cat,name,dur,price,ord)
JOIN categories c ON c.name = v.cat;

INSERT INTO staff(display_name,specialty,experience_years,bio,is_demo,sort_order) VALUES
 ('Demo Stylist','Hair & Grooming Expert',6,'Demo profile - replace with real details.',true,1),
 ('Demo Skin Expert','Skin Care Expert',5,'Demo profile - replace with real details.',true,2),
 ('Demo Nail Artist','Nail Expert',4,'Demo profile - replace with real details.',true,3),
 ('Demo Wellness Expert','Wellness Expert',7,'Demo profile - replace with real details.',true,4);

INSERT INTO staff_services(staff_id,service_id)
SELECT st.id, sv.id
FROM staff st
JOIN categories c ON c.name = CASE st.display_name
      WHEN 'Demo Stylist' THEN 'Hair & Grooming'
      WHEN 'Demo Skin Expert' THEN 'Skin Care'
      WHEN 'Demo Nail Artist' THEN 'Nails'
      ELSE 'Wellness & Spa' END
JOIN services sv ON sv.category_id = c.id;

INSERT INTO staff_schedules(staff_id,weekday,start_time,end_time)
SELECT s.id, d, '10:00', '19:00' FROM staff s, generate_series(1,6) d;

INSERT INTO gallery_images(image_url,alt,is_demo,sort_order)
SELECT NULL, 'Demo image ' || g, true, g FROM generate_series(1,6) g;