-- Contracts sites / groups (e.g. malls with multiple customers)
-- Run after contracts.sql
-- Safe to re-run: uses IF NOT EXISTS / information_schema checks / INSERT ... WHERE NOT EXISTS

SET @db := DATABASE();

CREATE TABLE IF NOT EXISTS `customer_sites` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NOT NULL,
    `site_type`             VARCHAR(50)         NULL,
    `address`               VARCHAR(255)        NULL,
    `city`                  VARCHAR(100)        NULL,
    `country`               VARCHAR(100)        NULL,
    `description`           TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @exist := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'site_id'
);
SET @sql := IF(@exist = 0,
    'ALTER TABLE `companies` ADD COLUMN `site_id` BIGINT UNSIGNED NULL AFTER `customer_group_id`',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exist := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'companies' AND INDEX_NAME = 'site_id'
);
SET @sql := IF(@exist = 0,
    'ALTER TABLE `companies` ADD KEY `site_id` (`site_id`)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_sites', 'customer_sites.view', 'View Customer Sites', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_sites.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_sites', 'customer_sites.create', 'Create Customer Sites', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_sites.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_sites', 'customer_sites.edit', 'Edit Customer Sites', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_sites.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_sites', 'customer_sites.delete', 'Delete Customer Sites', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_sites.delete');

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND p.slug LIKE 'customer_sites.%'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
