CREATE TABLE `collection_contacts` (
  `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,
  `email` text,
  `phone` text,
  `contact_name` text,
  `owner_name` text,
  `risk_override` text CHECK (`risk_override` IN ('low','medium','high','critical')),
  `notes` text,
  `updated_at` text NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `collection_contacts_company_customer_unique` ON `collection_contacts` (`company_id`,`customer_key`);
--> statement-breakpoint
CREATE INDEX `idx_collection_contacts_company` ON `collection_contacts` (`company_id`,`customer_name`);
--> statement-breakpoint
CREATE TABLE `collection_templates` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `code` text NOT NULL UNIQUE CHECK (`code` IN ('preventive','friendly','firm','formal_notice')),
  `name` text NOT NULL,
  `alert_level` text NOT NULL CHECK (`alert_level` IN ('low','medium','high','critical')),
  `min_days_overdue` integer NOT NULL,
  `max_days_overdue` integer,
  `subject_template` text NOT NULL,
  `body_template` text NOT NULL,
  `accent_color` text NOT NULL,
  `active` integer DEFAULT 1 NOT NULL,
  `created_at` text NOT NULL,
  `updated_at` text NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_collection_templates_level` ON `collection_templates` (`alert_level`,`active`);
--> statement-breakpoint
CREATE TABLE `collection_reminders` (
  `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,
  `alert_level` text NOT NULL CHECK (`alert_level` IN ('low','medium','high','critical')),
  `channel` text DEFAULT 'email' NOT NULL CHECK (`channel` IN ('email','phone','note')),
  `template_id` integer REFERENCES `collection_templates`(`id`),
  `recipient` text,
  `subject` text,
  `content` text NOT NULL,
  `status` text NOT NULL CHECK (`status` IN ('draft','queued','sent','failed','logged')),
  `sent_by` text NOT NULL,
  `sent_at` text,
  `provider_message_id` text,
  `failure_reason` text,
  `created_at` text NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_collection_reminders_customer_date` ON `collection_reminders` (`company_id`,`customer_key`,`created_at`);
--> statement-breakpoint
CREATE INDEX `idx_collection_reminders_status_date` ON `collection_reminders` (`status`,`created_at`);
--> statement-breakpoint
CREATE TABLE `collection_reminder_invoices` (
  `reminder_id` integer NOT NULL REFERENCES `collection_reminders`(`id`) ON DELETE CASCADE,
  `cash_flow_id` integer NOT NULL REFERENCES `cash_flows`(`id`)
);
--> statement-breakpoint
CREATE UNIQUE INDEX `collection_reminder_invoices_unique` ON `collection_reminder_invoices` (`reminder_id`,`cash_flow_id`);
--> statement-breakpoint
CREATE INDEX `idx_collection_reminder_invoices_flow` ON `collection_reminder_invoices` (`cash_flow_id`);
--> statement-breakpoint
CREATE TABLE `collection_documents` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `cash_flow_id` integer NOT NULL REFERENCES `cash_flows`(`id`) ON DELETE CASCADE,
  `file_name` text NOT NULL,
  `storage_name` text NOT NULL UNIQUE,
  `mime_type` text NOT NULL,
  `size_bytes` integer NOT NULL,
  `uploaded_by` text NOT NULL,
  `uploaded_at` text NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_collection_documents_flow` ON `collection_documents` (`cash_flow_id`,`uploaded_at`);
--> statement-breakpoint
INSERT INTO `collection_templates` (`code`,`name`,`alert_level`,`min_days_overdue`,`max_days_overdue`,`subject_template`,`body_template`,`accent_color`,`active`,`created_at`,`updated_at`) VALUES
('preventive','Relance préventive','low',-365,-1,'Échéance prochaine · {{company}} · {{customer}}','Bonjour {{contact}},\n\nNous vous adressons un rappel préventif concernant {{invoice_count}} facture(s), pour un montant total de {{total_due}}, arrivant prochainement à échéance.\n\nNous vous remercions de bien vouloir programmer le règlement aux dates indiquées dans l’état joint.\n\nCordialement,\nService financier · {{company}}','#2563eb',1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP),
('friendly','Relance amiable','medium',0,14,'Relance amiable · factures échues · {{customer}}','Bonjour {{contact}},\n\nSauf erreur de notre part, {{invoice_count}} facture(s) représentant {{total_due}} sont arrivées à échéance.\n\nNous vous remercions de procéder au règlement ou de nous indiquer la date prévue de paiement.\n\nCordialement,\nService recouvrement · {{company}}','#d97706',1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP),
('firm','Relance ferme','high',15,44,'Relance ferme · encours de {{total_due}} · {{customer}}','Bonjour {{contact}},\n\nMalgré notre précédente relance, l’encours exigible de {{total_due}} demeure impayé. Le retard le plus ancien atteint {{max_delay}} jour(s).\n\nMerci de régulariser la situation sous 5 jours ou de nous transmettre immédiatement un échéancier ferme.\n\nService recouvrement · {{company}}','#dc2626',1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP),
('formal_notice','Mise en demeure','critical',45,NULL,'Mise en demeure de régler · {{customer}}','Madame, Monsieur,\n\nNous vous mettons formellement en demeure de régler la somme exigible de {{total_due}}, correspondant aux factures détaillées dans l’état joint.\n\nÀ défaut de règlement sous 8 jours, nous nous réservons le droit d’engager toute procédure utile au recouvrement de notre créance.\n\nService juridique et financier · {{company}}','#7f1d1d',1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
--> statement-breakpoint
PRAGMA optimize;