USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  client_id BIGINT UNSIGNED NULL,
  type ENUM('new_document_uploaded','ocr_completed','extraction_needs_review','accounting_entry_needs_validation','tva_report_ready','bank_transaction_needs_matching','export_completed','ai_anomaly_detected') NOT NULL,
  title VARCHAR(180) NOT NULL,
  message TEXT NULL,
  related_type VARCHAR(80) NULL,
  related_id BIGINT UNSIGNED NULL,
  read_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY notifications_user_read_index (user_id, read_at),
  KEY notifications_client_index (client_id),
  KEY notifications_type_index (type),
  KEY notifications_created_index (created_at),
  CONSTRAINT notifications_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT notifications_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  status ENUM('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  due_date DATE NULL,
  related_type VARCHAR(80) NULL,
  related_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY tasks_related_unique (related_type, related_id),
  KEY tasks_client_index (client_id),
  KEY tasks_assigned_user_index (assigned_user_id),
  KEY tasks_status_index (status),
  KEY tasks_priority_index (priority),
  KEY tasks_due_date_index (due_date),
  CONSTRAINT tasks_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
  CONSTRAINT tasks_assigned_user_fk FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT tasks_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;