-- Activities 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 `sales_activities` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `activity_type`         ENUM('call','email','meeting','task','follow_up') NOT NULL,
    `subject`               VARCHAR(200)        NOT NULL,
    `opportunity_id`        BIGINT UNSIGNED     NULL,
    `company_id`            BIGINT UNSIGNED     NULL,
    `contact_id`            BIGINT UNSIGNED     NULL,
    `status`                ENUM('planned','completed','cancelled') NOT NULL DEFAULT 'planned',
    `priority`              ENUM('low','normal','high') NOT NULL DEFAULT 'normal',
    `scheduled_at`          DATETIME            NULL,
    `completed_at`          DATETIME            NULL,
    `due_at`                DATETIME            NULL,
    `duration_minutes`      INT UNSIGNED        NULL,
    `description`           TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `activity_type` (`activity_type`),
    KEY `status` (`status`),
    KEY `company_id` (`company_id`),
    KEY `opportunity_id` (`opportunity_id`),
    KEY `scheduled_at` (`scheduled_at`),
    KEY `due_at` (`due_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- Grant all activity 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 'calls.%' OR p.slug LIKE 'emails.%'
       OR p.slug LIKE 'meetings.%' OR p.slug LIKE 'tasks.%'
       OR p.slug LIKE 'follow_ups.%')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
