-- BPM System Database Schema
-- Table Prefix: bpm_

-- Users table
CREATE TABLE IF NOT EXISTS `bpm_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `timezone` varchar(50) DEFAULT 'Asia/Tehran',
  `status` enum('active','inactive','suspended') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API Keys table
CREATE TABLE IF NOT EXISTS `bpm_api_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `api_key` varchar(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_key` (`api_key`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `bpm_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rate Limits table
CREATE TABLE IF NOT EXISTS `bpm_rate_limits` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `api_key` varchar(32) NOT NULL,
  `period_type` enum('minute','hour','day','week','month') NOT NULL,
  `period_start` datetime NOT NULL,
  `request_count` int(11) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_period` (`api_key`,`period_type`,`period_start`),
  KEY `api_key` (`api_key`),
  KEY `period_start` (`period_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Roles table
CREATE TABLE IF NOT EXISTS `bpm_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User Roles table
CREATE TABLE IF NOT EXISTS `bpm_user_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_role` (`user_id`,`role_id`),
  KEY `role_id` (`role_id`),
  FOREIGN KEY (`user_id`) REFERENCES `bpm_users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `bpm_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions table (ABAC)
CREATE TABLE IF NOT EXISTS `bpm_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `resource_type` varchar(100) NOT NULL,
  `resource_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `conditions` json DEFAULT NULL,
  `fields` json DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `role_id` (`role_id`),
  KEY `user_id` (`user_id`),
  KEY `resource` (`resource_type`,`resource_id`),
  FOREIGN KEY (`role_id`) REFERENCES `bpm_roles` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `bpm_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Projects table
CREATE TABLE IF NOT EXISTS `bpm_projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `owner_id` int(11) NOT NULL,
  `metadata` json DEFAULT NULL,
  `status` enum('active','archived','deleted') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `owner_id` (`owner_id`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`),
  FOREIGN KEY (`owner_id`) REFERENCES `bpm_users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Statuses table (custom statuses per project)
CREATE TABLE IF NOT EXISTS `bpm_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `color` varchar(7) DEFAULT '#6B7280',
  `order` int(11) DEFAULT 0,
  `is_default` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_slug` (`project_id`,`slug`),
  KEY `project_id` (`project_id`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Transition Rules table
CREATE TABLE IF NOT EXISTS `bpm_transition_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `from_status_id` int(11) DEFAULT NULL,
  `to_status_id` int(11) NOT NULL,
  `required_role_id` int(11) DEFAULT NULL,
  `conditions` json DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `from_status` (`from_status_id`),
  KEY `to_status` (`to_status_id`),
  KEY `role_id` (`required_role_id`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`from_status_id`) REFERENCES `bpm_statuses` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`to_status_id`) REFERENCES `bpm_statuses` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`required_role_id`) REFERENCES `bpm_roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tasks table
CREATE TABLE IF NOT EXISTS `bpm_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `status_id` int(11) DEFAULT NULL,
  `assignee_id` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `due_date` datetime DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `deadline_duration` varchar(20) DEFAULT NULL,
  `estimated_hours` decimal(10,2) DEFAULT NULL,
  `actual_hours` decimal(10,2) DEFAULT NULL,
  `is_recurring` tinyint(1) DEFAULT 0,
  `recurrence_pattern` varchar(100) DEFAULT NULL,
  `recurrence_config` json DEFAULT NULL,
  `next_recurrence_at` datetime DEFAULT NULL,
  `order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `parent_id` (`parent_id`),
  KEY `status_id` (`status_id`),
  KEY `assignee_id` (`assignee_id`),
  KEY `created_by` (`created_by`),
  KEY `due_date` (`due_date`),
  KEY `created_at` (`created_at`),
  KEY `is_recurring` (`is_recurring`),
  KEY `next_recurrence_at` (`next_recurrence_at`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`parent_id`) REFERENCES `bpm_tasks` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`status_id`) REFERENCES `bpm_statuses` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assignee_id`) REFERENCES `bpm_users` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `bpm_users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Task Dependencies table
CREATE TABLE IF NOT EXISTS `bpm_task_dependencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `depends_on_task_id` int(11) NOT NULL,
  `dependency_type` enum('finish_to_start','start_to_start','finish_to_finish','start_to_finish') DEFAULT 'finish_to_start',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_dependency` (`task_id`,`depends_on_task_id`),
  KEY `depends_on_task_id` (`depends_on_task_id`),
  FOREIGN KEY (`task_id`) REFERENCES `bpm_tasks` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`depends_on_task_id`) REFERENCES `bpm_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Custom Fields definition table
