-- FashCognitive TNA Module - Database Migration
-- Creates all TNA-specific tables referencing fcqc's existing master data tables

SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------------
-- TNA Projects
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_projects`;
CREATE TABLE `tna_projects` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `target_date` date DEFAULT NULL,
  `po_id` int unsigned DEFAULT NULL,
  `template_id` int unsigned DEFAULT NULL,
  `status` enum('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`),
  KEY `company_id` (`company_id`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Tasks
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_tasks`;
CREATE TABLE `tna_tasks` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `phase` varchar(100) DEFAULT '',
  `responsible_party` varchar(255) DEFAULT '',
  `status` varchar(50) NOT NULL DEFAULT 'pending',
  `color` varchar(20) NOT NULL DEFAULT 'green',
  `start_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `completed_date` date DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `delay_days` int NOT NULL DEFAULT '0',
  `completion_percentage` int NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  `is_key_event` tinyint(1) NOT NULL DEFAULT '0',
  `is_milestone` tinyint(1) NOT NULL DEFAULT '0',
  `is_auto_scheduled` tinyint(1) NOT NULL DEFAULT '0',
  `depends_on_task_id` int unsigned DEFAULT NULL,
  `factory` varchar(255) DEFAULT '',
  `vendor` varchar(255) DEFAULT '',
  `merchandiser` varchar(255) DEFAULT '',
  `qa_responsible` varchar(255) DEFAULT '',
  `remarks` text,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tna_tasks_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Task Dependencies
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_task_dependencies`;
CREATE TABLE `tna_task_dependencies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `depends_on_task_id` int unsigned NOT NULL,
  `lag_days` int NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_dep` (`task_id`,`depends_on_task_id`),
  KEY `depends_on_task_id` (`depends_on_task_id`),
  CONSTRAINT `tna_task_deps_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tna_task_deps_ibfk_2` FOREIGN KEY (`depends_on_task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Templates
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_templates`;
CREATE TABLE `tna_templates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `duration_days` int NOT NULL DEFAULT '60',
  `task_definitions` longtext COMMENT 'JSON array of task definitions',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `buyer_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `buyer_id` (`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Comments
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_comments`;
CREATE TABLE `tna_comments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `parent_comment_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tna_comments_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Attachments
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_attachments`;
CREATE TABLE `tna_attachments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `comment_id` int unsigned DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` int unsigned DEFAULT '0',
  `uploaded_by` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `tna_attachments_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA QC Log
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_qc_log`;
CREATE TABLE `tna_qc_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `inspection_round` int NOT NULL DEFAULT '1',
  `result` enum('pass','fail') NOT NULL,
  `rejection_category` varchar(100) DEFAULT NULL,
  `rejection_reason` text,
  `inspected_by` varchar(255) DEFAULT NULL,
  `inspected_at` timestamp NULL DEFAULT NULL,
  `rework_deadline` date DEFAULT NULL,
  `rework_notes` text,
  `rework_completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`),
  CONSTRAINT `tna_qc_log_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Scheduling Rules
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_scheduling_rules`;
CREATE TABLE `tna_scheduling_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `trigger_event` varchar(255) NOT NULL,
  `days_offset` int NOT NULL,
  `duration_days` int NOT NULL DEFAULT '1',
  `is_key_event` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Activity Log
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_activity_log`;
CREATE TABLE `tna_activity_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned DEFAULT NULL,
  `task_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `action` varchar(100) NOT NULL DEFAULT '',
  `description` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `task_id` (`task_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Project Team
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_project_team`;
CREATE TABLE `tna_project_team` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `role` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_user` (`project_id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Team Members (CONSOLIDATED into users table)
-- -----------------------------------------------------------
-- This table was dropped and its columns merged into the users table.
-- See migrate_team_to_users.php for the migration.
-- Columns added to users: department_id, designation, manager_id, factory_id, portal_enabled

-- -----------------------------------------------------------
-- TNA Partners (External Portal)
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_partners`;
CREATE TABLE `tna_partners` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) NOT NULL,
  `contact_name` varchar(255) DEFAULT '',
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Partner Projects
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_partner_projects`;
CREATE TABLE `tna_partner_projects` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `partner_id` int unsigned NOT NULL,
  `project_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `partner_project` (`partner_id`,`project_id`),
  KEY `project_id` (`project_id`),
  CONSTRAINT `tna_partner_projects_ibfk_1` FOREIGN KEY (`partner_id`) REFERENCES `tna_partners` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tna_partner_projects_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Notifications
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_notifications`;
CREATE TABLE `tna_notifications` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `message` text,
  `type` varchar(50) NOT NULL DEFAULT 'general',
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `related_task_id` int unsigned DEFAULT NULL,
  `related_project_id` int unsigned DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  `is_archived` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `is_read` (`is_read`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Budgets
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_budgets`;
CREATE TABLE `tna_budgets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `planned_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `actual_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `currency` varchar(10) DEFAULT 'USD',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Expenses
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_expenses`;
CREATE TABLE `tna_expenses` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `description` varchar(500) NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `category` varchar(100) DEFAULT '',
  `vendor` varchar(255) DEFAULT '',
  `receipt_path` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Invoices
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_invoices`;
CREATE TABLE `tna_invoices` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `invoice_number` varchar(100) NOT NULL,
  `vendor` varchar(255) DEFAULT '',
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','sent','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `due_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Payments
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_payments`;
CREATE TABLE `tna_payments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `payment_method` varchar(100) DEFAULT '',
  `reference` varchar(255) DEFAULT '',
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Integrations
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_integrations`;
CREATE TABLE `tna_integrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('netsuite','apparelmagic','generic_rest') NOT NULL,
  `api_endpoint` varchar(500) DEFAULT '',
  `api_key` varchar(500) DEFAULT '',
  `api_secret` varchar(500) DEFAULT '',
  `additional_config` text,
  `status` enum('active','inactive','error') NOT NULL DEFAULT 'inactive',
  `sync_interval_minutes` int NOT NULL DEFAULT '60',
  `last_sync_at` timestamp NULL DEFAULT NULL,
  `last_sync_status` varchar(50) DEFAULT NULL,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Integration Logs
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_integration_logs`;
CREATE TABLE `tna_integration_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `integration_id` int unsigned NOT NULL,
  `direction` enum('import','export') NOT NULL DEFAULT 'import',
  `entity_type` varchar(100) DEFAULT NULL,
  `status` enum('running','success','failed','partial') NOT NULL DEFAULT 'running',
  `records_total` int NOT NULL DEFAULT '0',
  `records_succeeded` int NOT NULL DEFAULT '0',
  `records_failed` int NOT NULL DEFAULT '0',
  `error_message` text,
  `raw_request` longtext,
  `raw_response` longtext,
  `started_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `integration_id` (`integration_id`),
  CONSTRAINT `tna_integration_logs_ibfk_1` FOREIGN KEY (`integration_id`) REFERENCES `tna_integrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Analytics Daily Snapshot
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `analytics_daily_snapshot`;
CREATE TABLE `analytics_daily_snapshot` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned DEFAULT NULL,
  `factory` varchar(255) DEFAULT '',
  `total_tasks` int NOT NULL DEFAULT '0',
  `completed_tasks` int NOT NULL DEFAULT '0',
  `delayed_tasks` int NOT NULL DEFAULT '0',
  `pending_tasks` int NOT NULL DEFAULT '0',
  `avg_delay_days` decimal(8,2) NOT NULL DEFAULT '0.00',
  `completion_percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `risk_score` int NOT NULL DEFAULT '0',
  `efficiency` decimal(5,2) DEFAULT NULL,
  `snapshot_date` date NOT NULL,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_snapshot` (`project_id`,`snapshot_date`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- AI Predictions
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `ai_predictions`;
CREATE TABLE `ai_predictions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned DEFAULT NULL,
  `prediction_type` varchar(100) NOT NULL,
  `risk_level` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `confidence` decimal(5,2) NOT NULL DEFAULT '0.00',
  `predicted_delay_days` int DEFAULT NULL,
  `recommendation` text,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Alert Rules
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `tna_alert_rules`;
CREATE TABLE `tna_alert_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `entity_type` enum('task','project','po','qc') NOT NULL DEFAULT 'task',
  `condition_field` varchar(100) NOT NULL,
  `condition_operator` enum('eq','neq','gt','gte','lt','lte','in','contains','changed') NOT NULL DEFAULT 'eq',
  `condition_value` varchar(255) NOT NULL,
  `notification_title` varchar(255) NOT NULL,
  `notification_message` text,
  `notification_type` varchar(50) NOT NULL DEFAULT 'general',
  `notification_priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `recipient_type` enum('role','department','user','project_team') DEFAULT NULL,
  `recipient_id` int unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `last_evaluated_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `idx_entity_type` (`entity_type`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Workflow Definitions (React Flow)
-- -----------------------------------------------------------
DROP TABLE IF EXISTS `workflow_definitions`;
CREATE TABLE `workflow_definitions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `nodes` longtext COMMENT 'JSON array of React Flow nodes',
  `edges` longtext COMMENT 'JSON array of React Flow edges',
  `status` enum('draft','published') NOT NULL DEFAULT 'draft',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
