DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_status') THEN
    CREATE TYPE user_status AS ENUM ('active', 'invited', 'suspended');
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'prescription_status') THEN
    CREATE TYPE prescription_status AS ENUM ('received', 'under_review', 'approved', 'dispensed', 'rejected');
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'alert_severity') THEN
    CREATE TYPE alert_severity AS ENUM ('info', 'warning', 'critical');
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS tenants (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name varchar(180) NOT NULL,
  country_code char(2) NOT NULL,
  default_locale varchar(10) NOT NULL DEFAULT 'ar-SA',
  data_residency_region varchar(80) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS pharmacies (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  name varchar(180) NOT NULL,
  license_number varchar(80) NOT NULL,
  city varchar(120) NOT NULL,
  timezone varchar(80) NOT NULL DEFAULT 'Asia/Riyadh',
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, license_number)
);

CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  pharmacy_id uuid REFERENCES pharmacies(id),
  email citext NOT NULL,
  phone varchar(40),
  full_name varchar(180) NOT NULL,
  password_hash text NOT NULL,
  status user_status NOT NULL DEFAULT 'active',
  two_factor_enabled boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, email)
);

CREATE TABLE IF NOT EXISTS products (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  sku varchar(80) NOT NULL,
  barcode varchar(80),
  qr_code varchar(160),
  name_en varchar(220) NOT NULL,
  name_ar varchar(220) NOT NULL,
  active_ingredient varchar(220),
  dosage_form varchar(120),
  strength varchar(80),
  storage_type varchar(40) NOT NULL DEFAULT 'ambient',
  reorder_point integer NOT NULL DEFAULT 0,
  controlled_drug boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, sku),
  UNIQUE (tenant_id, barcode)
);

CREATE TABLE IF NOT EXISTS inventory_stock_lots (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  pharmacy_id uuid NOT NULL REFERENCES pharmacies(id),
  product_id uuid NOT NULL REFERENCES products(id),
  lot_number varchar(100) NOT NULL,
  rfid_tag varchar(160),
  expiry_date date NOT NULL,
  quantity_on_hand integer NOT NULL CHECK (quantity_on_hand >= 0),
  reserved_quantity integer NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0),
  unit_cost numeric(12, 4),
  received_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, pharmacy_id, product_id, lot_number)
);

CREATE TABLE IF NOT EXISTS patients (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  national_id_hash text,
  full_name varchar(180) NOT NULL,
  date_of_birth date,
  phone varchar(40),
  preferred_locale varchar(10) NOT NULL DEFAULT 'ar-SA',
  consent_version varchar(40),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS patient_chronic_conditions (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  patient_id uuid NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
  condition_code varchar(80) NOT NULL,
  condition_name_ar varchar(180) NOT NULL,
  condition_name_en varchar(180),
  diagnosed_at date,
  status varchar(40) NOT NULL DEFAULT 'active'
);

CREATE TABLE IF NOT EXISTS refill_orders (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  patient_id uuid NOT NULL REFERENCES patients(id),
  product_id uuid REFERENCES products(id),
  medication_name varchar(220) NOT NULL,
  delivery_method varchar(80) NOT NULL,
  delivery_address text,
  status varchar(40) NOT NULL DEFAULT 'new',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS diagnostic_orders (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  patient_id uuid NOT NULL REFERENCES patients(id),
  service_name varchar(220) NOT NULL,
  service_type varchar(80) NOT NULL,
  location_name varchar(220) NOT NULL,
  scheduled_at timestamptz,
  status varchar(40) NOT NULL DEFAULT 'confirmed',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  actor_user_id uuid REFERENCES users(id),
  action varchar(120) NOT NULL,
  entity_type varchar(120) NOT NULL,
  entity_id uuid,
  ip_address inet,
  user_agent text,
  metadata jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_stock_lots_expiry ON inventory_stock_lots (tenant_id, pharmacy_id, expiry_date);
CREATE INDEX IF NOT EXISTS idx_products_search ON products (tenant_id, name_ar, name_en);
CREATE INDEX IF NOT EXISTS idx_refill_orders_patient ON refill_orders (tenant_id, patient_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_diagnostic_orders_patient ON diagnostic_orders (tenant_id, patient_id, created_at DESC);