create extension if not exists postgis;
create extension if not exists pgcrypto;

create table if not exists users (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  first_name text not null default '',
  last_name text not null default '',
  password_hash text not null,
  role text not null default 'user' check (role in ('admin', 'user')),
  can_edit_vehicle_inspection boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table users add column if not exists first_name text not null default '';
alter table users add column if not exists last_name text not null default '';
alter table users add column if not exists can_edit_vehicle_inspection boolean not null default false;

create table if not exists vehicles (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  plate_number text,
  color_key text not null default 'visau-indigo',
  vehicle_type text not null default 'passenger' check (vehicle_type in ('passenger', 'delivery')),
  inspection_due_date date,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table vehicles add column if not exists color_key text not null default 'visau-indigo';
alter table vehicles add column if not exists vehicle_type text not null default 'passenger';
alter table vehicles add column if not exists inspection_due_date date;

create table if not exists devices (
  id uuid primary key default gen_random_uuid(),
  imei varchar(17) not null unique,
  protocol_name text not null default 'trackerking',
  phone_number text,
  vehicle_id uuid references vehicles(id) on delete set null,
  status text not null default 'offline' check (status in ('online', 'delayed', 'offline')),
  last_seen timestamptz,
  last_device_time timestamptz,
  battery double precision,
  gsm_signal double precision,
  ignition boolean,
  charging boolean,
  defense boolean,
  gps_valid boolean,
  satellite_count integer,
  position_type text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table devices add column if not exists phone_number text;
alter table devices add column if not exists last_device_time timestamptz;
alter table devices add column if not exists battery double precision;
alter table devices add column if not exists gsm_signal double precision;
alter table devices add column if not exists ignition boolean;
alter table devices add column if not exists charging boolean;
alter table devices add column if not exists defense boolean;
alter table devices add column if not exists gps_valid boolean;
alter table devices add column if not exists satellite_count integer;
alter table devices add column if not exists position_type text;

create table if not exists user_vehicle_permissions (
  user_id uuid not null references users(id) on delete cascade,
  vehicle_id uuid not null references vehicles(id) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (user_id, vehicle_id)
);

create table if not exists positions (
  id bigserial primary key,
  device_id uuid not null references devices(id) on delete cascade,
  vehicle_id uuid references vehicles(id) on delete set null,
  imei varchar(17) not null,
  protocol_name text not null,
  raw_payload text not null,
  device_time timestamptz,
  server_time timestamptz not null,
  lat double precision not null,
  lon double precision not null,
  speed double precision,
  heading double precision,
  ignition boolean,
  battery double precision,
  gsm_signal double precision,
  satellite_count integer,
  gps_valid boolean,
  charging boolean,
  defense boolean,
  position_type text,
  geom geography(point, 4326) not null
);

alter table positions add column if not exists satellite_count integer;
alter table positions add column if not exists gps_valid boolean;
alter table positions add column if not exists charging boolean;
alter table positions add column if not exists defense boolean;
alter table positions add column if not exists position_type text;

create table if not exists last_positions (
  device_id uuid primary key references devices(id) on delete cascade,
  vehicle_id uuid references vehicles(id) on delete set null,
  imei varchar(17) not null,
  lat double precision not null,
  lon double precision not null,
  speed double precision,
  heading double precision,
  device_time timestamptz,
  server_time timestamptz not null,
  ignition boolean,
  battery double precision,
  gsm_signal double precision,
  satellite_count integer,
  gps_valid boolean,
  charging boolean,
  defense boolean,
  position_type text,
  geom geography(point, 4326) not null,
  updated_at timestamptz not null default now()
);

alter table last_positions add column if not exists satellite_count integer;
alter table last_positions add column if not exists gps_valid boolean;
alter table last_positions add column if not exists charging boolean;
alter table last_positions add column if not exists defense boolean;
alter table last_positions add column if not exists position_type text;

create table if not exists unassigned_devices (
  imei varchar(17) primary key,
  protocol_name text not null,
  raw_payload text not null,
  first_seen timestamptz not null,
  last_seen timestamptz not null
);

create table if not exists vehicle_alerts (
  id uuid primary key default gen_random_uuid(),
  vehicle_id uuid not null references vehicles(id) on delete cascade,
  device_id uuid references devices(id) on delete set null,
  imei varchar(17),
  alert_type text not null,
  message text not null,
  speed double precision not null,
  triggered_at timestamptz not null,
  acknowledged_at timestamptz,
  acknowledged_by uuid references users(id) on delete set null,
  created_at timestamptz not null default now()
);

create table if not exists system_settings (
  key text primary key,
  value jsonb not null default '{}'::jsonb,
  updated_at timestamptz not null default now()
);

create index if not exists idx_devices_vehicle_id on devices(vehicle_id);
create index if not exists idx_devices_last_seen on devices(last_seen desc);
create index if not exists idx_positions_vehicle_time on positions(vehicle_id, server_time desc);
create index if not exists idx_positions_device_time on positions(device_id, server_time desc);
create index if not exists idx_positions_geom on positions using gist(geom);
create index if not exists idx_last_positions_vehicle on last_positions(vehicle_id);
create index if not exists idx_vehicle_alerts_vehicle_active on vehicle_alerts(vehicle_id, alert_type, acknowledged_at);
create index if not exists idx_vehicle_alerts_triggered_at on vehicle_alerts(triggered_at desc);
