-- Customers module for Sales & CRM workspace
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

CREATE TABLE IF NOT EXISTS `customer_groups` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(100)        NOT NULL,
    `code`                  VARCHAR(20)         NOT NULL,
    `description`           TEXT                NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `companies` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NULL,
    `email`                 VARCHAR(191)        NULL,
    `phone`                 VARCHAR(30)         NULL,
    `website`               VARCHAR(255)        NULL,
    `industry`              VARCHAR(100)        NULL,
    `customer_group_id`     BIGINT UNSIGNED     NULL,
    `address`               VARCHAR(255)        NULL,
    `city`                  VARCHAR(100)        NULL,
    `country`               VARCHAR(100)        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 `customer_group_id` (`customer_group_id`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contacts` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `first_name`            VARCHAR(100)        NOT NULL,
    `last_name`             VARCHAR(100)        NOT NULL,
    `email`                 VARCHAR(191)        NULL,
    `phone`                 VARCHAR(30)         NULL,
    `job_title`             VARCHAR(100)        NULL,
    `is_primary`            TINYINT(1)          NOT NULL DEFAULT 0,
    `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`),
    KEY `company_id` (`company_id`),
    KEY `status` (`status`),
    KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customer_history` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `contact_id`            BIGINT UNSIGNED     NULL,
    `event_type`            ENUM('note','call','email','meeting','status_change','document','other') NOT NULL DEFAULT 'note',
    `title`                 VARCHAR(200)        NOT NULL,
    `description`           TEXT                NULL,
    `event_at`              DATETIME            NOT NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `company_id` (`company_id`),
    KEY `contact_id` (`contact_id`),
    KEY `event_type` (`event_type`),
    KEY `event_at` (`event_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customer_documents` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `title`                 VARCHAR(150)        NOT NULL,
    `category`              VARCHAR(50)         NULL,
    `file_path`             VARCHAR(255)        NOT NULL,
    `original_filename`     VARCHAR(255)        NOT NULL,
    `file_size`             INT UNSIGNED        NOT NULL DEFAULT 0,
    `mime_type`             VARCHAR(100)        NULL,
    `notes`                 TEXT                NULL,
    `status`                ENUM('active','archived') NOT NULL DEFAULT 'active',
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `company_id` (`company_id`),
    KEY `status` (`status`),
    KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default customer groups
INSERT INTO `customer_groups` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'Standard', 'STD', 'Default customer segment', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `customer_groups` WHERE `code` = 'STD');

INSERT INTO `customer_groups` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'VIP', 'VIP', 'High-value or strategic accounts', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `customer_groups` WHERE `code` = 'VIP');

INSERT INTO `customer_groups` (`name`, `code`, `description`, `status`, `created_at`)
SELECT 'Partner', 'PARTNER', 'Channel and reseller partners', 'active', NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `customer_groups` WHERE `code` = 'PARTNER');

-- Permissions
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'companies', 'companies.view', 'View Companies', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'companies.view');

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- Grant all customer permissions to Administrator role
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 'companies.%' OR p.slug LIKE 'contacts.%'
       OR p.slug LIKE 'customer_groups.%' OR p.slug LIKE 'customer_history.%'
       OR p.slug LIKE 'customer_documents.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
