-- +goose Up
-- Remove BMD module artifacts: role-permission links, permissions, and table.

DELETE rp
FROM role_permissions rp
INNER JOIN permissions p ON p.id = rp.permission_id
WHERE p.`key` IN ('bmd.create', 'bmd.read', 'bmd.update', 'bmd.delete');

DELETE FROM permissions
WHERE `key` IN ('bmd.create', 'bmd.read', 'bmd.update', 'bmd.delete');

DROP TABLE IF EXISTS bmds;

-- +goose Down
-- Recreate BMD table and baseline permissions (without restoring historical data/links).

CREATE TABLE IF NOT EXISTS bmds (
  id BINARY(16) PRIMARY KEY,
  country_name VARCHAR(128) NOT NULL,
  note VARCHAR(255) NULL,
  sortkey INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  eu_accession_code SMALLINT NOT NULL,
  created_at DATETIME(3) NULL,
  created_by BINARY(16) NULL,
  updated_at DATETIME(3) NULL,
  updated_by BINARY(16) NULL,
  deleted_at DATETIME(3) NULL,
  deleted_by BINARY(16) NULL,
  KEY idx_bmds_sortkey (sortkey),
  KEY idx_bmds_is_active (is_active)
);

INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
SELECT UUID_TO_BIN(UUID()), 'Create BMD', 'bmd.create', 'Create BMD', 1, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.create');

INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
SELECT UUID_TO_BIN(UUID()), 'Read BMD', 'bmd.read', 'Read BMD', 0, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.read');

INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
SELECT UUID_TO_BIN(UUID()), 'Update BMD', 'bmd.update', 'Update BMD', 1, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.update');

INSERT INTO permissions (id, name, `key`, description, requires_pin, created_at, updated_at)
SELECT UUID_TO_BIN(UUID()), 'Delete BMD', 'bmd.delete', 'Delete BMD', 1, UTC_TIMESTAMP(3), UTC_TIMESTAMP(3)
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE `key` = 'bmd.delete');