-- ClickDelivery V0.6.1 Backend Mobile Production
-- Refresh tokens, sincronizacion offline, GPS batch, catalogo/pedidos, marketplace y ganancias/liquidaciones.

CREATE TABLE IF NOT EXISTS mobile_refresh_tokens (
  id text PRIMARY KEY,
  organization_id text REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_id text REFERENCES mobile_devices(id) ON DELETE SET NULL,
  token_hash text NOT NULL UNIQUE,
  expires_at timestamptz NOT NULL,
  last_used_at timestamptz,
  revoked_at timestamptz,
  rotated_from_id text REFERENCES mobile_refresh_tokens(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS mobile_refresh_tokens_user_idx ON mobile_refresh_tokens(user_id,expires_at DESC);
CREATE INDEX IF NOT EXISTS mobile_refresh_tokens_exp_idx ON mobile_refresh_tokens(expires_at) WHERE revoked_at IS NULL;

CREATE TABLE IF NOT EXISTS mobile_sync_operations (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_id text REFERENCES mobile_devices(id) ON DELETE SET NULL,
  client_operation_id text NOT NULL,
  operation_type text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  status text NOT NULL DEFAULT 'received' CHECK(status IN ('received','applied','rejected','failed')),
  result jsonb,
  error_code text,
  captured_at timestamptz,
  processed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id,client_operation_id)
);
CREATE INDEX IF NOT EXISTS mobile_sync_operations_user_created_idx ON mobile_sync_operations(user_id,created_at DESC);

ALTER TABLE driver_locations ADD COLUMN IF NOT EXISTS client_point_id text;
CREATE UNIQUE INDEX IF NOT EXISTS driver_locations_client_point_uidx
  ON driver_locations(driver_id,client_point_id) WHERE client_point_id IS NOT NULL;

ALTER TABLE trip_tracking_points ADD COLUMN IF NOT EXISTS client_point_id text;
CREATE UNIQUE INDEX IF NOT EXISTS trip_tracking_points_client_point_uidx
  ON trip_tracking_points(driver_id,client_point_id) WHERE client_point_id IS NOT NULL;

ALTER TABLE trips ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();

ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS default_delivery_fee numeric(12,2) NOT NULL DEFAULT 0 CHECK(default_delivery_fee >= 0);

CREATE TABLE IF NOT EXISTS merchant_products (
  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,
  description text,
  category text,
  price numeric(12,2) NOT NULL CHECK(price >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  image_url text,
  stock_status text NOT NULL DEFAULT 'available' CHECK(stock_status IN ('available','out_of_stock','hidden')),
  active boolean NOT NULL DEFAULT true,
  sort_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_products_org_merchant_idx ON merchant_products(organization_id,merchant_id,active,sort_order);

CREATE TABLE IF NOT EXISTS customer_addresses (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  customer_id text NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  label text,
  address text NOT NULL,
  location geography(Point,4326),
  reference text,
  is_default 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 customer_addresses_customer_idx ON customer_addresses(customer_id,active);
CREATE INDEX IF NOT EXISTS customer_addresses_location_gix ON customer_addresses USING GIST(location);

CREATE TABLE IF NOT EXISTS merchant_orders (
  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,
  branch_id text REFERENCES merchant_branches(id) ON DELETE SET NULL,
  customer_id text NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  trip_id text UNIQUE REFERENCES trips(id) ON DELETE SET NULL,
  external_reference text,
  status text NOT NULL DEFAULT 'pending_confirmation'
    CHECK(status IN ('pending_confirmation','confirmed','preparing','ready','completed','cancelled')),
  payment_status text NOT NULL DEFAULT 'pending'
    CHECK(payment_status IN ('pending','authorized','paid','failed','partially_refunded','refunded')),
  payment_method text,
  subtotal numeric(12,2) NOT NULL DEFAULT 0 CHECK(subtotal >= 0),
  delivery_fee numeric(12,2) NOT NULL DEFAULT 0 CHECK(delivery_fee >= 0),
  total numeric(12,2) NOT NULL DEFAULT 0 CHECK(total >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  delivery_address text NOT NULL,
  delivery_location geography(Point,4326),
  delivery_reference text,
  notes text,
  cancel_reason text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS merchant_orders_org_ext_ref_uidx
  ON merchant_orders(organization_id,external_reference) WHERE external_reference IS NOT NULL;
CREATE INDEX IF NOT EXISTS merchant_orders_customer_created_idx ON merchant_orders(customer_id,created_at DESC);
CREATE INDEX IF NOT EXISTS merchant_orders_merchant_status_idx ON merchant_orders(merchant_id,status,created_at DESC);

CREATE TABLE IF NOT EXISTS merchant_order_items (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  order_id text NOT NULL REFERENCES merchant_orders(id) ON DELETE CASCADE,
  product_id text REFERENCES merchant_products(id) ON DELETE SET NULL,
  product_name text NOT NULL,
  quantity integer NOT NULL CHECK(quantity BETWEEN 1 AND 999),
  unit_price numeric(12,2) NOT NULL CHECK(unit_price >= 0),
  line_total numeric(12,2) NOT NULL CHECK(line_total >= 0),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_order_items_order_idx ON merchant_order_items(order_id,id);

CREATE TABLE IF NOT EXISTS driver_wallet_entries (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id) ON DELETE SET NULL,
  entry_type text NOT NULL CHECK(entry_type IN ('delivery_earning','bonus','adjustment','settlement_debit')),
  amount numeric(12,2) NOT NULL,
  currency char(3) NOT NULL DEFAULT 'BOB',
  settlement_id text,
  description text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS driver_wallet_trip_earning_uidx
  ON driver_wallet_entries(trip_id,entry_type) WHERE trip_id IS NOT NULL AND entry_type='delivery_earning';
CREATE INDEX IF NOT EXISTS driver_wallet_driver_created_idx ON driver_wallet_entries(driver_id,created_at DESC);

CREATE TABLE IF NOT EXISTS driver_settlements (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  amount numeric(12,2) NOT NULL CHECK(amount >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','processing','paid','failed','void')),
  method text,
  reference text,
  period_from timestamptz,
  period_to timestamptz,
  paid_at timestamptz,
  created_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS driver_settlements_driver_created_idx ON driver_settlements(driver_id,created_at DESC);

ALTER TABLE driver_wallet_entries
  DROP CONSTRAINT IF EXISTS driver_wallet_entries_settlement_fk;
ALTER TABLE driver_wallet_entries
  ADD CONSTRAINT driver_wallet_entries_settlement_fk
  FOREIGN KEY(settlement_id) REFERENCES driver_settlements(id) ON DELETE SET NULL;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'mobile_sync_operations','merchant_products','customer_addresses','merchant_orders',
    'merchant_order_items','driver_wallet_entries','driver_settlements'
  ]
  LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
    EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I', t);
    EXECUTE format(
      'CREATE POLICY tenant_isolation ON %I FOR ALL USING (app_tenant_allowed(organization_id)) WITH CHECK (app_tenant_allowed(organization_id))',
      t
    );
  END LOOP;
END $$;

-- Refresh tokens are accessed before tenant context is known, same as sessions/users.
ALTER TABLE mobile_refresh_tokens DISABLE ROW LEVEL SECURITY;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='clickdelivery_app') THEN
    GRANT SELECT,INSERT,UPDATE,DELETE ON mobile_refresh_tokens,mobile_sync_operations,merchant_products,
      customer_addresses,merchant_orders,merchant_order_items,driver_wallet_entries,driver_settlements TO clickdelivery_app;
    GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA public TO clickdelivery_app;
  END IF;
END $$;


CREATE OR REPLACE FUNCTION clickdelivery_touch_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at=now();
  RETURN NEW;
END $$;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['trips','merchant_products','customer_addresses','merchant_orders','driver_settlements']
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS touch_updated_at ON %I',t);
    EXECUTE format('CREATE TRIGGER touch_updated_at BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION clickdelivery_touch_updated_at()',t);
  END LOOP;
END $$;