-- ============================================================
-- The Vibe - Fair-Trade Delivery Platform
-- PostgreSQL + PostGIS Schema
-- ============================================================

-- Extensions
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- fuzzy search for restaurant names

-- ============================================================
-- ENUMS
-- ============================================================

CREATE TYPE user_role AS ENUM ('customer', 'driver', 'restaurant_owner', 'admin');
CREATE TYPE order_status AS ENUM (
  'pending',
  'confirmed',
  'preparing',
  'ready_for_pickup',
  'driver_assigned',
  'picked_up',
  'delivered',
  'cancelled'
);
CREATE TYPE subscription_status AS ENUM ('active', 'past_due', 'cancelled', 'trialing');
CREATE TYPE driver_session_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE zone_status AS ENUM ('active', 'inactive', 'coming_soon');
CREATE TYPE payment_status AS ENUM ('pending', 'succeeded', 'failed', 'refunded');

-- ============================================================
-- USERS (all roles share this table)
-- ============================================================

CREATE TABLE users (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email           VARCHAR(255) UNIQUE NOT NULL,
  phone           VARCHAR(20),
  password_hash   TEXT NOT NULL,
  role            user_role NOT NULL,
  first_name      VARCHAR(100) NOT NULL,
  last_name       VARCHAR(100) NOT NULL,
  avatar_url      TEXT,
  is_verified     BOOLEAN DEFAULT FALSE,
  is_active       BOOLEAN DEFAULT TRUE,
  stripe_customer_id VARCHAR(255),
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_stripe ON users(stripe_customer_id);

-- ============================================================
-- ZONES (GTA Geofencing - PostGIS)
-- ============================================================

CREATE TABLE zones (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name            VARCHAR(100) NOT NULL,          -- e.g. "Downtown Toronto"
  slug            VARCHAR(100) UNIQUE NOT NULL,   -- e.g. "downtown-toronto"
  boundary        GEOGRAPHY(POLYGON, 4326) NOT NULL,
  center          GEOGRAPHY(POINT, 4326) NOT NULL,
  radius_km       DECIMAL(6,2),                   -- for quick distance checks
  status          zone_status DEFAULT 'active',
  priority        INTEGER DEFAULT 0,              -- higher = launched first
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_zones_boundary ON zones USING GIST(boundary);
CREATE INDEX idx_zones_center ON zones USING GIST(center);
CREATE INDEX idx_zones_status ON zones(status);

-- ============================================================
-- RESTAURANTS
-- ============================================================

CREATE TABLE restaurants (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  owner_id              UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  name                  VARCHAR(255) NOT NULL,
  slug                  VARCHAR(255) UNIQUE NOT NULL,
  description           TEXT,
  cuisine_type          VARCHAR(100)[],            -- array: ["Italian", "Pizza"]
  phone                 VARCHAR(20),
  email                 VARCHAR(255),
  address               TEXT NOT NULL,
  city                  VARCHAR(100) DEFAULT 'Toronto',
  province              VARCHAR(10) DEFAULT 'ON',
  postal_code           VARCHAR(10),
  location              GEOGRAPHY(POINT, 4326) NOT NULL,
  zone_id               UUID REFERENCES zones(id),
  logo_url              TEXT,
  banner_url            TEXT,
  is_active             BOOLEAN DEFAULT TRUE,
  is_open               BOOLEAN DEFAULT FALSE,
  accepts_orders        BOOLEAN DEFAULT TRUE,
  avg_prep_time_minutes INTEGER DEFAULT 20,
  min_order_amount      DECIMAL(10,2) DEFAULT 0,
  rating                DECIMAL(3,2),             -- 0.00 to 5.00
  total_reviews         INTEGER DEFAULT 0,
  -- Savings tracking vs competitors
  total_orders_platform INTEGER DEFAULT 0,
  total_savings_vs_uber DECIMAL(12,2) DEFAULT 0,  -- cumulative savings
  -- Stripe
  stripe_account_id     VARCHAR(255),             -- for future payout features
  subscription_id       VARCHAR(255),             -- Stripe subscription ID
  subscription_status   subscription_status,
  created_at            TIMESTAMPTZ DEFAULT NOW(),
  updated_at            TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_restaurants_location ON restaurants USING GIST(location);
CREATE INDEX idx_restaurants_zone ON restaurants(zone_id);
CREATE INDEX idx_restaurants_owner ON restaurants(owner_id);
CREATE INDEX idx_restaurants_active ON restaurants(is_active, is_open);
CREATE INDEX idx_restaurants_name ON restaurants USING gin(name gin_trgm_ops);

-- ============================================================
-- MENUS & MENU ITEMS
-- ============================================================

CREATE TABLE menu_categories (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  restaurant_id   UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  name            VARCHAR(100) NOT NULL,
  description     TEXT,
  sort_order      INTEGER DEFAULT 0,
  is_active       BOOLEAN DEFAULT TRUE,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_menu_categories_restaurant ON menu_categories(restaurant_id);

CREATE TABLE menu_items (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  restaurant_id   UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  category_id     UUID REFERENCES menu_categories(id) ON DELETE SET NULL,
  name            VARCHAR(255) NOT NULL,
  description     TEXT,
  price           DECIMAL(10,2) NOT NULL,         -- in-store price (parity guaranteed)
  image_url       TEXT,
  is_available    BOOLEAN DEFAULT TRUE,
  is_featured     BOOLEAN DEFAULT FALSE,
  dietary_tags    VARCHAR(50)[],                  -- ["vegan", "gluten-free", "halal"]
  allergens       VARCHAR(50)[],                  -- ["nuts", "dairy"]
  prep_time_min   INTEGER DEFAULT 10,
  calories        INTEGER,
  sort_order      INTEGER DEFAULT 0,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_menu_items_restaurant ON menu_items(restaurant_id);
CREATE INDEX idx_menu_items_category ON menu_items(category_id);
CREATE INDEX idx_menu_items_available ON menu_items(is_available);

-- ============================================================
-- DRIVERS
-- ============================================================

CREATE TABLE drivers (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id               UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE RESTRICT,
  zone_id               UUID REFERENCES zones(id),
  vehicle_type          VARCHAR(50),              -- "bicycle", "car", "ebike", "scooter"
  vehicle_plate         VARCHAR(20),
  license_number        VARCHAR(50),
  is_background_checked BOOLEAN DEFAULT FALSE,
  is_approved           BOOLEAN DEFAULT FALSE,
  rating                DECIMAL(3,2),
  total_deliveries      INTEGER DEFAULT 0,
  total_earnings        DECIMAL(12,2) DEFAULT 0,
  total_tips            DECIMAL(12,2) DEFAULT 0,
  -- Stripe
  stripe_payment_method VARCHAR(255),             -- saved card for daily fee
  created_at            TIMESTAMPTZ DEFAULT NOW(),
  updated_at            TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_drivers_user ON drivers(user_id);
CREATE INDEX idx_drivers_zone ON drivers(zone_id);
CREATE INDEX idx_drivers_approved ON drivers(is_approved);

-- ============================================================
-- DRIVER SESSIONS (daily login fee tracking)
-- ============================================================

CREATE TABLE driver_sessions (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  driver_id           UUID NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  session_date        DATE NOT NULL DEFAULT CURRENT_DATE,
  login_at            TIMESTAMPTZ DEFAULT NOW(),
  logout_at           TIMESTAMPTZ,
  daily_fee           DECIMAL(10,2) DEFAULT 20.00,
  fee_paid            BOOLEAN DEFAULT FALSE,
  fee_payment_intent  VARCHAR(255),              -- Stripe PaymentIntent ID
  fee_paid_at         TIMESTAMPTZ,
  -- Break-even tracking
  deliveries_count    INTEGER DEFAULT 0,
  delivery_revenue    DECIMAL(10,2) DEFAULT 0,   -- deliveries × $5
  tips_earned         DECIMAL(10,2) DEFAULT 0,
  net_earnings        DECIMAL(10,2) DEFAULT 0,   -- revenue + tips - daily_fee
  status              driver_session_status DEFAULT 'active',
  UNIQUE(driver_id, session_date)
);

CREATE INDEX idx_driver_sessions_driver ON driver_sessions(driver_id);
CREATE INDEX idx_driver_sessions_date ON driver_sessions(session_date);
CREATE INDEX idx_driver_sessions_fee ON driver_sessions(fee_paid);

-- ============================================================
-- DRIVER LOCATION (real-time PostGIS)
-- ============================================================

CREATE TABLE driver_locations (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  driver_id     UUID NOT NULL UNIQUE REFERENCES drivers(id) ON DELETE CASCADE,
  location      GEOGRAPHY(POINT, 4326) NOT NULL,
  heading       DECIMAL(5,2),                    -- degrees 0-360
  speed_kmh     DECIMAL(6,2),
  is_online     BOOLEAN DEFAULT FALSE,
  is_available  BOOLEAN DEFAULT FALSE,           -- online AND not on a delivery
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_driver_locations_location ON driver_locations USING GIST(location);
CREATE INDEX idx_driver_locations_available ON driver_locations(is_online, is_available);

-- ============================================================
-- ORDERS
-- ============================================================

CREATE TABLE orders (
  id                    UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  order_number          SERIAL,                  -- human-readable order number
  customer_id           UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  restaurant_id         UUID NOT NULL REFERENCES restaurants(id) ON DELETE RESTRICT,
  driver_id             UUID REFERENCES drivers(id) ON DELETE SET NULL,
  zone_id               UUID REFERENCES zones(id),
  -- Addresses (stored as text + geo)
  delivery_address      TEXT NOT NULL,
  delivery_location     GEOGRAPHY(POINT, 4326) NOT NULL,
  restaurant_location   GEOGRAPHY(POINT, 4326),  -- snapshot at order time
  -- Pricing (all transparent, no hidden fees)
  subtotal              DECIMAL(10,2) NOT NULL,   -- sum of items
  delivery_fee          DECIMAL(10,2) DEFAULT 5.00,
  tip_amount            DECIMAL(10,2) DEFAULT 0,
  platform_fee          DECIMAL(10,2) DEFAULT 0.10,    -- restaurant pays
  cc_processing_fee     DECIMAL(10,2),            -- calculated at checkout
  total_customer_pays   DECIMAL(10,2) NOT NULL,  -- subtotal + delivery + tip + cc
  restaurant_receives   DECIMAL(10,2),            -- subtotal - platform_fee - cc
  driver_receives       DECIMAL(10,2),            -- delivery_fee + tip
  -- Savings display
  uber_equivalent_fee   DECIMAL(10,2),            -- what UberEats would charge (30%)
  restaurant_savings    DECIMAL(10,2),            -- uber_fee - platform_fee
  -- Status
  status                order_status DEFAULT 'pending',
  special_instructions  TEXT,
  estimated_pickup_at   TIMESTAMPTZ,
  estimated_delivery_at TIMESTAMPTZ,
  picked_up_at          TIMESTAMPTZ,
  delivered_at          TIMESTAMPTZ,
  cancelled_at          TIMESTAMPTZ,
  cancellation_reason   TEXT,
  -- Stripe
  payment_intent_id     VARCHAR(255),
  payment_status        payment_status DEFAULT 'pending',
  -- OSRM route snapshot
  route_polyline        TEXT,                     -- encoded polyline
  distance_km           DECIMAL(8,3),
  duration_minutes      INTEGER,
  created_at            TIMESTAMPTZ DEFAULT NOW(),
  updated_at            TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_restaurant ON orders(restaurant_id);
CREATE INDEX idx_orders_driver ON orders(driver_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_orders_delivery_location ON orders USING GIST(delivery_location);

-- ============================================================
-- ORDER ITEMS
-- ============================================================

CREATE TABLE order_items (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  order_id        UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  menu_item_id    UUID NOT NULL REFERENCES menu_items(id) ON DELETE RESTRICT,
  name            VARCHAR(255) NOT NULL,          -- snapshot at order time
  price           DECIMAL(10,2) NOT NULL,         -- snapshot at order time
  quantity        INTEGER NOT NULL DEFAULT 1,
  subtotal        DECIMAL(10,2) NOT NULL,         -- price × quantity
  special_request TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_order_items_order ON order_items(order_id);

-- ============================================================
-- DRIVER EARNINGS (per-delivery record)
-- ============================================================

CREATE TABLE driver_earnings (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  driver_id       UUID NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  session_id      UUID NOT NULL REFERENCES driver_sessions(id) ON DELETE CASCADE,
  order_id        UUID NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
  delivery_fee    DECIMAL(10,2) DEFAULT 5.00,
  tip_amount      DECIMAL(10,2) DEFAULT 0,
  total           DECIMAL(10,2) NOT NULL,         -- delivery_fee + tip
  is_profit       BOOLEAN DEFAULT FALSE,          -- true if past break-even
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_driver_earnings_driver ON driver_earnings(driver_id);
CREATE INDEX idx_driver_earnings_session ON driver_earnings(session_id);
CREATE INDEX idx_driver_earnings_order ON driver_earnings(order_id);

-- ============================================================
-- RESTAURANT SUBSCRIPTIONS
-- ============================================================

CREATE TABLE restaurant_subscriptions (
  id                        UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  restaurant_id             UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  stripe_subscription_id    VARCHAR(255) UNIQUE NOT NULL,
  stripe_customer_id        VARCHAR(255) NOT NULL,
  status                    subscription_status DEFAULT 'active',
  current_period_start      TIMESTAMPTZ,
  current_period_end        TIMESTAMPTZ,
  monthly_fee               DECIMAL(10,2) DEFAULT 500.00,
  trial_ends_at             TIMESTAMPTZ,
  cancelled_at              TIMESTAMPTZ,
  created_at                TIMESTAMPTZ DEFAULT NOW(),
  updated_at                TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_restaurant ON restaurant_subscriptions(restaurant_id);
CREATE INDEX idx_subscriptions_stripe ON restaurant_subscriptions(stripe_subscription_id);
CREATE INDEX idx_subscriptions_status ON restaurant_subscriptions(status);

-- ============================================================
-- DELIVERY TRACKING (breadcrumb trail)
-- ============================================================

CREATE TABLE delivery_tracking (
  id          BIGSERIAL PRIMARY KEY,             -- high insert rate, use bigserial
  order_id    UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  driver_id   UUID NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  location    GEOGRAPHY(POINT, 4326) NOT NULL,
  heading     DECIMAL(5,2),
  speed_kmh   DECIMAL(6,2),
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_delivery_tracking_order ON delivery_tracking(order_id);
CREATE INDEX idx_delivery_tracking_recorded ON delivery_tracking(recorded_at DESC);
-- Partition by month for performance at scale
-- CREATE TABLE delivery_tracking_2025_01 PARTITION OF delivery_tracking
--   FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- ============================================================
-- PLATFORM ANALYTICS (materialized for performance)
-- ============================================================

CREATE TABLE daily_platform_stats (
  stat_date           DATE PRIMARY KEY,
  total_orders        INTEGER DEFAULT 0,
  total_revenue       DECIMAL(12,2) DEFAULT 0,   -- all delivery fees collected
  total_driver_fees   DECIMAL(12,2) DEFAULT 0,   -- daily login fees
  total_restaurant_fees DECIMAL(12,2) DEFAULT 0, -- per-order fees
  active_drivers      INTEGER DEFAULT 0,
  active_restaurants  INTEGER DEFAULT 0,
  new_customers       INTEGER DEFAULT 0,
  avg_delivery_time   INTEGER,                    -- minutes
  created_at          TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- REVIEWS
-- ============================================================

CREATE TABLE reviews (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  order_id        UUID NOT NULL UNIQUE REFERENCES orders(id) ON DELETE CASCADE,
  customer_id     UUID NOT NULL REFERENCES users(id),
  restaurant_id   UUID REFERENCES restaurants(id),
  driver_id       UUID REFERENCES drivers(id),
  restaurant_rating INTEGER CHECK (restaurant_rating BETWEEN 1 AND 5),
  driver_rating   INTEGER CHECK (driver_rating BETWEEN 1 AND 5),
  comment         TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_reviews_restaurant ON reviews(restaurant_id);
CREATE INDEX idx_reviews_driver ON reviews(driver_id);

-- ============================================================
-- FUNCTIONS & TRIGGERS
-- ============================================================

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
  BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_restaurants_updated_at
  BEFORE UPDATE ON restaurants FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_menu_items_updated_at
  BEFORE UPDATE ON menu_items FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_orders_updated_at
  BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Update driver session when an earning is recorded
CREATE OR REPLACE FUNCTION update_driver_session_on_earning()
RETURNS TRIGGER AS $$
DECLARE
  v_daily_fee DECIMAL(10,2);
BEGIN
  SELECT daily_fee INTO v_daily_fee
  FROM driver_sessions WHERE id = NEW.session_id;

  UPDATE driver_sessions SET
    deliveries_count = deliveries_count + 1,
    delivery_revenue = delivery_revenue + NEW.delivery_fee,
    tips_earned      = tips_earned + NEW.tip_amount,
    net_earnings     = (delivery_revenue + NEW.delivery_fee) + (tips_earned + NEW.tip_amount) - daily_fee
  WHERE id = NEW.session_id;

  -- Mark earning as profit if past break-even
  UPDATE driver_earnings SET
    is_profit = (
      SELECT net_earnings >= 0
      FROM driver_sessions WHERE id = NEW.session_id
    )
  WHERE id = NEW.id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_driver_earnings_session
  AFTER INSERT ON driver_earnings
  FOR EACH ROW EXECUTE FUNCTION update_driver_session_on_earning();

-- Update restaurant savings on order completion
CREATE OR REPLACE FUNCTION update_restaurant_savings()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'delivered' AND OLD.status != 'delivered' THEN
    UPDATE restaurants SET
      total_orders_platform = total_orders_platform + 1,
      total_savings_vs_uber = total_savings_vs_uber + COALESCE(NEW.restaurant_savings, 0)
    WHERE id = NEW.restaurant_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_savings
  AFTER UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION update_restaurant_savings();

-- Check delivery location is within an active zone
CREATE OR REPLACE FUNCTION check_delivery_in_zone(p_location GEOGRAPHY)
RETURNS UUID AS $$
DECLARE
  v_zone_id UUID;
BEGIN
  SELECT id INTO v_zone_id
  FROM zones
  WHERE status = 'active'
    AND ST_Within(p_location::geometry, boundary::geometry)
  ORDER BY priority DESC
  LIMIT 1;

  RETURN v_zone_id;
END;
$$ LANGUAGE plpgsql;

-- Find available drivers within radius of a point
CREATE OR REPLACE FUNCTION find_nearby_drivers(
  p_location  GEOGRAPHY,
  p_radius_km DECIMAL DEFAULT 5.0,
  p_limit     INTEGER DEFAULT 10
)
RETURNS TABLE(driver_id UUID, distance_m FLOAT) AS $$
BEGIN
  RETURN QUERY
  SELECT
    dl.driver_id,
    ST_Distance(dl.location, p_location) AS distance_m
  FROM driver_locations dl
  JOIN drivers d ON d.id = dl.driver_id
  WHERE
    dl.is_online = TRUE
    AND dl.is_available = TRUE
    AND d.is_approved = TRUE
    AND ST_DWithin(dl.location, p_location, p_radius_km * 1000)
  ORDER BY distance_m ASC
  LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- VIEWS
-- ============================================================

-- Driver dashboard view
CREATE VIEW v_driver_dashboard AS
SELECT
  d.id AS driver_id,
  u.first_name,
  u.last_name,
  ds.session_date,
  ds.deliveries_count,
  ds.daily_fee,
  ds.delivery_revenue,
  ds.tips_earned,
  ds.net_earnings,
  -- Break-even logic
  GREATEST(0, ds.daily_fee - ds.delivery_revenue) AS remaining_to_break_even,
  CEIL(GREATEST(0, ds.daily_fee - ds.delivery_revenue) / 5.0) AS deliveries_to_break_even,
  ds.delivery_revenue >= ds.daily_fee AS has_broken_even,
  ds.fee_paid
FROM drivers d
JOIN users u ON u.id = d.user_id
LEFT JOIN driver_sessions ds ON ds.driver_id = d.id
  AND ds.session_date = CURRENT_DATE;

-- Restaurant savings dashboard view
CREATE VIEW v_restaurant_savings AS
SELECT
  r.id AS restaurant_id,
  r.name,
  r.total_orders_platform,
  r.total_savings_vs_uber,
  -- Monthly stats
  COUNT(o.id) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS orders_this_month,
  SUM(o.platform_fee) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS platform_fees_this_month,
  SUM(o.uber_equivalent_fee) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS uber_would_have_charged,
  SUM(o.restaurant_savings) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS saved_this_month
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.id AND o.status = 'delivered'
GROUP BY r.id, r.name, r.total_orders_platform, r.total_savings_vs_uber;