Immigration Management System

-- View for complete case information
CREATE VIEW v_case_details AS
SELECT
  c.case_id,
  c.case_number,
  ct.case_type_name,
  c.status,
  c.filing_date,
  c.target_completion_date,
  p.person_id,
  CONCAT(p.first_name, ' ', p.last_name) AS person_name,
  p.nationality,
  cp.role,
  cp.is_primary,
  cp.relationship,
  COUNT(DISTINCT d.document_id) AS total_documents,
  COUNT(DISTINCT CASE WHEN d.status = 'APPROVED' THEN d.document_id END) AS approved_documents,
  MIN(d.expiration_date) AS next_expiration
FROM cases c
INNER JOIN case_types ct ON c.case_type_id = ct.case_type_id
LEFT JOIN case_persons cp ON c.case_id = cp.case_id
LEFT JOIN persons p ON cp.person_id = p.person_id
LEFT JOIN documents d ON c.case_id = d.case_id
GROUP BY c.case_id, p.person_id, cp.case_person_id;

-- Query to find cases needing attention (expiring documents)
SELECT
  c.case_number,
  c.status,
  p.first_name,
  p.last_name,
  dt.document_type_name,
  d.expiration_date,
  DATEDIFF(d.expiration_date, CURDATE()) AS days_until_expiration
FROM documents d
INNER JOIN cases c ON d.case_id = c.case_id
INNER JOIN case_persons cp ON c.case_id = cp.case_id AND cp.is_primary = TRUE
INNER JOIN persons p ON cp.person_id = p.person_id
INNER JOIN document_types dt ON d.document_type_id = dt.document_type_id
WHERE d.expiration_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
  AND d.status NOT IN ('EXPIRED', 'REJECTED')
  AND c.status NOT IN ('CLOSED', 'DENIED')
ORDER BY d.expiration_date ASC;

-- Procedure to create a case with primary applicant atomically
DELIMITER $$

CREATE PROCEDURE create_immigration_case(
  IN p_case_number VARCHAR(50),
  IN p_case_type_id INT,
  IN p_filing_date DATE,
  IN p_first_name VARCHAR(100),
  IN p_last_name VARCHAR(100),
  IN p_date_of_birth DATE,
  IN p_nationality VARCHAR(100),
  IN p_passport_number VARCHAR(50),
  OUT p_case_id INT,
  OUT p_person_id INT
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  -- Check if person already exists by passport
  SELECT person_id INTO p_person_id
  FROM persons
  WHERE passport_number = p_passport_number
  LIMIT 1;

  -- Create person if not exists
  IF p_person_id IS NULL THEN
    INSERT INTO persons (first_name, last_name, date_of_birth, nationality, passport_number)
    VALUES (p_first_name, p_last_name, p_date_of_birth, p_nationality, p_passport_number);
    SET p_person_id = LAST_INSERT_ID();
  END IF;

  -- Create case
  INSERT INTO cases (case_number, case_type_id, filing_date, status)
  VALUES (p_case_number, p_case_type_id, p_filing_date, 'OPEN');
  SET p_case_id = LAST_INSERT_ID();

  -- Link person to case as primary applicant
  INSERT INTO case_persons (case_id, person_id, role, is_primary)
  VALUES (p_case_id, p_person_id, 'APPLICANT', TRUE);

  -- Update denormalized counter
  UPDATE persons SET active_case_count = active_case_count + 1
  WHERE person_id = p_person_id;

  -- Create initial status history entry
  INSERT INTO case_status_history (case_id, old_status, new_status, changed_by)
  VALUES (p_case_id, NULL, 'OPEN', USER());

  COMMIT;
END$$

DELIMITER ;