-- Passlabb booking app: initial schema.
-- Money is stored in Moroccan dirhams (MAD). Prices on booking lines are copied
-- at booking time, so later price changes never touch confirmed or paid bookings.

CREATE TABLE users (
  id            serial PRIMARY KEY,
  email         text NOT NULL,
  name          text NOT NULL DEFAULT '',
  role          text NOT NULL CHECK (role IN ('owner', 'manager', 'support')),
  password_hash text NOT NULL,
  active        boolean NOT NULL DEFAULT true,
  must_change_password boolean NOT NULL DEFAULT false,
  created_at    timestamptz NOT NULL DEFAULT now(),
  last_login_at timestamptz
);
CREATE UNIQUE INDEX users_email_key ON users (lower(email));

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

CREATE TABLE settings (
  key        text PRIMARY KEY,
  value      jsonb NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by int REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE regions (
  id      text PRIMARY KEY,
  name    text NOT NULL,
  name_fr text NOT NULL DEFAULT '',
  name_ar text NOT NULL DEFAULT '',
  sort    int NOT NULL DEFAULT 0
);

CREATE TABLE cities (
  id        text PRIMARY KEY,
  region_id text REFERENCES regions(id) ON DELETE SET NULL,
  name      text NOT NULL,
  name_fr   text NOT NULL DEFAULT '',
  name_ar   text NOT NULL DEFAULT '',
  lat       numeric(9,5),
  lon       numeric(9,5),
  active    boolean NOT NULL DEFAULT true
);

CREATE TABLE suppliers (
  id           serial PRIMARY KEY,
  name         text NOT NULL,
  kind         text NOT NULL DEFAULT '',
  city_id      text REFERENCES cities(id) ON DELETE SET NULL,
  contact_name text NOT NULL DEFAULT '',
  phone        text NOT NULL DEFAULT '',
  whatsapp     text NOT NULL DEFAULT '',
  email        text NOT NULL DEFAULT '',
  lang         text NOT NULL DEFAULT 'fr',
  portal_token text UNIQUE,
  notes        text NOT NULL DEFAULT '',
  active       boolean NOT NULL DEFAULT true,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE experiences (
  id               text PRIMARY KEY,
  city_id          text NOT NULL REFERENCES cities(id),
  category         text NOT NULL DEFAULT 'culture',
  name             text NOT NULL,
  name_fr          text NOT NULL DEFAULT '',
  name_ar          text NOT NULL DEFAULT '',
  place            text NOT NULL DEFAULT '',
  place_fr         text NOT NULL DEFAULT '',
  description      text NOT NULL DEFAULT '',
  description_fr   text NOT NULL DEFAULT '',
  description_ar   text NOT NULL DEFAULT '',
  duration_min     int NOT NULL DEFAULT 60 CHECK (duration_min >= 0),
  price_mad        numeric(10,2) NOT NULL CHECK (price_mad >= 0),   -- selling price per person
  cost_mad         numeric(10,2),                                    -- supplier cost per person
  margin_pct       numeric(6,2) NOT NULL DEFAULT 0,
  supplier_id      int REFERENCES suppliers(id) ON DELETE SET NULL,
  info             jsonb NOT NULL DEFAULT '{}',
  image            text NOT NULL DEFAULT '',
  confirm_required boolean NOT NULL DEFAULT false,
  active           boolean NOT NULL DEFAULT true,
  paused           boolean NOT NULL DEFAULT false,
  paused_reason    text NOT NULL DEFAULT '',
  updated_at       timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX experiences_city_idx ON experiences (city_id);
CREATE INDEX experiences_supplier_idx ON experiences (supplier_id);

CREATE TABLE experience_blocked_days (
  experience_id text NOT NULL REFERENCES experiences(id) ON DELETE CASCADE,
  day           date NOT NULL,
  PRIMARY KEY (experience_id, day)
);

CREATE TABLE routes (
  id         text PRIMARY KEY,
  city_id    text REFERENCES cities(id) ON DELETE SET NULL,
  name       text NOT NULL,
  name_fr    text NOT NULL DEFAULT '',
  name_ar    text NOT NULL DEFAULT '',
  blurb      text NOT NULL DEFAULT '',
  blurb_fr   text NOT NULL DEFAULT '',
  blurb_ar   text NOT NULL DEFAULT '',
  start_time text NOT NULL DEFAULT '09:00',
  items      jsonb NOT NULL DEFAULT '[]',   -- ordered experience ids
  active     boolean NOT NULL DEFAULT true
);

CREATE TABLE blog_posts (
  id         text PRIMARY KEY,
  category   text NOT NULL DEFAULT '',
  city_id    text REFERENCES cities(id) ON DELETE SET NULL,
  date       date NOT NULL DEFAULT current_date,
  read_min   int NOT NULL DEFAULT 3,
  title      text NOT NULL,
  title_fr   text NOT NULL DEFAULT '',
  excerpt    text NOT NULL DEFAULT '',
  excerpt_fr text NOT NULL DEFAULT '',
  body       text NOT NULL DEFAULT '',
  body_fr    text NOT NULL DEFAULT '',
  image      text NOT NULL DEFAULT '',
  source     text NOT NULL DEFAULT '',
  source_url text NOT NULL DEFAULT '',
  active     boolean NOT NULL DEFAULT true
);

CREATE TABLE bookings (
  id             serial PRIMARY KEY,
  ref            text NOT NULL UNIQUE,
  status         text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'cancelled', 'completed')),
  payment_status text NOT NULL DEFAULT 'unpaid' CHECK (payment_status IN ('unpaid', 'partial', 'paid', 'refunded')),
  customer       jsonb NOT NULL,              -- {name, email, phone, country}
  travellers     int NOT NULL CHECK (travellers BETWEEN 1 AND 50),
  lang           text NOT NULL DEFAULT 'en',
  guide_days     int NOT NULL DEFAULT 0,
  guide_rate_mad numeric(10,2) NOT NULL DEFAULT 0,
  items_mad      numeric(12,2) NOT NULL,
  total_mad      numeric(12,2) NOT NULL,
  paid_mad       numeric(12,2) NOT NULL DEFAULT 0,
  first_day      date,
  notes          text NOT NULL DEFAULT '',
  internal_notes text NOT NULL DEFAULT '',
  access_hash    text NOT NULL,               -- sha256 of the client's private link token
  source         text NOT NULL DEFAULT 'web',
  test           boolean NOT NULL DEFAULT false,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX bookings_first_day_idx ON bookings (first_day);
CREATE INDEX bookings_status_idx ON bookings (status);

CREATE TABLE booking_items (
  id             serial PRIMARY KEY,
  booking_id     int NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  day            date NOT NULL,
  start_time     text NOT NULL DEFAULT '',
  experience_id  text REFERENCES experiences(id) ON DELETE SET NULL,
  name           text NOT NULL,
  unit_price_mad numeric(10,2) NOT NULL,
  unit_cost_mad  numeric(10,2),
  quantity       int NOT NULL,
  line_total_mad numeric(12,2) NOT NULL,
  supplier_id    int REFERENCES suppliers(id) ON DELETE SET NULL,
  status         text NOT NULL DEFAULT 'ok' CHECK (status IN ('ok', 'cancelled')),
  sort           int NOT NULL DEFAULT 0
);
CREATE INDEX booking_items_booking_idx ON booking_items (booking_id);
CREATE INDEX booking_items_day_idx ON booking_items (day);
CREATE INDEX booking_items_supplier_idx ON booking_items (supplier_id);

CREATE TABLE payments (
  id           serial PRIMARY KEY,
  booking_id   int NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  provider     text NOT NULL,           -- manual, bank, cash, test, cmi …
  amount_mad   numeric(12,2) NOT NULL CHECK (amount_mad > 0),
  status       text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'valid', 'failed', 'refunded')),
  provider_ref text NOT NULL DEFAULT '',
  note         text NOT NULL DEFAULT '',
  raw          jsonb NOT NULL DEFAULT '{}',
  created_by   int REFERENCES users(id) ON DELETE SET NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX payments_booking_idx ON payments (booking_id);

CREATE TABLE invoice_counters (year int PRIMARY KEY, last int NOT NULL);
CREATE TABLE invoices (
  id         serial PRIMARY KEY,
  booking_id int NOT NULL UNIQUE REFERENCES bookings(id) ON DELETE RESTRICT,
  number     text NOT NULL UNIQUE,
  issued_at  timestamptz NOT NULL DEFAULT now(),
  total_mad  numeric(12,2) NOT NULL,
  data       jsonb NOT NULL                -- snapshot: business details, customer, lines
);

CREATE TABLE change_requests (
  id         serial PRIMARY KEY,
  booking_id int NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  message    text NOT NULL,
  late       boolean NOT NULL DEFAULT false, -- sent inside the free-change window
  status     text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'done', 'declined')),
  reply      text NOT NULL DEFAULT '',
  handled_by int REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE leads (
  id         serial PRIMARY KEY,
  kind       text NOT NULL CHECK (kind IN ('tailor', 'contact')),
  name       text NOT NULL,
  email      text NOT NULL,
  phone      text NOT NULL DEFAULT '',
  data       jsonb NOT NULL DEFAULT '{}',  -- dates, travellers, interests, budget, message
  lang       text NOT NULL DEFAULT 'en',
  status     text NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'in_progress', 'won', 'lost')),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE price_sources (
  experience_id text PRIMARY KEY REFERENCES experiences(id) ON DELETE CASCADE,
  type          text NOT NULL CHECK (type IN ('web', 'file', 'api')),
  url           text NOT NULL DEFAULT '',
  selector      text NOT NULL DEFAULT '',     -- optional /regex/ with one capture group
  key_column    text NOT NULL DEFAULT '',
  key_value     text NOT NULL DEFAULT '',
  price_column  text NOT NULL DEFAULT '',
  currency      text NOT NULL DEFAULT 'MAD',
  freq_hours    int NOT NULL DEFAULT 6,
  active        boolean NOT NULL DEFAULT true,
  last_checked_at timestamptz
);

