create table if not exists admin_users (
  id bigserial primary key,
  email varchar(254) not null unique,
  password_hash text not null,
  is_active boolean not null default true,
  last_login_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists admin_users_lower_email_idx on admin_users (lower(email));

create table if not exists admin_sessions (
  id bigserial primary key,
  admin_user_id bigint not null references admin_users(id) on delete cascade,
  token_hash text not null unique,
  expires_at timestamptz not null,
  revoked_at timestamptz,
  last_used_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists admin_sessions_user_active_idx
  on admin_sessions (admin_user_id, revoked_at, expires_at desc);
create index if not exists admin_sessions_token_hash_idx on admin_sessions (token_hash);

create table if not exists bookings (
  id bigserial primary key,
  full_name varchar(120) not null,
  phone varchar(30) not null,
  vehicle_type varchar(40) not null,
  service varchar(80) not null,
  preferred_date date not null,
  preferred_time time not null,
  address text not null,
  status varchar(30) not null default 'pending',
  created_at timestamptz not null default now()
);

create index if not exists bookings_created_at_idx on bookings (created_at desc);
create index if not exists bookings_phone_idx on bookings (phone);

create table if not exists blogs (
  id bigserial primary key,
  slug varchar(160) not null unique,
  title varchar(220) not null,
  excerpt text not null,
  meta_description text,
  og_description text,
  category varchar(80) not null,
  published_date varchar(40) not null,
  read_time varchar(40) not null,
  image text not null,
  meta_image text,
  og_image text,
  image_alt varchar(220) not null,
  sections jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists blogs_created_at_idx on blogs (created_at desc);

create table if not exists faqs (
  id bigserial primary key,
  category varchar(100) not null,
  question varchar(240) not null,
  answer text not null,
  display_order integer not null default 999,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists faqs_active_order_idx on faqs (is_active, display_order, category);
create index if not exists faqs_category_order_idx on faqs (category, display_order);

create table if not exists faq_categories (
  id bigserial primary key,
  name varchar(100) not null unique,
  display_order integer not null default 999,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists faq_categories_order_idx on faq_categories (display_order, name);

insert into faq_categories (name, display_order)
select category, min(display_order)
from faqs
group by category
on conflict (name) do nothing;

insert into faq_categories (name, display_order)
values ('General', 1)
on conflict (name) do nothing;

insert into faqs (category, question, answer, display_order, is_active)
select seed_faqs.category, seed_faqs.question, seed_faqs.answer, seed_faqs.display_order, true
from (
  values
    (
      'General',
      'How do I book a Ride N Revive service?',
      'You can book a service from the website by choosing your vehicle type, service, preferred date, time, and address. Once submitted, our team will review the request and contact you for confirmation.',
      1
    ),
    (
      'General',
      'Do you provide doorstep vehicle service?',
      'Yes. Ride N Revive provides doorstep service support for cars and bikes across supported service locations, so you can get help without visiting a workshop.',
      2
    ),
    (
      'General',
      'Can I choose the service date and time?',
      'Yes. During booking, you can select your preferred date and time slot. The team will confirm availability before the service is scheduled.',
      3
    ),
    (
      'General',
      'How are service prices confirmed?',
      'Prices depend on the selected package, vehicle model, parts, and any additional work required after inspection. The final estimate is shared before work begins.',
      4
    ),
    (
      'General',
      'What should I do if I need help after booking?',
      'You can contact Ride N Revive with your booking details. Our team will help with scheduling, service updates, and any questions about your vehicle care request.',
      5
    )
) as seed_faqs(category, question, answer, display_order)
where not exists (
  select 1
  from faqs
  where faqs.category = seed_faqs.category
    and faqs.question = seed_faqs.question
);

create table if not exists users (
  id bigserial primary key,
  name varchar(120) not null,
  email varchar(150) unique,
  mobile varchar(20) not null unique,
  otp varchar(5),
  otp_expires_at timestamptz,
  otp_purpose varchar(30),
  pending_email varchar(150),
  pending_mobile varchar(20),
  is_mobile_verified boolean not null default false,
  is_email_verified boolean not null default false,
  profile_image text,
  status varchar(20) not null default 'active'
    check (status in ('active', 'inactive', 'blocked')),
  referral_code varchar(20) unique,
  referred_by bigint references users(id),
  last_login_at timestamptz,
  last_active_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists users_lower_email_uidx on users (lower(email)) where email is not null;
create unique index if not exists users_mobile_uidx on users (mobile);
create unique index if not exists users_referral_code_uidx
  on users (referral_code)
  where referral_code is not null;

alter table users drop column if exists otp_expiry;
alter table users drop column if exists auth_token;
alter table users drop column if exists fcm_token;
alter table users add column if not exists otp_expires_at timestamptz;
alter table users add column if not exists otp_purpose varchar(30);
alter table users add column if not exists pending_email varchar(150);
alter table users add column if not exists pending_mobile varchar(20);
alter table users alter column otp type varchar(5) using left(otp, 5);

create table if not exists user_sessions (
  id bigserial primary key,
  user_id bigint not null references users(id) on delete cascade,
  token_hash text not null unique,
  fcm_token text,
  device_name varchar(160),
  user_agent text,
  ip_address varchar(80),
  expires_at timestamptz,
  revoked_at timestamptz,
  last_used_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists user_sessions_user_active_idx
  on user_sessions (user_id, revoked_at, created_at desc);
create index if not exists user_sessions_token_hash_idx on user_sessions (token_hash);

create table if not exists push_subscriptions (
  id bigserial primary key,
  user_id bigint not null references users(id) on delete cascade,
  session_id bigint references user_sessions(id) on delete set null,
  endpoint text not null unique,
  p256dh text not null,
  auth text not null,
  user_agent text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists push_subscriptions_user_idx
  on push_subscriptions (user_id, updated_at desc);

create table if not exists admin_push_subscriptions (
  id bigserial primary key,
  admin_user_id bigint not null references admin_users(id) on delete cascade,
  admin_session_id bigint references admin_sessions(id) on delete cascade,
  endpoint text not null unique,
  p256dh text not null,
  auth text not null,
  user_agent text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists admin_push_subscriptions_user_idx
  on admin_push_subscriptions (admin_user_id, updated_at desc);
alter table admin_push_subscriptions
  add column if not exists admin_session_id bigint references admin_sessions(id) on delete cascade;
create index if not exists admin_push_subscriptions_session_idx
  on admin_push_subscriptions (admin_session_id, updated_at desc);

alter table blogs add column if not exists published_date varchar(40) not null default '';
alter table blogs add column if not exists read_time varchar(40) not null default '3 min read';
alter table blogs add column if not exists meta_description text;
alter table blogs add column if not exists og_description text;
alter table blogs add column if not exists meta_image text;
alter table blogs add column if not exists og_image text;
alter table blogs add column if not exists image_alt varchar(220);
alter table blogs add column if not exists sections jsonb not null default '[]'::jsonb;
alter table blogs add column if not exists created_at timestamptz not null default now();
alter table blogs add column if not exists updated_at timestamptz not null default now();

do $$
begin
  if exists (
    select 1
    from information_schema.columns
    where table_name = 'blogs'
      and column_name = 'date'
  ) then
    execute 'update blogs set published_date = "date"::text where published_date = '''' and "date" is not null';
  end if;

  if exists (
    select 1
    from information_schema.columns
    where table_name = 'blogs'
      and column_name = 'publishedDate'
  ) then
    execute 'update blogs set published_date = "publishedDate"::text where published_date = '''' and "publishedDate" is not null';
  end if;

  if exists (
    select 1
    from information_schema.columns
    where table_name = 'blogs'
      and column_name = 'readtime'
  ) then
    execute 'update blogs set read_time = readtime::text where read_time = ''3 min read'' and readtime is not null';
  end if;

  if exists (
    select 1
    from information_schema.columns
    where table_name = 'blogs'
      and column_name = 'readTime'
  ) then
    execute 'update blogs set read_time = "readTime"::text where read_time = ''3 min read'' and "readTime" is not null';
  end if;
end $$;

create table if not exists service_locations (
  id bigserial primary key,
  name varchar(160) not null unique,
  slug varchar(180),
  is_popular boolean not null default false,
  popular_order integer,
  additional_cost_percentage numeric(6,2) not null default 0,
  discount_percentage numeric(6,2) not null default 0,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table service_locations add column if not exists slug varchar(180);
alter table service_locations add column if not exists is_popular boolean not null default false;
alter table service_locations add column if not exists popular_order integer;

with location_slugs as (
  select
    id,
    coalesce(
      nullif(trim(both '-' from regexp_replace(lower(name), '[^a-z0-9]+', '-', 'g')), ''),
      'location'
    ) as base_slug
  from service_locations
  where slug is null or slug = ''
),
numbered_slugs as (
  select
    id,
    base_slug,
    row_number() over (partition by base_slug order by id) as duplicate_index
  from location_slugs
)
update service_locations
set slug = case
  when numbered_slugs.duplicate_index = 1 then numbered_slugs.base_slug
  else numbered_slugs.base_slug || '-' || numbered_slugs.duplicate_index
end
from numbered_slugs
where service_locations.id = numbered_slugs.id;

with ordered_locations as (
  select
    id,
    row_number() over (order by name asc) as display_order
  from service_locations
  where is_active = true
)
update service_locations
set
  is_popular = true,
  popular_order = ordered_locations.display_order
from ordered_locations
where service_locations.id = ordered_locations.id
  and not exists (
    select 1
    from service_locations popular_locations
    where popular_locations.is_popular = true
  );

create index if not exists service_locations_name_idx on service_locations (name);
create index if not exists service_locations_active_idx on service_locations (is_active, name);
create index if not exists service_locations_popular_idx on service_locations (is_popular, popular_order, name);
create unique index if not exists service_locations_slug_uidx on service_locations (slug) where slug is not null;

create table if not exists service_locations_landmarks (
  id bigserial primary key,
  location_id bigint not null references service_locations(id) on delete cascade,
  name varchar(180) not null,
  status varchar(30) not null default 'active',
  slug varchar(180) not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table service_locations_landmarks add column if not exists location_id bigint references service_locations(id) on delete cascade;
alter table service_locations_landmarks add column if not exists name varchar(180);
alter table service_locations_landmarks add column if not exists status varchar(30) not null default 'active';
alter table service_locations_landmarks add column if not exists slug varchar(180);
alter table service_locations_landmarks add column if not exists created_at timestamptz not null default now();
alter table service_locations_landmarks add column if not exists updated_at timestamptz not null default now();

create index if not exists service_locations_landmarks_location_idx
  on service_locations_landmarks (location_id, status, name);
create unique index if not exists service_locations_landmarks_location_slug_uidx
  on service_locations_landmarks (location_id, slug)
  where slug is not null;

create table if not exists vehicle_companies (
  id integer generated by default as identity primary key,
  vehicle_type varchar(20) not null check (vehicle_type in ('car', 'bike')),
  name varchar(160) not null,
  slug varchar(180) not null,
  logo text,
  is_popular boolean not null default false,
  popular_order integer,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists vehicle_companies_type_name_idx on vehicle_companies (vehicle_type, name);
create index if not exists vehicle_companies_popular_idx on vehicle_companies (vehicle_type, is_popular, popular_order, name);
create unique index if not exists vehicle_companies_type_slug_uidx on vehicle_companies (vehicle_type, slug);

create table if not exists vehicle_models (
  id integer generated by default as identity primary key,
  manufacturer_id integer not null references vehicle_companies(id) on delete cascade,
  tier_id bigint,
  name varchar(180) not null,
  fuel_type varchar(60),
  engine_cc numeric(8,2),
  is_ev boolean not null default false,
  status varchar(60),
  image text,
  image_prompt text,
  image_status varchar(20) not null default 'pending',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists vehicle_models_manufacturer_idx on vehicle_models (manufacturer_id, name);
create index if not exists vehicle_models_name_idx on vehicle_models (name);
create index if not exists vehicle_models_tier_idx on vehicle_models (tier_id);

update vehicle_models
set image = regexp_replace(image, '^https?://[^/]+/(ridenrevive/.*)$', '/\1')
where image ~ '^https?://[^/]+/ridenrevive/';

create table if not exists vehicle_tiers (
  id bigserial primary key,
  vehicle_type varchar(20) not null check (vehicle_type in ('car', 'bike', 'ev-car', 'ev-bike')),
  tier_level integer not null check (tier_level between 1 and 6),
  name varchar(80) not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists vehicle_tiers_type_level_uidx
  on vehicle_tiers (vehicle_type, tier_level);
create unique index if not exists vehicle_tiers_type_name_uidx
  on vehicle_tiers (vehicle_type, name);

insert into vehicle_tiers (vehicle_type, tier_level, name)
select vehicle_type, tier_level, name
from (
  values
    ('car', 1, 'Economy'),
    ('car', 2, 'Standard'),
    ('car', 3, 'Premium'),
    ('car', 4, 'Executive'),
    ('car', 5, 'Luxury'),
    ('car', 6, 'Ultra Luxury'),
    ('bike', 1, 'Economy'),
    ('bike', 2, 'Standard'),
    ('bike', 3, 'Premium'),
    ('bike', 4, 'Executive'),
    ('bike', 5, 'Luxury'),
    ('bike', 6, 'Ultra Luxury'),
    ('ev-car', 1, 'Economy'),
    ('ev-car', 2, 'Standard'),
    ('ev-car', 3, 'Premium'),
    ('ev-car', 4, 'Executive'),
    ('ev-car', 5, 'Luxury'),
    ('ev-car', 6, 'Ultra Luxury'),
    ('ev-bike', 1, 'Economy'),
    ('ev-bike', 2, 'Standard'),
    ('ev-bike', 3, 'Premium'),
    ('ev-bike', 4, 'Executive'),
    ('ev-bike', 5, 'Luxury'),
    ('ev-bike', 6, 'Ultra Luxury')
) as seed_tiers(vehicle_type, tier_level, name)
on conflict (vehicle_type, tier_level) do nothing;

create table if not exists packages (
  id bigserial primary key,
  package_type varchar(40) not null default 'service_package' check (package_type in ('service_package', 'engine_repair', 'car_ac', 'battery_service', 'tyre_wheel_service')),
  vehicle_type varchar(40) not null check (vehicle_type in ('bike', 'car', 'ev-bike', 'ev-car', '["car","bike"]')),
  name varchar(160) not null,
  display_order integer not null default 999,
  estimated_duration varchar(80) not null,
  features jsonb not null default '[]'::jsonb,
  highlighted_features jsonb not null default '[]'::jsonb,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists packages_type_vehicle_idx on packages (package_type, vehicle_type, is_active, name);

create table if not exists model_package_prices (
  model_id integer not null references vehicle_models(id) on delete cascade,
  package_id bigint not null references packages(id) on delete cascade,
  base_price numeric(10,2) not null default 0,
  discount_percentage numeric(6,2) not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (model_id, package_id)
);

alter table model_package_prices add column if not exists discount_percentage numeric(6,2) not null default 0;

create index if not exists model_package_prices_package_idx on model_package_prices (package_id);

create table if not exists add_on_items (
  id bigserial primary key,
  vehicle_type varchar(20) not null default 'car' check (vehicle_type in ('car', 'bike')),
  name varchar(160) not null,
  price numeric(10,2) not null default 0,
  discount numeric(6,2) not null default 0,
  description_points jsonb not null default '[]'::jsonb,
  image text,
  is_labour_included boolean not null default false,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists add_on_items_vehicle_name_idx on add_on_items (vehicle_type, name);
create index if not exists add_on_items_active_name_idx on add_on_items (is_active, name);
create index if not exists add_on_items_labour_idx on add_on_items (is_labour_included, name);

create table if not exists amc_packages (
  id bigserial primary key,
  vehicle_type varchar(20) not null check (vehicle_type in ('car', 'bike', 'ev-car', 'ev-bike')),
  name varchar(160) not null,
  display_order integer not null default 999,
  ideal_for text not null,
  sections jsonb not null default '[]'::jsonb,
  validity_months integer not null default 12,
  terms jsonb not null default '[]'::jsonb,
  is_active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists amc_packages_vehicle_name_uidx
  on amc_packages (vehicle_type, name);
create index if not exists amc_packages_vehicle_order_idx
  on amc_packages (vehicle_type, display_order, name);

insert into amc_packages (
  vehicle_type,
  name,
  display_order,
  ideal_for,
  sections,
  validity_months,
  terms,
  is_active
)
values
  (
    'bike',
    'BASIC CARE PLAN',
    1,
    'Low usage vehicles (under 8,000 km/year)',
    $$[
      {
        "heading": "What's Included",
        "items": [
          "2 Scheduled Bike Services (Labor Included)",
          "1 General Inspection (40+ checkpoints)",
          "Fluid Top-Up (Coolant, Brake, Washer)",
          "Battery Health Check",
          "Brake & Suspension Inspection",
          "Tyre Pressure & Condition Check",
          "Complimentary Bike Wash (2 times)"
        ]
      },
      {
        "heading": "Not Included",
        "items": [
          "Engine oil & filters",
          "Spare parts / consumables"
        ]
      }
    ]$$::jsonb,
    12,
    $$["Price: 4,999 / Year", "Plan validity: 12 Months", "AMC is non-transferable", "Terms can be customized if required"]$$::jsonb,
    true
  ),
  (
    'bike',
    'SMART CARE PLAN',
    2,
    'Moderate usage (8,000-15,000 km/year)',
    $$[
      {
        "heading": "What's Included",
        "items": [
          "3 Scheduled Services (Labor Included)",
          "Engine Oil Replacement (1 time)",
          "Oil Filter Replacement (1 time)",
          "50+ Point Inspection (Every Service)",
          "Wheel Alignment Check (1 time)",
          "Bike Wash (3 times)"
        ]
      },
      {
        "heading": "Additional Benefits",
        "items": [
          "10% Discount on Repairs",
          "Priority Booking Slots"
        ]
      },
      {
        "heading": "Not Included",
        "items": [
          "Major parts (clutch, suspension, etc.)",
          "Additional oil changes beyond plan"
        ]
      }
    ]$$::jsonb,
    12,
    $$["Price: 8,999 / Year", "Plan validity: 12 Months", "AMC is non-transferable", "Terms can be customized if required"]$$::jsonb,
    true
  ),
  (
    'bike',
    'PREMIUM CARE PLAN',
    3,
    'High usage / premium bike owners (15,000+ km/year)',
    $$[
      {
        "heading": "What's Included",
        "items": [
          "4 Scheduled Services (Complete)",
          "Engine Oil Replacement (2 times)",
          "All Filters Included (Air, Oil)",
          "Pickup & Drop (Unlimited)",
          "Free Wheel Alignment & Balancing (2 times)",
          "Brake Cleaning & Servicing",
          "Interior Vacuum + Exterior Wash (Every Service)"
        ]
      },
      {
        "heading": "Premium Benefits",
        "items": [
          "15% Discount on Repairs",
          "Free Minor Consumables (nuts, bolts, greases)",
          "Priority Express Service"
        ]
      }
    ]$$::jsonb,
    12,
    $$["Price: 14,999 / Year", "Plan validity: 12 Months", "AMC is non-transferable", "Terms can be customized if required"]$$::jsonb,
    true
  ),
  (
    'bike',
    'ELITE PRO PLAN',
    4,
    'Zero-hassle ownership & full coverage',
    $$[
      {
        "heading": "What's Included",
        "items": [
          "Everything in Premium Plan +",
          "Wear & Tear Parts Coverage (Selected Items)",
          "Brake Pads (1 replacement)",
          "Wiper Blades",
          "Bulbs & Fuses",
          "Roadside Assistance (RSA)",
          "Emergency Breakdown Support",
          "Free AC Check & Gas Top-Up (1 time)"
        ]
      },
      {
        "heading": "Exclusive Benefits",
        "items": [
          "20% Discount on All Repairs",
          "Dedicated Service Manager",
          "Same-Day Service Priority"
        ]
      }
    ]$$::jsonb,
    12,
    $$["Price: 21,999 / Year", "Plan validity: 12 Months", "AMC is non-transferable", "Terms can be customized if required"]$$::jsonb,
    true
  )
on conflict (vehicle_type, name) do update
set
  display_order = excluded.display_order,
  ideal_for = excluded.ideal_for,
  sections = excluded.sections,
  validity_months = excluded.validity_months,
  terms = excluded.terms,
  is_active = excluded.is_active,
  updated_at = now();

create table if not exists company_amc_package_prices (
  company_id integer not null references vehicle_companies(id) on delete cascade,
  tier_id bigint references vehicle_tiers(id) on delete cascade,
  amc_package_id bigint not null references amc_packages(id) on delete cascade,
  base_price numeric(10,2) not null default 0,
  discount_percentage numeric(6,2) not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (company_id, tier_id, amc_package_id)
);

create index if not exists company_amc_package_prices_package_idx
  on company_amc_package_prices (amc_package_id);
create index if not exists company_amc_package_prices_tier_idx
  on company_amc_package_prices (tier_id);

alter table company_amc_package_prices add column if not exists tier_id bigint references vehicle_tiers(id) on delete cascade;
alter table company_amc_package_prices add column if not exists discount_percentage numeric(6,2) not null default 0;

insert into company_amc_package_prices (
  company_id,
  tier_id,
  amc_package_id,
  base_price,
  created_at,
  updated_at
)
select
  existing_prices.company_id,
  vehicle_tiers.id,
  existing_prices.amc_package_id,
  existing_prices.base_price,
  existing_prices.created_at,
  now()
from company_amc_package_prices existing_prices
join vehicle_companies on vehicle_companies.id = existing_prices.company_id
join amc_packages on amc_packages.id = existing_prices.amc_package_id
join vehicle_tiers on vehicle_tiers.vehicle_type = vehicle_companies.vehicle_type
where existing_prices.tier_id is null
  and vehicle_companies.vehicle_type = 'car'
  and amc_packages.vehicle_type = 'car'
  and not exists (
    select 1
    from company_amc_package_prices current_prices
    where current_prices.company_id = existing_prices.company_id
      and current_prices.tier_id = vehicle_tiers.id
      and current_prices.amc_package_id = existing_prices.amc_package_id
  );

delete from company_amc_package_prices where tier_id is null;

do $$
begin
  alter table company_amc_package_prices drop constraint if exists company_amc_package_prices_pkey;
  alter table company_amc_package_prices alter column tier_id set not null;
  alter table company_amc_package_prices add constraint company_amc_package_prices_pkey
    primary key (company_id, tier_id, amc_package_id);
end $$;

create table if not exists service_page_views (
  id bigserial primary key,
  page_path varchar(220) not null,
  service_slug varchar(80) not null,
  service_label varchar(120) not null,
  referrer text,
  user_agent text,
  ip_address varchar(80),
  viewed_at timestamptz not null default now()
);

create index if not exists service_page_views_viewed_at_idx on service_page_views (viewed_at desc);
create index if not exists service_page_views_service_idx on service_page_views (service_slug, viewed_at desc);

create table if not exists website_page_views (
  id bigserial primary key,
  page_path varchar(500) not null,
  service_slug varchar(80),
  service_label varchar(120),
  referrer text,
  user_agent text,
  ip_address varchar(80),
  viewed_at timestamptz not null default now()
);

create index if not exists website_page_views_viewed_at_idx on website_page_views (viewed_at desc);
create index if not exists website_page_views_path_idx on website_page_views (page_path, viewed_at desc);
create index if not exists website_page_views_service_idx on website_page_views (service_slug, viewed_at desc);
create index if not exists website_page_views_ip_idx on website_page_views (ip_address, viewed_at desc);