CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'admin',
  last_login_at VARCHAR(64) NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_settings (
  setting_key VARCHAR(191) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_consumers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  consumer_type VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  company VARCHAR(255) NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  allowed_originating_systems JSON NOT NULL,
  allowed_resources JSON NOT NULL,
  rate_limit_per_minute INT NOT NULL DEFAULT 60,
  rate_limit_per_day INT NOT NULL DEFAULT 5000,
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS consumer_api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  consumer_id INT NOT NULL,
  key_prefix VARCHAR(32) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  last_used_at VARCHAR(64) NULL,
  expires_at VARCHAR(64) NULL,
  created_at VARCHAR(64) NOT NULL,
  revoked_at VARCHAR(64) NULL,
  INDEX idx_key_hash (key_hash),
  CONSTRAINT fk_consumer_keys_consumer FOREIGN KEY (consumer_id) REFERENCES api_consumers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS consumer_usage_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  consumer_id INT NULL,
  api_key_id INT NULL,
  request_url TEXT NOT NULL,
  request_method VARCHAR(12) NOT NULL,
  requested_at VARCHAR(64) NOT NULL,
  ip_address VARCHAR(64) NULL,
  user_agent TEXT NULL,
  resource_name VARCHAR(64) NULL,
  originating_system_name VARCHAR(128) NULL,
  http_status INT NOT NULL,
  response_bytes INT NOT NULL DEFAULT 0,
  duration_ms INT NOT NULL DEFAULT 0,
  error_code VARCHAR(80) NULL,
  INDEX idx_usage_requested (requested_at),
  INDEX idx_usage_consumer_requested (consumer_id, requested_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_audit_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  admin_user_id INT NULL,
  action VARCHAR(191) NOT NULL,
  target_type VARCHAR(80) NULL,
  target_id VARCHAR(80) NULL,
  ip_address VARCHAR(64) NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mls_sync_state (
  resource_name VARCHAR(64) NOT NULL,
  originating_system_name VARCHAR(128) NOT NULL,
  last_modification_timestamp VARCHAR(64) NULL,
  last_success_at VARCHAR(64) NULL,
  initial_import_complete TINYINT(1) NOT NULL DEFAULT 0,
  locked_at VARCHAR(64) NULL,
  lock_owner VARCHAR(128) NULL,
  PRIMARY KEY (resource_name, originating_system_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mls_raw_records (
  resource_name VARCHAR(64) NOT NULL,
  record_key VARCHAR(191) NOT NULL,
  originating_system_name VARCHAR(128) NOT NULL,
  modification_timestamp VARCHAR(64) NULL,
  mlg_can_view TINYINT(1) NOT NULL DEFAULT 1,
  payload_json JSON NOT NULL,
  listing_id VARCHAR(191) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.ListingId'))) PERSISTENT,
  city VARCHAR(128) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.City'))) PERSISTENT,
  county_or_parish VARCHAR(128) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.CountyOrParish'))) PERSISTENT,
  standard_status VARCHAR(80) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.StandardStatus'))) PERSISTENT,
  property_type VARCHAR(80) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.PropertyType'))) PERSISTENT,
  list_price DECIMAL(15,2) GENERATED ALWAYS AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.ListPrice')) AS DECIMAL(15,2))) PERSISTENT,
  bedrooms_total DECIMAL(8,2) GENERATED ALWAYS AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.BedroomsTotal')) AS DECIMAL(8,2))) PERSISTENT,
  bathrooms_total_integer DECIMAL(8,2) GENERATED ALWAYS AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.BathroomsTotalInteger')) AS DECIMAL(8,2))) PERSISTENT,
  postal_code VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.PostalCode'))) PERSISTENT,
  is_quality_assurance TINYINT(1) GENERATED ALWAYS AS (
    CASE
      WHEN JSON_UNQUOTE(JSON_EXTRACT(payload_json, '$.PublicRemarks')) = 'Quality Assurance Testing purposes ONLY' THEN 1
      ELSE 0
    END
  ) PERSISTENT,
  deleted_at VARCHAR(64) NULL,
  updated_at VARCHAR(64) NOT NULL,
  PRIMARY KEY (resource_name, record_key),
  INDEX idx_raw_resource_origin_mod (resource_name, originating_system_name, modification_timestamp),
  INDEX idx_raw_listing_id (resource_name, originating_system_name, listing_id),
  INDEX idx_raw_grid_newest (resource_name, originating_system_name, county_or_parish, mlg_can_view, deleted_at, standard_status, is_quality_assurance, postal_code, modification_timestamp),
  INDEX idx_raw_grid_price (resource_name, originating_system_name, county_or_parish, mlg_can_view, deleted_at, standard_status, is_quality_assurance, postal_code, list_price),
  INDEX idx_raw_grid_filters (resource_name, originating_system_name, city, property_type, bedrooms_total, bathrooms_total_integer)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mls_media_files (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  media_key VARCHAR(191) NOT NULL,
  parent_resource VARCHAR(64) NOT NULL,
  parent_key VARCHAR(191) NOT NULL,
  media_url_hash CHAR(64) NOT NULL,
  source_url TEXT NOT NULL,
  local_path TEXT NOT NULL,
  public_path TEXT NOT NULL,
  content_type VARCHAR(191) NULL,
  bytes INT NOT NULL DEFAULT 0,
  downloaded_at VARCHAR(64) NOT NULL,
  deleted_at VARCHAR(64) NULL,
  UNIQUE KEY uq_media_hash (media_key, media_url_hash),
  INDEX idx_media_parent (parent_resource, parent_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
