CREATE TABLE `collection_customer_profiles` (`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,`credit_limit` real DEFAULT 0 NOT NULL,`vip` integer DEFAULT 0 NOT NULL,`automation_paused` integer DEFAULT 0 NOT NULL,`pause_reason` text,`portal_enabled` integer DEFAULT 1 NOT NULL,`preferred_channels` text DEFAULT '["email"]' NOT NULL,`updated_by` text NOT NULL,`updated_at` text NOT NULL);
CREATE UNIQUE INDEX `collection_customer_profiles_unique` ON `collection_customer_profiles` (`company_id`,`customer_key`);
CREATE INDEX `idx_collection_customer_profiles_flags` ON `collection_customer_profiles` (`company_id`,`vip`,`automation_paused`);
--> statement-breakpoint
CREATE TABLE `collection_workflows` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,`company_id` text REFERENCES `companies`(`id`),`name` text NOT NULL,`description` text,`segment` text DEFAULT 'standard' NOT NULL,`priority` integer DEFAULT 100 NOT NULL,`active` integer DEFAULT 1 NOT NULL,`created_by` text NOT NULL,`created_at` text NOT NULL,`updated_at` text NOT NULL);
CREATE INDEX `idx_collection_workflows_active` ON `collection_workflows` (`company_id`,`active`,`priority`);
--> statement-breakpoint
CREATE TABLE `collection_workflow_steps` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,`workflow_id` integer NOT NULL REFERENCES `collection_workflows`(`id`) ON DELETE CASCADE,`sequence` integer NOT NULL,`name` text NOT NULL,`trigger_offset_days` integer NOT NULL,`channel` text NOT NULL CHECK (`channel` IN ('email','sms','whatsapp','postal')),`template_code` text NOT NULL,`tone` text NOT NULL CHECK (`tone` IN ('preventive','friendly','firm','formal','legal')),`human_approval` integer DEFAULT 1 NOT NULL,`conditions_json` text DEFAULT '{}' NOT NULL,`active` integer DEFAULT 1 NOT NULL);
CREATE UNIQUE INDEX `collection_workflow_steps_sequence_unique` ON `collection_workflow_steps` (`workflow_id`,`sequence`);
CREATE INDEX `idx_collection_workflow_steps_trigger` ON `collection_workflow_steps` (`workflow_id`,`active`,`trigger_offset_days`);
--> statement-breakpoint
CREATE TABLE `collection_communications` (`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,`cash_flow_id` integer REFERENCES `cash_flows`(`id`),`workflow_id` integer REFERENCES `collection_workflows`(`id`),`workflow_step_id` integer REFERENCES `collection_workflow_steps`(`id`),`channel` text NOT NULL CHECK (`channel` IN ('email','sms','whatsapp','postal','phone','note','portal')),`direction` text DEFAULT 'outbound' NOT NULL CHECK (`direction` IN ('outbound','inbound','internal')),`recipient` text,`subject` text,`content` text NOT NULL,`status` text NOT NULL CHECK (`status` IN ('approval_required','queued','sent','delivered','failed','cancelled','logged')),`provider` text,`provider_message_id` text,`failure_reason` text,`approved_by` text,`approved_at` text,`sent_at` text,`created_by` text NOT NULL,`created_at` text NOT NULL);
CREATE INDEX `idx_collection_communications_customer` ON `collection_communications` (`company_id`,`customer_key`,`created_at`);
CREATE INDEX `idx_collection_communications_queue` ON `collection_communications` (`status`,`channel`,`created_at`);
--> statement-breakpoint
CREATE TABLE `collection_disputes` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,`legacy_case_id` integer UNIQUE,`company_id` text NOT NULL REFERENCES `companies`(`id`),`customer_key` text NOT NULL,`customer_name` text NOT NULL,`cash_flow_id` integer REFERENCES `cash_flows`(`id`),`category` text NOT NULL CHECK (`category` IN ('missing_goods','pricing_error','incorrect_po','quality','duplicate','administrative','other')),`amount` real NOT NULL CHECK (`amount` > 0),`description` text NOT NULL,`status` text DEFAULT 'open' NOT NULL CHECK (`status` IN ('open','investigating','waiting_customer','resolved','rejected','cancelled')),`pause_dunning` integer DEFAULT 1 NOT NULL,`owner_name` text,`resolution` text,`opened_at` text NOT NULL,`resolved_at` text,`updated_at` text NOT NULL);
CREATE INDEX `idx_collection_disputes_open` ON `collection_disputes` (`company_id`,`customer_key`,`status`);
CREATE INDEX `idx_collection_disputes_invoice` ON `collection_disputes` (`cash_flow_id`,`status`);
--> statement-breakpoint
CREATE TABLE `collection_promises` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,`legacy_case_id` integer UNIQUE,`company_id` text NOT NULL REFERENCES `companies`(`id`),`customer_key` text NOT NULL,`customer_name` text NOT NULL,`cash_flow_id` integer REFERENCES `cash_flows`(`id`),`amount` real NOT NULL CHECK (`amount` > 0),`promised_date` text NOT NULL,`status` text DEFAULT 'open' NOT NULL CHECK (`status` IN ('open','kept','breached','cancelled')),`source` text DEFAULT 'manual' NOT NULL,`notes` text,`owner_name` text,`created_by` text NOT NULL,`created_at` text NOT NULL,`closed_at` text,`updated_at` text NOT NULL);
CREATE INDEX `idx_collection_promises_due` ON `collection_promises` (`status`,`promised_date`);
CREATE INDEX `idx_collection_promises_customer` ON `collection_promises` (`company_id`,`customer_key`,`status`);
--> statement-breakpoint
CREATE TABLE `collection_tasks` (`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,`cash_flow_id` integer REFERENCES `cash_flows`(`id`),`promise_id` integer REFERENCES `collection_promises`(`id`),`dispute_id` integer REFERENCES `collection_disputes`(`id`),`communication_id` integer REFERENCES `collection_communications`(`id`),`task_type` text NOT NULL CHECK (`task_type` IN ('review','call','approve_message','dispute_followup','promise_breached','legal_handover')),`title` text NOT NULL,`description` text,`priority_score` integer DEFAULT 0 NOT NULL,`amount_at_risk` real DEFAULT 0 NOT NULL,`due_date` text NOT NULL,`assignee` text,`status` text DEFAULT 'open' NOT NULL CHECK (`status` IN ('open','in_progress','done','cancelled')),`completed_at` text,`created_at` text NOT NULL,`updated_at` text NOT NULL);
CREATE INDEX `idx_collection_tasks_queue` ON `collection_tasks` (`status`,`due_date`,`priority_score`);
CREATE INDEX `idx_collection_tasks_assignee` ON `collection_tasks` (`assignee`,`status`,`due_date`);
--> statement-breakpoint
CREATE TABLE `collection_portal_tokens` (`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,`token_hash` text NOT NULL UNIQUE,`expires_at` text NOT NULL,`last_accessed_at` text,`revoked_at` text,`created_by` text NOT NULL,`created_at` text NOT NULL);
CREATE INDEX `idx_collection_portal_tokens_customer` ON `collection_portal_tokens` (`company_id`,`customer_key`,`expires_at`);
--> statement-breakpoint
CREATE TABLE `collection_payment_intents` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,`portal_token_id` integer NOT NULL REFERENCES `collection_portal_tokens`(`id`),`cash_flow_id` integer REFERENCES `cash_flows`(`id`),`provider` text NOT NULL CHECK (`provider` IN ('stripe','sepa','local')),`amount` real NOT NULL CHECK (`amount` > 0),`currency` text DEFAULT 'MAD' NOT NULL,`status` text DEFAULT 'created' NOT NULL CHECK (`status` IN ('created','pending','succeeded','failed','cancelled')),`provider_reference` text,`checkout_url` text,`created_at` text NOT NULL,`updated_at` text NOT NULL);
CREATE INDEX `idx_collection_payment_intents_status` ON `collection_payment_intents` (`status`,`created_at`);
--> statement-breakpoint
CREATE TABLE `collection_metric_snapshots` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,`company_id` text NOT NULL REFERENCES `companies`(`id`),`snapshot_date` text NOT NULL,`total_receivables` real NOT NULL,`overdue_receivables` real NOT NULL,`current_receivables` real NOT NULL,`credit_sales_period` real DEFAULT 0 NOT NULL,`cash_collected_period` real DEFAULT 0 NOT NULL,`dso` real DEFAULT 0 NOT NULL,`cei` real DEFAULT 0 NOT NULL,`created_at` text NOT NULL);
CREATE UNIQUE INDEX `collection_metric_snapshots_unique` ON `collection_metric_snapshots` (`company_id`,`snapshot_date`);
CREATE INDEX `idx_collection_metric_snapshots_date` ON `collection_metric_snapshots` (`snapshot_date`);
--> statement-breakpoint
INSERT INTO `collection_workflows` (`company_id`,`name`,`description`,`segment`,`priority`,`active`,`created_by`,`created_at`,`updated_at`) SELECT NULL,'Scénario standard multicanal','Préventif, amiable, ferme, mise en demeure puis transmission juridique.','standard',100,1,'Moventra ERP',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP WHERE NOT EXISTS (SELECT 1 FROM `collection_workflows` WHERE `name`='Scénario standard multicanal');
INSERT INTO `collection_workflow_steps` (`workflow_id`,`sequence`,`name`,`trigger_offset_days`,`channel`,`template_code`,`tone`,`human_approval`,`conditions_json`,`active`) SELECT id,1,'Rappel préventif',-7,'email','preventive','preventive',1,'{"excludeVip":true,"excludeDisputed":true}',1 FROM collection_workflows WHERE name='Scénario standard multicanal';
INSERT INTO `collection_workflow_steps` (`workflow_id`,`sequence`,`name`,`trigger_offset_days`,`channel`,`template_code`,`tone`,`human_approval`,`conditions_json`,`active`) SELECT id,2,'Relance amiable',1,'email','friendly','friendly',1,'{"excludeVip":true,"excludeDisputed":true}',1 FROM collection_workflows WHERE name='Scénario standard multicanal';
INSERT INTO `collection_workflow_steps` (`workflow_id`,`sequence`,`name`,`trigger_offset_days`,`channel`,`template_code`,`tone`,`human_approval`,`conditions_json`,`active`) SELECT id,3,'Escalade SMS',7,'sms','friendly_sms','firm',1,'{"excludeVip":true,"excludeDisputed":true}',1 FROM collection_workflows WHERE name='Scénario standard multicanal';
INSERT INTO `collection_workflow_steps` (`workflow_id`,`sequence`,`name`,`trigger_offset_days`,`channel`,`template_code`,`tone`,`human_approval`,`conditions_json`,`active`) SELECT id,4,'Mise en demeure',15,'postal','formal_notice','formal',1,'{"excludeVip":true,"excludeDisputed":true}',1 FROM collection_workflows WHERE name='Scénario standard multicanal';
INSERT INTO `collection_workflow_steps` (`workflow_id`,`sequence`,`name`,`trigger_offset_days`,`channel`,`template_code`,`tone`,`human_approval`,`conditions_json`,`active`) SELECT id,5,'Transmission juridique',30,'postal','legal_handover','legal',1,'{"excludeVip":true,"excludeDisputed":true}',1 FROM collection_workflows WHERE name='Scénario standard multicanal';
--> statement-breakpoint
INSERT OR IGNORE INTO collection_disputes (legacy_case_id,company_id,customer_key,customer_name,category,amount,description,status,pause_dunning,owner_name,opened_at,resolved_at,updated_at) SELECT id,company_id,customer_key,customer_name,'other',amount,reason,CASE WHEN status='resolved' THEN 'resolved' WHEN status='cancelled' THEN 'cancelled' ELSE 'open' END,1,owner_name,created_at,CASE WHEN status='resolved' THEN updated_at ELSE NULL END,updated_at FROM collection_cases WHERE case_type='dispute';
INSERT OR IGNORE INTO collection_promises (legacy_case_id,company_id,customer_key,customer_name,amount,promised_date,status,source,notes,owner_name,created_by,created_at,closed_at,updated_at) SELECT id,company_id,customer_key,customer_name,amount,COALESCE(due_date,substr(created_at,1,10)),CASE WHEN status='kept' THEN 'kept' WHEN status='broken' THEN 'breached' WHEN status='cancelled' THEN 'cancelled' ELSE 'open' END,'legacy',reason,owner_name,created_by,created_at,CASE WHEN status IN ('kept','broken','cancelled') THEN updated_at ELSE NULL END,updated_at FROM collection_cases WHERE case_type='promise';
--> statement-breakpoint
PRAGMA optimize;