-- Legal Case Management — content seed (PII-free). Statements separated by `-- @@`.
-- Domain reference + demo data and the reporting view only; no auth/users/sessions.
-- @@
CREATE TABLE IF NOT EXISTS `courts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `level` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_court` (`level`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (1, 'सर्वोच्च अदालत', 'सर्वोच्च अदालत', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (2, 'उच्च अदालत', 'उच्च अदालत पाटन', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (3, 'उच्च अदालत', 'उच्च अदालत विराटनगर', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (4, 'उच्च अदालत', 'उच्च अदालत तुलसीपुर', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (5, 'उच्च अदालत', 'उच्च अदालत पोखरा', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (6, 'उच्च अदालत', 'उच्च अदालत जनकपुर', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (7, 'जिल्ला अदालत', 'जिल्ला अदालत काठमाडौं', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (8, 'जिल्ला अदालत', 'जिल्ला अदालत मोरङ', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (9, 'जिल्ला अदालत', 'जिल्ला अदालत चितवन', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (10, 'जिल्ला अदालत', 'जिल्ला अदालत रुपन्देही', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (11, 'जिल्ला अदालत', 'जिल्ला अदालत बाँके', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (12, 'जिल्ला अदालत', 'जिल्ला अदालत पर्सा', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (13, 'जिल्ला अदालत', 'जिल्ला अदालत कपिलवस्तु', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (14, 'जिल्ला अदालत', 'जिल्ला अदालत मकवानपुर', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (15, 'जिल्ला अदालत', 'जिल्ला अदालत नवलपरासी', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (16, 'जिल्ला अदालत', 'जिल्ला अदालत पर्वत', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (17, 'ऋण असुली न्यायाधिकरण', 'ऋण असुली न्यायाधिकरण', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `courts` (`id`, `level`, `name`, `is_active`, `created_at`) VALUES (18, 'राजस्व न्यायाधिकरण', 'राजस्व न्यायाधिकरण', 1, '2026-08-15 21:32:34')
-- @@
CREATE TABLE IF NOT EXISTS `case_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (1, 'जालसाजी', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (2, 'लिखत बदर', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (3, 'धितो बदर', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (4, 'निषेधाज्ञा', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (5, 'उत्प्रेषण / परमादेश (रिट)', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (6, 'ऋण असुली', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (7, 'दाखिल खारेज', 1, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_types` (`id`, `name`, `is_active`, `created_at`) VALUES (8, 'मिलापत्र', 1, '2026-08-15 21:32:34')
-- @@
CREATE TABLE IF NOT EXISTS `cases` (
  `id` int NOT NULL AUTO_INCREMENT,
  `plaintiff` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `defendant` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `case_type_id` int DEFAULT NULL,
  `case_number` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `court_id` int DEFAULT NULL,
  `branch` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `province` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `waresh_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `waresh_contact` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `waresh_email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_date_bs` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_date_ad` date DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'running',
  `current_status_detail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_hearing_date_bs` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_hearing_date_ad` date DEFAULT NULL,
  `hearing_type` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `claim_amount` decimal(18,2) DEFAULT NULL,
  `amount_type` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `decision_date_bs` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `decision_date_ad` date DEFAULT NULL,
  `decision_result` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remarks` text COLLATE utf8mb4_unicode_ci,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `deleted_by` int 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 `idx_next_ad` (`next_hearing_date_ad`),
  KEY `idx_status` (`status`),
  KEY `idx_court` (`court_id`),
  KEY `idx_type` (`case_type_id`),
  KEY `idx_province` (`province`),
  KEY `idx_deleted` (`deleted_at`),
  CONSTRAINT `fk_case_court` FOREIGN KEY (`court_id`) REFERENCES `courts` (`id`),
  CONSTRAINT `fk_case_type` FOREIGN KEY (`case_type_id`) REFERENCES `case_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- @@
INSERT INTO `cases` (`id`, `plaintiff`, `defendant`, `case_type_id`, `case_number`, `court_id`, `branch`, `province`, `waresh_name`, `waresh_contact`, `waresh_email`, `registration_date_bs`, `registration_date_ad`, `status`, `current_status_detail`, `next_hearing_date_bs`, `next_hearing_date_ad`, `hearing_type`, `claim_amount`, `amount_type`, `decision_date_bs`, `decision_date_ad`, `decision_result`, `remarks`, `created_by`, `updated_by`, `deleted_at`, `deleted_by`, `created_at`, `updated_at`) VALUES (1, 'गरिमा विकास बैंक लिमिटेड', 'राम बहादुर श्रेष्ठ', 6, '०८१-CV-०१२३', 17, 'नयाँ बानेश्वर', 'बागमती', 'सुरेश न्यौपाने', '9841000001', 'waresh1@example.com', '2081-03-15', '2024-06-29', 'running', 'ऋण असुली दावी दर्ता', '2083-05-01', '2026-08-16', 'पेशी', '5200000.00', 'दावी', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-08-15 21:32:34', '2026-08-16 14:44:56')
-- @@
INSERT INTO `cases` (`id`, `plaintiff`, `defendant`, `case_type_id`, `case_number`, `court_id`, `branch`, `province`, `waresh_name`, `waresh_contact`, `waresh_email`, `registration_date_bs`, `registration_date_ad`, `status`, `current_status_detail`, `next_hearing_date_bs`, `next_hearing_date_ad`, `hearing_type`, `claim_amount`, `amount_type`, `decision_date_bs`, `decision_date_ad`, `decision_result`, `remarks`, `created_by`, `updated_by`, `deleted_at`, `deleted_by`, `created_at`, `updated_at`) VALUES (2, 'सीता देवी पौडेल', 'गरिमा विकास बैंक लिमिटेड', 2, '०८१-CV-०४५६', 7, 'पुतलीसडक', 'बागमती', 'रमेश अधिकारी', '9851000002', 'waresh2@example.com', '2081-05-10', '2024-08-25', 'running', 'लिखत बदर विवाद', '2083-05-01', '2026-08-16', 'साधारण तारिख', '1800000.00', 'विवादित', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-08-15 21:32:34', '2026-08-16 14:44:56')
-- @@
INSERT INTO `cases` (`id`, `plaintiff`, `defendant`, `case_type_id`, `case_number`, `court_id`, `branch`, `province`, `waresh_name`, `waresh_contact`, `waresh_email`, `registration_date_bs`, `registration_date_ad`, `status`, `current_status_detail`, `next_hearing_date_bs`, `next_hearing_date_ad`, `hearing_type`, `claim_amount`, `amount_type`, `decision_date_bs`, `decision_date_ad`, `decision_result`, `remarks`, `created_by`, `updated_by`, `deleted_at`, `deleted_by`, `created_at`, `updated_at`) VALUES (3, 'गरिमा विकास बैंक लिमिटेड', 'हरि प्रसाद गुरुङ', 3, '०८०-CV-०७८९', 9, 'भरतपुर', 'बागमती', 'कृष्ण थापा', '9856000003', 'waresh3@example.com', '2080-11-20', '2024-03-04', 'running', 'फैसला भइसकेको', '2083-05-04', '2026-08-19', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-08-15 21:32:34', '2026-08-16 14:44:56')
-- @@
INSERT INTO `cases` (`id`, `plaintiff`, `defendant`, `case_type_id`, `case_number`, `court_id`, `branch`, `province`, `waresh_name`, `waresh_contact`, `waresh_email`, `registration_date_bs`, `registration_date_ad`, `status`, `current_status_detail`, `next_hearing_date_bs`, `next_hearing_date_ad`, `hearing_type`, `claim_amount`, `amount_type`, `decision_date_bs`, `decision_date_ad`, `decision_result`, `remarks`, `created_by`, `updated_by`, `deleted_at`, `deleted_by`, `created_at`, `updated_at`) VALUES (4, 'नबिल बैंक लिमिटेड', 'मोहन थापा', 6, '०८१-CV-०६१२', 7, 'दरबारमार्ग', 'बागमती', 'दीपक राई', '9841222333', 'waresh4@example.com', '2081-06-01', '2024-09-16', 'running', NULL, '2083-05-06', '2026-08-21', 'पेशी', '3200000.00', 'दावी', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-08-16 00:59:06', '2026-08-16 14:44:56')
-- @@
INSERT INTO `cases` (`id`, `plaintiff`, `defendant`, `case_type_id`, `case_number`, `court_id`, `branch`, `province`, `waresh_name`, `waresh_contact`, `waresh_email`, `registration_date_bs`, `registration_date_ad`, `status`, `current_status_detail`, `next_hearing_date_bs`, `next_hearing_date_ad`, `hearing_type`, `claim_amount`, `amount_type`, `decision_date_bs`, `decision_date_ad`, `decision_result`, `remarks`, `created_by`, `updated_by`, `deleted_at`, `deleted_by`, `created_at`, `updated_at`) VALUES (5, 'हिमालयन बैंक', 'कमला श्रेष्ठ', 2, '०८१-CV-०७३४', 9, 'भरतपुर', 'बागमती', 'सन्देश गुरुङ', '9856111222', 'waresh5@example.com', '2081-06-10', '2024-09-25', 'running', NULL, '2083-05-09', '2026-08-24', 'साधारण तारिख', '1450000.00', 'विवादित', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-08-16 00:59:06', '2026-08-16 14:44:56')
-- @@
INSERT INTO `cases` (`id`, `plaintiff`, `defendant`, `case_type_id`, `case_number`, `court_id`, `branch`, `province`, `waresh_name`, `waresh_contact`, `waresh_email`, `registration_date_bs`, `registration_date_ad`, `status`, `current_status_detail`, `next_hearing_date_bs`, `next_hearing_date_ad`, `hearing_type`, `claim_amount`, `amount_type`, `decision_date_bs`, `decision_date_ad`, `decision_result`, `remarks`, `created_by`, `updated_by`, `deleted_at`, `deleted_by`, `created_at`, `updated_at`) VALUES (6, 'गरिमा विकास बैंक लिमिटेड', 'राजु महर्जन', 3, '०८१-CV-०८९१', 17, 'ललितपुर', 'बागमती', 'अनिता कार्की', '9801333444', 'waresh6@example.com', '2081-06-20', '2024-10-05', 'running', NULL, '2083-05-02', '2026-08-17', 'पेशी', '6750000.00', 'दावी', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-08-16 00:59:06', '2026-08-16 14:44:56')
-- @@
CREATE TABLE IF NOT EXISTS `case_hearings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `case_id` int NOT NULL,
  `hearing_date_bs` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `hearing_date_ad` date DEFAULT NULL,
  `hearing_type` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` text COLLATE utf8mb4_unicode_ci,
  `next_hearing_date_bs` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_hearing_date_ad` date DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_h_case` (`case_id`),
  KEY `idx_h_next_ad` (`next_hearing_date_ad`),
  CONSTRAINT `fk_hearing_case` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- @@
INSERT INTO `case_hearings` (`id`, `case_id`, `hearing_date_bs`, `hearing_date_ad`, `hearing_type`, `note`, `next_hearing_date_bs`, `next_hearing_date_ad`, `created_by`, `created_at`) VALUES (1, 1, '2083-03-20', '2026-07-04', 'पेशी', 'प्रारम्भिक बहस सम्पन्न', '2083-04-02', '2026-08-15', NULL, '2026-08-15 21:32:34')
-- @@
INSERT INTO `case_hearings` (`id`, `case_id`, `hearing_date_bs`, `hearing_date_ad`, `hearing_type`, `note`, `next_hearing_date_bs`, `next_hearing_date_ad`, `created_by`, `created_at`) VALUES (2, 2, '2083-03-22', '2026-07-06', 'साधारण तारिख', 'तारिख सारियो', '2083-04-05', '2026-07-20', NULL, '2026-08-15 21:32:34')
-- @@
CREATE TABLE IF NOT EXISTS `case_documents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `case_id` int NOT NULL,
  `file` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_name` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` text COLLATE utf8mb4_unicode_ci,
  `uploaded_by` int DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `deleted_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_d_case` (`case_id`),
  CONSTRAINT `fk_doc_case` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- @@
CREATE TABLE IF NOT EXISTS `deletion_requests` (
  `id` int NOT NULL AUTO_INCREMENT,
  `case_id` int NOT NULL,
  `reason` text COLLATE utf8mb4_unicode_ci,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `requested_by` int DEFAULT NULL,
  `requested_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `decided_by` int DEFAULT NULL,
  `decided_at` timestamp NULL DEFAULT NULL,
  `decision_note` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `idx_dr_case` (`case_id`),
  KEY `idx_dr_status` (`status`),
  CONSTRAINT `fk_dr_case` FOREIGN KEY (`case_id`) REFERENCES `cases` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- @@
CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW `case_report_v` AS select `c`.`id` AS `id`,`c`.`case_number` AS `case_number`,`c`.`plaintiff` AS `plaintiff`,`c`.`defendant` AS `defendant`,`c`.`waresh_name` AS `waresh_name`,`c`.`waresh_contact` AS `waresh_contact`,`c`.`waresh_email` AS `waresh_email`,`c`.`status` AS `status`,(case `c`.`status` when 'running' then 'चलिरहेको' when 'decided' then 'फैसला भएको' else coalesce(`c`.`status`,'—') end) AS `status_label`,coalesce(nullif(`c`.`province`,''),'—') AS `province`,coalesce(nullif(`c`.`hearing_type`,''),'तोकिएको छैन') AS `hearing_type`,`c`.`branch` AS `branch`,`c`.`claim_amount` AS `claim_amount`,`c`.`next_hearing_date_ad` AS `next_hearing_date_ad`,`c`.`next_hearing_date_bs` AS `next_hearing_date_bs`,`c`.`registration_date_ad` AS `registration_date_ad`,`c`.`registration_date_bs` AS `registration_date_bs`,coalesce(`co`.`name`,'—') AS `court_name`,coalesce(`ct`.`name`,'—') AS `case_type_name` from ((`cases` `c` left join `courts` `co` on((`co`.`id` = `c`.`court_id`))) left join `case_types` `ct` on((`ct`.`id` = `c`.`case_type_id`))) where (`c`.`deleted_at` is null)