CREATE TABLE price_checks (
  id            serial PRIMARY KEY,
  experience_id text NOT NULL REFERENCES experiences(id) ON DELETE CASCADE,
  at            timestamptz NOT NULL DEFAULT now(),
  status        text NOT NULL,      -- verified, updated, paused, error
  action        text NOT NULL,      -- none, update, pause
  stored_mad    numeric(10,2),
  observed_mad  numeric(10,2),
  diff_pct      numeric(7,2),
  reason        text NOT NULL DEFAULT '',
  dry           boolean NOT NULL DEFAULT false,
  source        text NOT NULL DEFAULT ''
);
CREATE INDEX price_checks_exp_idx ON price_checks (experience_id, at DESC);

CREATE TABLE price_alerts (
  id            serial PRIMARY KEY,
  experience_id text NOT NULL REFERENCES experiences(id) ON DELETE CASCADE,
  kind          text NOT NULL,      -- paused, error
  status        text NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'approved', 'dismissed')),
  old_cost_mad  numeric(10,2),
  new_cost_mad  numeric(10,2),
  old_price_mad numeric(10,2),
  new_price_mad numeric(10,2),
  diff_pct      numeric(7,2),
  reason        text NOT NULL DEFAULT '',
  source        text NOT NULL DEFAULT '',
  dry           boolean NOT NULL DEFAULT false,
  created_at    timestamptz NOT NULL DEFAULT now(),
  resolved_at   timestamptz,
  resolved_by   int REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX price_alerts_open_idx ON price_alerts (status) WHERE status = 'open';

