CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS organizations (
  id text PRIMARY KEY,
  mode text NOT NULL CHECK (mode IN ('white_label','franchise')),
  brand_name text NOT NULL,
  legal_name text NOT NULL,
  nit text,
  department text NOT NULL,
  city text NOT NULL,
  city_class text NOT NULL CHECK (city_class IN ('pequena','intermedia','grande')),
  status text NOT NULL CHECK (status IN ('pending','trial','active','suspended','rejected','terminated')) DEFAULT 'pending',
  trip_fee numeric(12,2) NOT NULL DEFAULT 0,
  franchise_percent numeric(7,4) NOT NULL DEFAULT 0,
  primary_color text NOT NULL DEFAULT '#ff411f',
  secondary_color text NOT NULL DEFAULT '#0f172a',
  logo_url text,
  contract_status text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS organizations_mode_status_idx ON organizations(mode,status);
CREATE INDEX IF NOT EXISTS organizations_city_idx ON organizations(department,city);

CREATE TABLE IF NOT EXISTS users (
  id text PRIMARY KEY,
  organization_id text REFERENCES organizations(id) ON DELETE CASCADE,
  email text UNIQUE NOT NULL,
  name text NOT NULL,
  role text NOT NULL,
  password_salt text NOT NULL,
  password_hash text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS users_org_idx ON users(organization_id);

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

CREATE TABLE IF NOT EXISTS contracts (
  id text PRIMARY KEY,
  version text UNIQUE NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  body_hash text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS contract_acceptances (
  id text PRIMARY KEY,
  contract_id text NOT NULL REFERENCES contracts(id),
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  signer_name text NOT NULL,
  signer_document text NOT NULL,
  accepted_at timestamptz NOT NULL DEFAULT now(),
  ip inet,
  user_agent text,
  body_hash text NOT NULL
);

CREATE TABLE IF NOT EXISTS documents (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  filename text NOT NULL,
  doc_type text NOT NULL,
  storage_key text,
  status text NOT NULL CHECK(status IN ('pending','approved','rejected')) DEFAULT 'pending',
  review_notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  reviewed_at timestamptz
);
CREATE INDEX IF NOT EXISTS documents_org_status_idx ON documents(organization_id,status);

CREATE TABLE IF NOT EXISTS merchants (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  status text NOT NULL CHECK(status IN ('pending','active','suspended','rejected')) DEFAULT 'pending',
  orders integer NOT NULL DEFAULT 0,
  balance numeric(12,2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchants_org_status_idx ON merchants(organization_id,status);

CREATE TABLE IF NOT EXISTS merchant_branches (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  name text NOT NULL,
  address text,
  location geography(Point,4326),
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_branches_org_idx ON merchant_branches(organization_id);
CREATE INDEX IF NOT EXISTS merchant_branches_location_gix ON merchant_branches USING GIST(location);

CREATE TABLE IF NOT EXISTS drivers (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  email text,
  phone text,
  vehicle text NOT NULL DEFAULT 'Moto',
  approval text NOT NULL CHECK(approval IN ('pending','approved','rejected')) DEFAULT 'pending',
  availability text NOT NULL CHECK(availability IN ('online','offline','busy')) DEFAULT 'offline',
  rating numeric(4,2) NOT NULL DEFAULT 0,
  trips integer NOT NULL DEFAULT 0,
  current_location geography(Point,4326),
  last_location_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS drivers_org_availability_idx ON drivers(organization_id,availability);
CREATE INDEX IF NOT EXISTS drivers_location_gix ON drivers USING GIST(current_location);

CREATE TABLE IF NOT EXISTS driver_locations (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  location geography(Point,4326) NOT NULL,
  accuracy_m numeric(10,2),
  speed_kmh numeric(10,2),
  heading numeric(7,2),
  captured_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS driver_locations_driver_time_idx ON driver_locations(driver_id,captured_at DESC);
CREATE INDEX IF NOT EXISTS driver_locations_location_gix ON driver_locations USING GIST(location);

CREATE TABLE IF NOT EXISTS territories (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  kind text NOT NULL DEFAULT 'service_area',
  geometry geometry(MultiPolygon,4326) NOT NULL,
  exclusive boolean NOT NULL DEFAULT false,
  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 territories_org_idx ON territories(organization_id);
CREATE INDEX IF NOT EXISTS territories_geometry_gix ON territories USING GIST(geometry);

CREATE TABLE IF NOT EXISTS trips (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  external_reference text,
  merchant_id text REFERENCES merchants(id),
  branch_id text REFERENCES merchant_branches(id),
  customer_name text NOT NULL,
  customer_phone text,
  pickup_address text NOT NULL,
  dropoff_address text NOT NULL,
  pickup_location geography(Point,4326),
  dropoff_location geography(Point,4326),
  driver_id text REFERENCES drivers(id),
  amount numeric(12,2) NOT NULL DEFAULT 0,
  delivery_fee numeric(12,2) NOT NULL DEFAULT 0,
  status text NOT NULL CHECK(status IN ('requested','searching','assigned','en_route_store','at_store','picked_up','en_route_customer','at_customer','completed','cancelled','failed')) DEFAULT 'requested',
  platform_fee numeric(12,2) NOT NULL DEFAULT 0,
  pickup_pin text,
  delivery_pin text,
  created_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);
CREATE UNIQUE INDEX IF NOT EXISTS trips_org_ext_ref_uq ON trips(organization_id,external_reference) WHERE external_reference IS NOT NULL;
CREATE INDEX IF NOT EXISTS trips_org_created_idx ON trips(organization_id,created_at DESC);
CREATE INDEX IF NOT EXISTS trips_pickup_gix ON trips USING GIST(pickup_location);
CREATE INDEX IF NOT EXISTS trips_dropoff_gix ON trips USING GIST(dropoff_location);

CREATE TABLE IF NOT EXISTS trip_events (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  event_type text NOT NULL,
  actor_user_id text,
  detail_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS trip_events_trip_created_idx ON trip_events(trip_id,created_at);

CREATE TABLE IF NOT EXISTS charges (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id),
  charge_type text NOT NULL,
  amount numeric(12,2) NOT NULL,
  currency char(3) NOT NULL DEFAULT 'BOB',
  status text NOT NULL CHECK(status IN ('pending','invoiced','paid','void')) DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS charges_trip_type_uq ON charges(trip_id,charge_type) WHERE trip_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS charges_org_created_idx ON charges(organization_id,created_at DESC);

CREATE TABLE IF NOT EXISTS ledger_entries (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id),
  entry_type text NOT NULL,
  amount numeric(12,2) NOT NULL,
  currency char(3) NOT NULL DEFAULT 'BOB',
  reference text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ledger_org_created_idx ON ledger_entries(organization_id,created_at DESC);

CREATE TABLE IF NOT EXISTS banners (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  title text NOT NULL,
  placement text NOT NULL DEFAULT 'Inicio',
  image_url text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS app_builds (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  app text NOT NULL,
  platform text NOT NULL,
  package_id text,
  version text,
  status text NOT NULL DEFAULT 'planned',
  artifact_url text,
  config_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS app_builds_org_idx ON app_builds(organization_id);

CREATE TABLE IF NOT EXISTS webhook_endpoints (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  url text NOT NULL,
  secret_ciphertext text NOT NULL,
  events text[] NOT NULL DEFAULT '{}',
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id text PRIMARY KEY,
  actor_user_id text,
  organization_id text REFERENCES organizations(id) ON DELETE SET NULL,
  action text NOT NULL,
  entity_type text,
  entity_id text,
  detail_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  ip inet,
  correlation_id uuid NOT NULL DEFAULT gen_random_uuid(),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS audit_org_created_idx ON audit_logs(organization_id,created_at DESC);

CREATE TABLE IF NOT EXISTS idempotency_keys (
  scope text NOT NULL,
  route_key text NOT NULL,
  idem_key text NOT NULL,
  request_hash text NOT NULL,
  response_json jsonb,
  status_code integer,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL DEFAULT now() + interval '24 hours',
  PRIMARY KEY(scope,route_key,idem_key)
);
CREATE INDEX IF NOT EXISTS idempotency_exp_idx ON idempotency_keys(expires_at);

INSERT INTO settings(key,value) VALUES
  ('platformName','"ClickDelivery Platform"'::jsonb),
  ('currency','"BOB"'::jsonb),
  ('smallTripFee','0.99'::jsonb),
  ('largeTripFee','1.39'::jsonb),
  ('franchisePercent','8'::jsonb)
ON CONFLICT(key) DO NOTHING;