CREATE TABLE IF NOT EXISTS `bpm_custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `entity_type` enum('user','project','task','status','role','project_role','global') DEFAULT 'project',
  `entity_id` int(11) DEFAULT NULL,
  `field_key` varchar(100) NOT NULL,
  `field_type` enum('text','number','date','boolean','select','textarea') NOT NULL,
  `label` varchar(255) NOT NULL,
  `options` json DEFAULT NULL,
  `required` tinyint(1) DEFAULT 0,
  `is_global` tinyint(1) DEFAULT 0,
  `global_required` tinyint(1) DEFAULT 0,
  `default_value` text,
  `order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_field` (`project_id`,`field_key`),
  KEY `project_id` (`project_id`),
  KEY `entity` (`entity_type`,`entity_id`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Task Custom Data table (JSON storage)
CREATE TABLE IF NOT EXISTS `bpm_task_custom_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `custom_data` json NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_id` (`task_id`),
  FOREIGN KEY (`task_id`) REFERENCES `bpm_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Formulas table
CREATE TABLE IF NOT EXISTS `bpm_formulas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `field_key` varchar(100) NOT NULL,
  `label` varchar(255) NOT NULL,
  `formula_type` enum('math','lookup') NOT NULL,
  `formula_expression` text NOT NULL,
  `target_table` varchar(100) DEFAULT NULL,
  `target_field` varchar(100) DEFAULT NULL,
  `conditions` json DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_field` (`project_id`,`field_key`),
  KEY `project_id` (`project_id`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Webhooks table
CREATE TABLE IF NOT EXISTS `bpm_webhooks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `url` text NOT NULL,
  `events` json NOT NULL,
  `secret` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `status` (`status`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Webhook Logs table
CREATE TABLE IF NOT EXISTS `bpm_webhook_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `webhook_id` int(11) NOT NULL,
  `event` varchar(100) NOT NULL,
  `payload` json NOT NULL,
  `response_code` int(11) DEFAULT NULL,
  `response_body` text,
  `status` enum('pending','success','failed') DEFAULT 'pending',
  `attempts` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `processed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `webhook_id` (`webhook_id`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`),
  FOREIGN KEY (`webhook_id`) REFERENCES `bpm_webhooks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Queue table
CREATE TABLE IF NOT EXISTS `bpm_queue` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `job_type` varchar(100) NOT NULL,
  `payload` json NOT NULL,
  `status` enum('pending','processing','completed','failed') DEFAULT 'pending',
  `priority` int(11) DEFAULT 0,
  `attempts` int(11) DEFAULT 0,
  `max_attempts` int(11) DEFAULT 3,
  `error_message` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `processed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `status` (`status`,`priority`,`created_at`),
  KEY `job_type` (`job_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recurring Tasks table
CREATE TABLE IF NOT EXISTS `bpm_recurring_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `template_task_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `recurrence_pattern` varchar(100) NOT NULL,
  `recurrence_config` json DEFAULT NULL,
  `next_run_at` datetime NOT NULL,
  `assignee_id` int(11) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `next_run_at` (`next_run_at`),
  KEY `status` (`status`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`template_task_id`) REFERENCES `bpm_tasks` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assignee_id`) REFERENCES `bpm_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Logs table
CREATE TABLE IF NOT EXISTS `bpm_audit_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `api_key` varchar(32) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `resource_type` varchar(100) NOT NULL,
  `resource_id` int(11) DEFAULT NULL,
  `old_value` json DEFAULT NULL,
  `new_value` json DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `resource` (`resource_type`,`resource_id`),
  KEY `created_at` (`created_at`),
  KEY `action` (`action`),
  FOREIGN KEY (`user_id`) REFERENCES `bpm_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings table
CREATE TABLE IF NOT EXISTS `bpm_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` varchar(100) NOT NULL,
  `value` text,
  `type` varchar(50) DEFAULT 'string',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Project Roles table
CREATE TABLE IF NOT EXISTS `bpm_project_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role_name` varchar(100) NOT NULL,
  `can_view_all_tasks` tinyint(1) DEFAULT 0,
  `can_edit_all_tasks` tinyint(1) DEFAULT 0,
  `can_create_tasks` tinyint(1) DEFAULT 0,
  `can_delete_tasks` tinyint(1) DEFAULT 0,
  `can_manage_project` tinyint(1) DEFAULT 0,
  `can_manage_users` tinyint(1) DEFAULT 0,
  `can_view_reports` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_user` (`project_id`,`user_id`),
  KEY `project_id` (`project_id`),
  KEY `user_id` (`user_id`),
  FOREIGN KEY (`project_id`) REFERENCES `bpm_projects` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `bpm_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default roles
INSERT INTO `bpm_roles` (`name`, `slug`, `description`) VALUES
('Administrator', 'admin', 'Full system access'),
('Project Manager', 'project_manager', 'Can manage projects and tasks'),
('Team Member', 'team_member', 'Can view and update assigned tasks'),
('Viewer', 'viewer', 'Read-only access');

