-- ACS Diagnose production schema baseline for MySQL 8 / MariaDB 10.6+.
-- Apply to a dedicated database user. Application-layer encryption is required for PII columns.

CREATE TABLE accounts (
  account_id VARCHAR(64) PRIMARY KEY,
  mobile_ciphertext TEXT NOT NULL,
  mobile_lookup_hmac CHAR(64) NOT NULL UNIQUE,
  name_ciphertext TEXT NULL,
  preferred_language ENUM('en','ar') NOT NULL DEFAULT 'en',
  onboarding_complete BOOLEAN NOT NULL DEFAULT FALSE,
  status ENUM('active','suspended','deleted') NOT NULL DEFAULT 'active',
  created_at DATETIME(3) NOT NULL,
  updated_at DATETIME(3) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE account_roles (
  account_id VARCHAR(64) NOT NULL,
  role ENUM('customer','service_adviser','administrator') NOT NULL,
  granted_at DATETIME(3) NOT NULL,
  revoked_at DATETIME(3) NULL,
  PRIMARY KEY (account_id, role),
  CONSTRAINT fk_roles_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;

CREATE TABLE devices (
  device_ref VARCHAR(64) PRIMARY KEY,
  account_id VARCHAR(64) NOT NULL,
  device_id_hash CHAR(64) NOT NULL,
  platform ENUM('ios','android') NOT NULL,
  app_version VARCHAR(64) NOT NULL,
  os_version VARCHAR(64) NOT NULL,
  attestation_state ENUM('unknown','valid','invalid') NOT NULL DEFAULT 'unknown',
  created_at DATETIME(3) NOT NULL,
  revoked_at DATETIME(3) NULL,
  INDEX ix_devices_account (account_id),
  CONSTRAINT fk_devices_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;

CREATE TABLE sessions (
  session_id VARCHAR(64) PRIMARY KEY,
  account_id VARCHAR(64) NOT NULL,
  device_ref VARCHAR(64) NOT NULL,
  refresh_token_hash CHAR(64) NOT NULL UNIQUE,
  rotation_family VARCHAR(64) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  revoked_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX ix_sessions_account (account_id),
  CONSTRAINT fk_sessions_account FOREIGN KEY (account_id) REFERENCES accounts(account_id),
  CONSTRAINT fk_sessions_device FOREIGN KEY (device_ref) REFERENCES devices(device_ref)
) ENGINE=InnoDB;

CREATE TABLE consent_events (
  consent_id VARCHAR(64) PRIMARY KEY,
  account_id VARCHAR(64) NOT NULL,
  purpose VARCHAR(64) NOT NULL,
  policy_version VARCHAR(64) NOT NULL,
  granted BOOLEAN NOT NULL,
  recorded_at DATETIME(3) NOT NULL,
  INDEX ix_consent_latest (account_id, purpose, recorded_at),
  CONSTRAINT fk_consent_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;

CREATE TABLE vehicles (
  vehicle_id VARCHAR(64) PRIMARY KEY,
  account_id VARCHAR(64) NOT NULL,
  plate_ciphertext TEXT NOT NULL,
  plate_lookup_hmac CHAR(64) NOT NULL,
  vin_ciphertext TEXT NULL,
  vin_lookup_hmac CHAR(64) NULL,
  model VARCHAR(120) NOT NULL,
  model_year SMALLINT NOT NULL,
  powertrain VARCHAR(32) NOT NULL,
  odometer_value DECIMAL(12,1) NOT NULL,
  odometer_unit ENUM('km','mi') NOT NULL,
  coverage_tier ENUM('unverified','validated','expected','limited','unsupported') NOT NULL DEFAULT 'unverified',
  created_at DATETIME(3) NOT NULL,
  updated_at DATETIME(3) NOT NULL,
  deleted_at DATETIME(3) NULL,
  INDEX ix_vehicles_account (account_id, deleted_at),
  INDEX ix_vehicles_vin (vin_lookup_hmac),
  CONSTRAINT fk_vehicles_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;

CREATE TABLE scans (
  scan_id VARCHAR(128) PRIMARY KEY,
  account_id VARCHAR(64) NOT NULL,
  vehicle_id VARCHAR(64) NOT NULL,
  status ENUM('completed','failed','inconclusive') NOT NULL,
  urgency VARCHAR(32) NOT NULL,
  schema_version VARCHAR(32) NOT NULL,
  rules_version VARCHAR(64) NOT NULL,
  evidence_hash VARCHAR(128) NOT NULL,
  document_json JSON NOT NULL,
  explanation_state ENUM('pending','ready','failed','unavailable') NOT NULL,
  received_at DATETIME(3) NOT NULL,
  deleted_at DATETIME(3) NULL,
  INDEX ix_scans_customer_history (account_id, received_at, deleted_at),
  INDEX ix_scans_vehicle_history (vehicle_id, received_at, deleted_at),
  INDEX ix_scans_staff_queue (urgency, received_at, deleted_at),
  CONSTRAINT fk_scans_account FOREIGN KEY (account_id) REFERENCES accounts(account_id),
  CONSTRAINT fk_scans_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id)
) ENGINE=InnoDB;

CREATE TABLE attachments (
  attachment_id VARCHAR(128) PRIMARY KEY,
  scan_id VARCHAR(128) NOT NULL,
  account_id VARCHAR(64) NOT NULL,
  private_storage_key VARCHAR(512) NOT NULL,
  content_hash VARCHAR(128) NOT NULL,
  mime_type VARCHAR(64) NOT NULL,
  byte_size INT UNSIGNED NOT NULL,
  consent_version VARCHAR(64) NOT NULL,
  created_at DATETIME(3) NOT NULL,
  deletion_due_at DATETIME(3) NOT NULL,
  deleted_at DATETIME(3) NULL,
  INDEX ix_attachments_scan (scan_id),
  CONSTRAINT fk_attachments_scan FOREIGN KEY (scan_id) REFERENCES scans(scan_id),
  CONSTRAINT fk_attachments_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;

CREATE TABLE service_requests (
  request_id VARCHAR(64) PRIMARY KEY,
  scan_id VARCHAR(128) NOT NULL,
  account_id VARCHAR(64) NOT NULL,
  request_type ENUM('callback','appointment') NOT NULL,
  preferred_contact ENUM('phone','whatsapp') NOT NULL,
  preferred_time_note VARCHAR(500) NULL,
  status ENUM('open','contacted','scheduled','closed','cancelled') NOT NULL DEFAULT 'open',
  created_at DATETIME(3) NOT NULL,
  updated_at DATETIME(3) NOT NULL,
  INDEX ix_service_open (status, created_at),
  CONSTRAINT fk_service_scan FOREIGN KEY (scan_id) REFERENCES scans(scan_id),
  CONSTRAINT fk_service_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;

CREATE TABLE scan_share_events (
  event_id VARCHAR(64) PRIMARY KEY,
  scan_id VARCHAR(128) NOT NULL,
  account_id VARCHAR(64) NOT NULL,
  shared BOOLEAN NOT NULL,
  reason VARCHAR(64) NOT NULL,
  consent_version VARCHAR(64) NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX ix_share_latest (scan_id, created_at),
  CONSTRAINT fk_share_scan FOREIGN KEY (scan_id) REFERENCES scans(scan_id)
) ENGINE=InnoDB;

CREATE TABLE staff_reviews (
  review_id VARCHAR(64) PRIMARY KEY,
  scan_id VARCHAR(128) NOT NULL,
  staff_account_id VARCHAR(64) NOT NULL,
  outcome VARCHAR(64) NOT NULL,
  internal_note TEXT NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX ix_reviews_scan (scan_id, created_at),
  CONSTRAINT fk_reviews_scan FOREIGN KEY (scan_id) REFERENCES scans(scan_id),
  CONSTRAINT fk_reviews_staff FOREIGN KEY (staff_account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;

CREATE TABLE audit_events (
  audit_id VARCHAR(64) PRIMARY KEY,
  actor_account_id VARCHAR(64) NOT NULL,
  action VARCHAR(64) NOT NULL,
  target_type VARCHAR(64) NOT NULL,
  target_id VARCHAR(128) NOT NULL,
  purpose VARCHAR(64) NULL,
  request_id VARCHAR(64) NOT NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX ix_audit_target (target_type, target_id, created_at),
  INDEX ix_audit_actor (actor_account_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE jobs (
  job_id VARCHAR(64) PRIMARY KEY,
  job_type VARCHAR(64) NOT NULL,
  target_id VARCHAR(128) NOT NULL,
  state ENUM('pending','running','complete','dead') NOT NULL DEFAULT 'pending',
  attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  next_run_at DATETIME(3) NOT NULL,
  locked_at DATETIME(3) NULL,
  last_error VARCHAR(500) NULL,
  created_at DATETIME(3) NOT NULL,
  completed_at DATETIME(3) NULL,
  INDEX ix_jobs_claim (state, next_run_at)
) ENGINE=InnoDB;

CREATE TABLE idempotency_keys (
  account_id VARCHAR(64) NOT NULL,
  route VARCHAR(128) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  request_hash CHAR(64) NOT NULL,
  resource_id VARCHAR(128) NOT NULL,
  created_at DATETIME(3) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  PRIMARY KEY (account_id, route, key_hash)
) ENGINE=InnoDB;

CREATE TABLE privacy_requests (
  request_id VARCHAR(64) PRIMARY KEY,
  account_id VARCHAR(64) NOT NULL,
  request_type VARCHAR(64) NOT NULL,
  target_id VARCHAR(128) NULL,
  status ENUM('received','verifying','processing','completed','declined') NOT NULL,
  details TEXT NULL,
  export_storage_key VARCHAR(512) NULL,
  export_expires_at DATETIME(3) NULL,
  export_deleted_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL,
  completed_at DATETIME(3) NULL,
  INDEX ix_privacy_account (account_id, created_at),
  CONSTRAINT fk_privacy_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB;