-- Keeps company profile fields in sync between:
-- - company_overview (used by setup wizard + company pages)
-- - system_options (client_* keys used by various screens)
--
-- Designed for MariaDB 10.6+/MySQL 5.7+.
--
-- Mapping:
-- company_overview.company_name            <-> system_options.name='client_name'
-- company_overview.company_address_1       <-> system_options.name='client_address_1'
-- company_overview.company_address_2       <-> system_options.name='client_address_2'
-- company_overview.company_address_county  <-> system_options.name='client_address_3'
-- company_overview.company_address_postcode<-> system_options.name='client_address_postcode'
-- company_overview.company_address_country <-> system_options.name='client_address_country'

DELIMITER $$

DROP TRIGGER IF EXISTS trg_company_overview_to_system_options_ai$$
DROP TRIGGER IF EXISTS trg_company_overview_to_system_options_au$$
DROP TRIGGER IF EXISTS trg_system_options_to_company_overview_ai$$
DROP TRIGGER IF EXISTS trg_system_options_to_company_overview_au$$

CREATE TRIGGER trg_company_overview_to_system_options_ai
AFTER INSERT ON company_overview
FOR EACH ROW
BEGIN
  IF COALESCE(@cs_profile_sync, 0) = 0 THEN
    SET @cs_profile_sync = 1;

    -- Only sync the canonical company row.
    IF NEW.id = 1 THEN

      INSERT INTO system_options (`name`, `value`) VALUES ('client_name', NEW.company_name)
        ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      INSERT INTO system_options (`name`, `value`) VALUES ('client_address_1', NEW.company_address_1)
        ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      INSERT INTO system_options (`name`, `value`) VALUES ('client_address_2', NEW.company_address_2)
        ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      INSERT INTO system_options (`name`, `value`) VALUES ('client_address_3', NEW.company_address_county)
        ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      INSERT INTO system_options (`name`, `value`) VALUES ('client_address_postcode', NEW.company_address_postcode)
        ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      INSERT INTO system_options (`name`, `value`) VALUES ('client_address_country', NEW.company_address_country)
        ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
    END IF;

    SET @cs_profile_sync = 0;
  END IF;
END$$

CREATE TRIGGER trg_company_overview_to_system_options_au
AFTER UPDATE ON company_overview
FOR EACH ROW
BEGIN
  IF COALESCE(@cs_profile_sync, 0) = 0 THEN
    SET @cs_profile_sync = 1;

    -- Only sync the canonical company row.
    IF NEW.id = 1 THEN

      IF (NEW.company_name <=> OLD.company_name) = 0 THEN
        INSERT INTO system_options (`name`, `value`) VALUES ('client_name', NEW.company_name)
          ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      END IF;

      IF (NEW.company_address_1 <=> OLD.company_address_1) = 0 THEN
        INSERT INTO system_options (`name`, `value`) VALUES ('client_address_1', NEW.company_address_1)
          ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      END IF;

      IF (NEW.company_address_2 <=> OLD.company_address_2) = 0 THEN
        INSERT INTO system_options (`name`, `value`) VALUES ('client_address_2', NEW.company_address_2)
          ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      END IF;

      IF (NEW.company_address_county <=> OLD.company_address_county) = 0 THEN
        INSERT INTO system_options (`name`, `value`) VALUES ('client_address_3', NEW.company_address_county)
          ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      END IF;

      IF (NEW.company_address_postcode <=> OLD.company_address_postcode) = 0 THEN
        INSERT INTO system_options (`name`, `value`) VALUES ('client_address_postcode', NEW.company_address_postcode)
          ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      END IF;

      IF (NEW.company_address_country <=> OLD.company_address_country) = 0 THEN
        INSERT INTO system_options (`name`, `value`) VALUES ('client_address_country', NEW.company_address_country)
          ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
      END IF;
    END IF;

    SET @cs_profile_sync = 0;
  END IF;
END$$

CREATE TRIGGER trg_system_options_to_company_overview_ai
AFTER INSERT ON system_options
FOR EACH ROW
BEGIN
  IF COALESCE(@cs_profile_sync, 0) = 0 THEN
    SET @cs_profile_sync = 1;

    IF NEW.name = 'client_name' THEN
      INSERT INTO company_overview (`id`, `company_name`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_name`=VALUES(`company_name`);
    ELSEIF NEW.name = 'client_address_1' THEN
      INSERT INTO company_overview (`id`, `company_address_1`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_1`=VALUES(`company_address_1`);
    ELSEIF NEW.name = 'client_address_2' THEN
      INSERT INTO company_overview (`id`, `company_address_2`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_2`=VALUES(`company_address_2`);
    ELSEIF NEW.name = 'client_address_3' THEN
      INSERT INTO company_overview (`id`, `company_address_county`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_county`=VALUES(`company_address_county`);
    ELSEIF NEW.name = 'client_address_postcode' THEN
      INSERT INTO company_overview (`id`, `company_address_postcode`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_postcode`=VALUES(`company_address_postcode`);
    ELSEIF NEW.name = 'client_address_country' THEN
      INSERT INTO company_overview (`id`, `company_address_country`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_country`=VALUES(`company_address_country`);
    END IF;

    SET @cs_profile_sync = 0;
  END IF;
END$$

CREATE TRIGGER trg_system_options_to_company_overview_au
AFTER UPDATE ON system_options
FOR EACH ROW
BEGIN
  IF COALESCE(@cs_profile_sync, 0) = 0 THEN
    SET @cs_profile_sync = 1;

    IF NEW.name = 'client_name' THEN
      INSERT INTO company_overview (`id`, `company_name`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_name`=VALUES(`company_name`);
    ELSEIF NEW.name = 'client_address_1' THEN
      INSERT INTO company_overview (`id`, `company_address_1`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_1`=VALUES(`company_address_1`);
    ELSEIF NEW.name = 'client_address_2' THEN
      INSERT INTO company_overview (`id`, `company_address_2`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_2`=VALUES(`company_address_2`);
    ELSEIF NEW.name = 'client_address_3' THEN
      INSERT INTO company_overview (`id`, `company_address_county`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_county`=VALUES(`company_address_county`);
    ELSEIF NEW.name = 'client_address_postcode' THEN
      INSERT INTO company_overview (`id`, `company_address_postcode`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_postcode`=VALUES(`company_address_postcode`);
    ELSEIF NEW.name = 'client_address_country' THEN
      INSERT INTO company_overview (`id`, `company_address_country`) VALUES (1, NEW.value)
        ON DUPLICATE KEY UPDATE `company_address_country`=VALUES(`company_address_country`);
    END IF;

    SET @cs_profile_sync = 0;
  END IF;
END$$

DELIMITER ;
