-- Contracts module: customers (companies) + contracts
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / information_schema checks / INSERT ... WHERE NOT EXISTS

-- Extend companies with customer type + one-time flag
SET @db := DATABASE();

SET @exist := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'customer_type'
);
SET @sql := IF(@exist = 0,
    'ALTER TABLE `companies` ADD COLUMN `customer_type` ENUM(''product'',''service'',''project'') NULL AFTER `industry`',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exist := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'companies' AND COLUMN_NAME = 'is_one_time'
);
SET @sql := IF(@exist = 0,
    'ALTER TABLE `companies` ADD COLUMN `is_one_time` TINYINT(1) NOT NULL DEFAULT 0 AFTER `customer_type`',
    '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 = 'customer_type'
);
SET @sql := IF(@exist = 0,
    'ALTER TABLE `companies` ADD KEY `customer_type` (`customer_type`)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `contracts` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `title`                 VARCHAR(200)        NOT NULL,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `contract_type`         ENUM('product','service','project') NOT NULL,
    `status`                ENUM('draft','active','expired','terminated','cancelled') NOT NULL DEFAULT 'draft',
    `start_date`            DATE                NULL,
    `end_date`              DATE                NULL,
    `value`                 DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `total_hours`           DECIMAL(12,2)       NULL,
    `hourly_rate`           DECIMAL(15,2)       NULL,
    `location`              VARCHAR(255)        NULL,
    `project_name`          VARCHAR(200)        NULL,
    `project_description`   TEXT                NULL,
    `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 `number` (`number`),
    KEY `company_id` (`company_id`),
    KEY `contract_type` (`contract_type`),
    KEY `status` (`status`),
    KEY `start_date` (`start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contract_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `contract_id`           BIGINT UNSIGNED     NOT NULL,
    `item_code`             VARCHAR(50)         NULL,
    `item_name`             VARCHAR(200)        NOT NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 1.000,
    `unit`                  VARCHAR(30)         NOT NULL DEFAULT 'pcs',
    `unit_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `line_total`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `sort_order`            INT UNSIGNED        NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `contract_id` (`contract_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contract_milestones` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `contract_id`           BIGINT UNSIGNED     NOT NULL,
    `title`                 VARCHAR(200)        NOT NULL,
    `due_date`              DATE                NULL,
    `amount`                DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`                ENUM('pending','done') NOT NULL DEFAULT 'pending',
    `sort_order`            INT UNSIGNED        NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `contract_id` (`contract_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contract_employees` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `contract_id`           BIGINT UNSIGNED     NOT NULL,
    `employee_id`           BIGINT UNSIGNED     NOT NULL,
    `role_on_contract`      VARCHAR(100)        NULL,
    `hours_allocated`       DECIMAL(12,2)       NULL,
    `transport_leg`         ENUM('both','outbound_only','return_only') NOT NULL DEFAULT 'both',
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `contract_employee` (`contract_id`, `employee_id`),
    KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'contracts', 'contracts.delete', 'Delete Contracts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'contracts.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 'contracts.%'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