CREATE TABLE portal_submissions (
  id           serial PRIMARY KEY,
  supplier_id  int NOT NULL REFERENCES suppliers(id) ON DELETE CASCADE,
  items        jsonb NOT NULL,     -- {experienceId: {price, currency, blocked: [YYYY-MM-DD]}}
  created_at   timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz
);

CREATE TABLE fx_rates (
  day        date PRIMARY KEY,
  eur_mad    numeric(10,4) NOT NULL,   -- MAD for 1 EUR
  usd_mad    numeric(10,4) NOT NULL,   -- MAD for 1 USD
  source     text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE email_outbox (
  id         serial PRIMARY KEY,
  to_email   text NOT NULL,
  subject    text NOT NULL,
  html       text NOT NULL,
  status     text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed')),
  attempts   int NOT NULL DEFAULT 0,
  error      text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now(),
  sent_at    timestamptz
);
CREATE INDEX email_outbox_pending_idx ON email_outbox (id) WHERE status = 'pending';

CREATE TABLE audit_log (
  id        bigserial PRIMARY KEY,
  user_id   int REFERENCES users(id) ON DELETE SET NULL,
  action    text NOT NULL,
  entity    text NOT NULL,
  entity_id text NOT NULL DEFAULT '',
  data      jsonb NOT NULL DEFAULT '{}',
  at        timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_log_entity_idx ON audit_log (entity, entity_id);