-- =============================================================================
-- 08-notice-members.sql
-- Migrations: NoticeMembers1780960000000, NoticeMembersForeignKeys1780970000000
-- Requires: 00b-cpanel-helpers.sql
-- cPanel safe. Safe to run multiple times.
-- =============================================================================

CREATE TABLE IF NOT EXISTS `_noticemembers` (
  `B` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `A` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  INDEX `IDX_notice_members_B` (`B`),
  INDEX `IDX_notice_members_A` (`A`),
  PRIMARY KEY (`B`, `A`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CALL cp_safe_exec('ALTER TABLE `_noticemembers` ADD CONSTRAINT `FK_notice_members_notice` FOREIGN KEY (`B`) REFERENCES `notices`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');

CALL cp_safe_exec('ALTER TABLE `_noticemembers` ADD CONSTRAINT `FK_notice_members_member` FOREIGN KEY (`A`) REFERENCES `members`(`id`) ON DELETE NO ACTION ON UPDATE NO ACTION');

INSERT INTO migrations (timestamp, name)
SELECT 1780960000000, 'NoticeMembers1780960000000'
WHERE NOT EXISTS (
  SELECT 1 FROM migrations WHERE name = 'NoticeMembers1780960000000'
);

INSERT INTO migrations (timestamp, name)
SELECT 1780970000000, 'NoticeMembersForeignKeys1780970000000'
WHERE NOT EXISTS (
  SELECT 1 FROM migrations WHERE name = 'NoticeMembersForeignKeys1780970000000'
);

SELECT COUNT(*) AS notice_members_rows FROM `_noticemembers`;
