CREATE TABLE `collection_cases` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `company_id` text NOT NULL REFERENCES `companies`(`id`),
  `customer_key` text NOT NULL,
  `customer_name` text NOT NULL,
  `case_type` text NOT NULL CHECK (`case_type` IN ('promise','dispute')),
  `amount` real NOT NULL CHECK (`amount` > 0),
  `due_date` text,
  `status` text NOT NULL CHECK (`status` IN ('open','kept','broken','resolved','cancelled')),
  `reason` text NOT NULL,
  `owner_name` text,
  `created_by` text NOT NULL,
  `created_at` text NOT NULL,
  `updated_at` text NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_collection_cases_customer` ON `collection_cases` (`company_id`,`customer_key`,`status`,`updated_at`);
--> statement-breakpoint
CREATE INDEX `idx_collection_cases_due` ON `collection_cases` (`case_type`,`status`,`due_date`);
--> statement-breakpoint
PRAGMA optimize;