-- Release 064: provider-neutral master-data synchronisation framework.
-- No provider endpoint, credential, or external schema is assumed by this migration.

CREATE TABLE IF NOT EXISTS master_data_providers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_key VARCHAR(80) NOT NULL,
  display_name VARCHAR(160) NOT NULL,
  provider_class VARCHAR(120) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  auth_mode ENUM('none','api_key','oauth2') NOT NULL DEFAULT 'none',
  endpoint_url VARCHAR(1000) NULL,
  credential_cipher TEXT NULL,
  mapping_json JSON NULL,
  schedule_days SMALLINT UNSIGNED NOT NULL DEFAULT 30,
  last_attempt_at DATETIME NULL,
  last_success_at DATETIME NULL,
  last_error_at DATETIME NULL,
  last_error_code VARCHAR(80) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_master_data_provider_key(provider_key),
  KEY idx_master_data_provider_due(enabled,last_success_at),
  CONSTRAINT fk_master_data_provider_created_by FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_master_data_provider_updated_by FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS master_data_sync_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_id BIGINT UNSIGNED NOT NULL,
  requested_by BIGINT UNSIGNED NULL,
  run_mode ENUM('preview','scheduled') NOT NULL,
  status ENUM('running','preview_ready','applied','failed','cancelled') NOT NULL DEFAULT 'running',
  source_fingerprint CHAR(64) NULL,
  summary_json JSON NULL,
  error_code VARCHAR(80) NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL,
  KEY idx_master_data_runs(provider_id,started_at),
  KEY idx_master_data_runs_status(status,started_at),
  CONSTRAINT fk_master_data_runs_provider FOREIGN KEY(provider_id) REFERENCES master_data_providers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_master_data_runs_requested_by FOREIGN KEY(requested_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS master_data_sync_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  run_id BIGINT UNSIGNED NOT NULL,
  record_type ENUM('order','province','unit','reference') NOT NULL,
  external_key VARCHAR(255) NOT NULL,
  action_kind ENUM('create','update','deactivate','unchanged','ambiguous','invalid') NOT NULL,
  local_province_id BIGINT UNSIGNED NULL,
  local_unit_id BIGINT UNSIGNED NULL,
  before_json JSON NULL,
  after_json JSON NULL,
  reason_code VARCHAR(80) NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  applied_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_master_data_run_record(run_id,record_type,external_key),
  KEY idx_master_data_items_review(run_id,action_kind,id),
  CONSTRAINT fk_master_data_items_run FOREIGN KEY(run_id) REFERENCES master_data_sync_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_master_data_items_province FOREIGN KEY(local_province_id) REFERENCES provinces(id) ON DELETE SET NULL,
  CONSTRAINT fk_master_data_items_unit FOREIGN KEY(local_unit_id) REFERENCES units(id) ON DELETE SET NULL,
  CONSTRAINT fk_master_data_items_approved_by FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS master_data_external_identities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider_id BIGINT UNSIGNED NOT NULL,
  record_type ENUM('order','province','unit','reference') NOT NULL,
  external_key VARCHAR(255) NOT NULL,
  province_id BIGINT UNSIGNED NULL,
  unit_id BIGINT UNSIGNED NULL,
  order_key VARCHAR(60) NULL,
  last_source_json JSON NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_master_data_external_identity(provider_id,record_type,external_key),
  KEY idx_master_data_identity_province(province_id),
  KEY idx_master_data_identity_unit(unit_id),
  CONSTRAINT fk_master_data_identity_provider FOREIGN KEY(provider_id) REFERENCES master_data_providers(id) ON DELETE CASCADE,
  CONSTRAINT fk_master_data_identity_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE SET NULL,
  CONSTRAINT fk_master_data_identity_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO master_data_providers(provider_key,display_name,provider_class,enabled,auth_mode,mapping_json,schedule_days)
VALUES('mock_reference','Mock reference provider','MockLodgeDataProvider',0,'none',JSON_OBJECT(),30)
ON DUPLICATE KEY UPDATE display_name=VALUES(display_name),provider_class=VALUES(provider_class);