-- 0016 — Owner Dropzone Master Registry
--
-- Phase B (2026-04-15).
--
-- Extends the `dropzones` table with the canonical fields from the
-- Owner Dropzone Master Registry spec:
--   • master identity (country, region, city, address, contact, maps)
--   • operational profile (size, seasonal months, altitude, facility)
--   • commercial profile (price, jump volume, gateway, notes)
--   • governance state (hidden / frozen / archived / verified / visibility)
--   • module visibility JSON (per-DZ tab toggles)
--   • import metadata (importBatchId, importSource)
--   • internal owner notes + updatedByUserId
--
-- Additive only. All columns are nullable or defaulted so existing rows
-- stay valid. Idempotent — safe to re-run against partially migrated
-- local databases.

DROP PROCEDURE IF EXISTS sky_add_col_if_missing_0016;
DELIMITER $$
CREATE PROCEDURE sky_add_col_if_missing_0016(
  IN t_name VARCHAR(64),
  IN c_name VARCHAR(64),
  IN c_def  VARCHAR(500)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = t_name
      AND column_name  = c_name
  ) THEN
    SET @sql := CONCAT('ALTER TABLE `', t_name, '` ADD COLUMN `', c_name, '` ', c_def);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

-- Master identity
CALL sky_add_col_if_missing_0016('dropzones', 'country',          'VARCHAR(80) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'region',           'VARCHAR(120) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'city',             'VARCHAR(120) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'address',          'TEXT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'contactEmail',     'VARCHAR(255) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'contactPhone',     'VARCHAR(50) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'website',          'VARCHAR(500) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'mapsLocationText', 'VARCHAR(500) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'googlePlaceId',    'VARCHAR(120) NULL');

-- Operational profile
CALL sky_add_col_if_missing_0016('dropzones', 'dzSize',               'VARCHAR(40) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'seasonalMonths',       'VARCHAR(120) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'standardAltitudeFt',   'INT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'typicalCapacityLabel', 'VARCHAR(120) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'aircraftSummary',      'TEXT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'facilitySummary',      'TEXT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'keyFeatures',          'TEXT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'numberOfInstructors',  'INT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'sourceMetricB',        'INT NULL');

-- Commercial profile
CALL sky_add_col_if_missing_0016('dropzones', 'pricePerJump',      'DECIMAL(10,2) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'totalJumpsPerYear', 'INT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'avgJumpsPerYear',   'INT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'totalJumpValue',    'DECIMAL(14,2) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'paymentGateway',    'VARCHAR(120) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'commercialNotes',   'TEXT NULL');

-- Governance state
CALL sky_add_col_if_missing_0016('dropzones', 'isHidden',           'TINYINT(1) NOT NULL DEFAULT 0');
CALL sky_add_col_if_missing_0016('dropzones', 'isFrozen',           'TINYINT(1) NOT NULL DEFAULT 0');
CALL sky_add_col_if_missing_0016('dropzones', 'isArchived',         'TINYINT(1) NOT NULL DEFAULT 0');
CALL sky_add_col_if_missing_0016('dropzones', 'isVerified',         'TINYINT(1) NOT NULL DEFAULT 0');
CALL sky_add_col_if_missing_0016('dropzones', 'visibilityState',    "VARCHAR(40) NOT NULL DEFAULT 'ACTIVE'");
CALL sky_add_col_if_missing_0016('dropzones', 'hiddenAt',           'DATETIME(3) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'frozenAt',           'DATETIME(3) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'archivedAt',         'DATETIME(3) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'deletedAt',          'DATETIME(3) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'internalOwnerNotes', 'TEXT NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'updatedByUserId',    'INT NULL');

-- Module visibility + import metadata
CALL sky_add_col_if_missing_0016('dropzones', 'moduleVisibility', 'JSON NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'importBatchId',    'VARCHAR(120) NULL');
CALL sky_add_col_if_missing_0016('dropzones', 'importSource',     'VARCHAR(120) NULL');

DROP PROCEDURE IF EXISTS sky_add_col_if_missing_0016;

-- Indexes to support owner-registry listings and filter combos.
DROP PROCEDURE IF EXISTS sky_add_idx_if_missing_0016;
DELIMITER $$
CREATE PROCEDURE sky_add_idx_if_missing_0016(
  IN t_name VARCHAR(64),
  IN i_name VARCHAR(64),
  IN i_def  VARCHAR(255)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name   = t_name
      AND index_name   = i_name
  ) THEN
    SET @sql := CONCAT('ALTER TABLE `', t_name, '` ADD INDEX `', i_name, '` ', i_def);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_country_idx',         '(`country`)');
CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_region_idx',          '(`region`)');
CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_isHidden_idx',        '(`isHidden`)');
CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_isFrozen_idx',        '(`isFrozen`)');
CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_isArchived_idx',      '(`isArchived`)');
CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_visibilityState_idx', '(`visibilityState`)');
CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_deletedAt_idx',       '(`deletedAt`)');
CALL sky_add_idx_if_missing_0016('dropzones', 'dropzones_importBatchId_idx',   '(`importBatchId`)');

DROP PROCEDURE IF EXISTS sky_add_idx_if_missing_0016;