SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_sequences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,invoice_type VARCHAR(30) NOT NULL,prefix VARCHAR(20) NOT NULL,include_year TINYINT(1) DEFAULT 1,include_month TINYINT(1) DEFAULT 0,padding TINYINT UNSIGNED DEFAULT 6,next_value BIGINT UNSIGNED DEFAULT 1,current_year SMALLINT UNSIGNED,reset_annually TINYINT(1) DEFAULT 1,UNIQUE(company_id,invoice_type),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS customer_invoices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,draft_reference VARCHAR(50) NOT NULL,accounting_number VARCHAR(50),idempotency_key CHAR(36) NOT NULL,invoice_type VARCHAR(30) NOT NULL DEFAULT 'standard',customer_id BIGINT UNSIGNED NOT NULL,contact_id BIGINT UNSIGNED,billing_address_id BIGINT UNSIGNED,quote_id BIGINT UNSIGNED,customer_order_id BIGINT UNSIGNED,original_invoice_id BIGINT UNSIGNED,invoice_date DATE NOT NULL,due_date DATE,payment_terms VARCHAR(120),planned_payment_method VARCHAR(80),currency_code CHAR(3) DEFAULT 'MAD',customer_reference VARCHAR(100),salesperson_id BIGINT UNSIGNED NOT NULL,subject VARCHAR(190),internal_notes TEXT,visible_notes TEXT,legal_mentions TEXT,status VARCHAR(30) DEFAULT 'draft',gross_subtotal DECIMAL(19,4) DEFAULT 0,line_discount_total DECIMAL(19,4) DEFAULT 0,global_discount_total DECIMAL(19,4) DEFAULT 0,subtotal DECIMAL(19,4) DEFAULT 0,tax_total DECIMAL(19,4) DEFAULT 0,shipping_cost DECIMAL(19,4) DEFAULT 0,additional_cost DECIMAL(19,4) DEFAULT 0,stamp_amount DECIMAL(19,4) DEFAULT 0,total DECIMAL(19,4) DEFAULT 0,deposit_deduction DECIMAL(19,4) DEFAULT 0,credit_total DECIMAL(19,4) DEFAULT 0,net_due DECIMAL(19,4) DEFAULT 0,paid_amount DECIMAL(19,4) DEFAULT 0,balance_due DECIMAL(19,4) DEFAULT 0,company_snapshot JSON,customer_snapshot JSON,validated_by BIGINT UNSIGNED,validated_at DATETIME,sent_at DATETIME,cancelled_at DATETIME,archived_at DATETIME,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,draft_reference),UNIQUE(company_id,accounting_number),UNIQUE(idempotency_key),INDEX idx_invoice_filters(company_id,status,invoice_type,invoice_date,due_date),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(customer_id)REFERENCES third_parties(id),FOREIGN KEY(contact_id)REFERENCES third_party_contacts(id),FOREIGN KEY(billing_address_id)REFERENCES third_party_addresses(id),FOREIGN KEY(quote_id)REFERENCES quotes(id),FOREIGN KEY(customer_order_id)REFERENCES customer_orders(id),FOREIGN KEY(original_invoice_id)REFERENCES customer_invoices(id),FOREIGN KEY(salesperson_id)REFERENCES users(id),FOREIGN KEY(validated_by)REFERENCES users(id),FOREIGN KEY(created_by)REFERENCES users(id),CHECK(invoice_type IN('standard','deposit','balance','recurring','manual','proforma','credit_note')),CHECK(status IN('draft','validated','sent','partially_paid','paid','overdue','cancelled','partially_credited','fully_credited','archived')))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS customer_invoice_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,line_type VARCHAR(20) NOT NULL,sort_order SMALLINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED,variant_id BIGINT UNSIGNED,packaging_id BIGINT UNSIGNED,customer_order_line_id BIGINT UNSIGNED,delivery_note_line_id BIGINT UNSIGNED,reference_snapshot VARCHAR(100),designation_snapshot VARCHAR(255) NOT NULL,description_snapshot TEXT,unit_snapshot VARCHAR(50),quantity DECIMAL(19,6) DEFAULT 0,unit_price DECIMAL(19,4) DEFAULT 0,discount_rate DECIMAL(9,6) DEFAULT 0,discount_total DECIMAL(19,4) DEFAULT 0,tax_rate DECIMAL(9,6) DEFAULT 0,line_subtotal DECIMAL(19,4) DEFAULT 0,line_tax DECIMAL(19,4) DEFAULT 0,line_total DECIMAL(19,4) DEFAULT 0,UNIQUE(customer_invoice_id,sort_order),INDEX idx_invoice_origin(customer_order_line_id,delivery_note_line_id),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id)ON DELETE CASCADE,FOREIGN KEY(product_id)REFERENCES products(id),FOREIGN KEY(variant_id)REFERENCES product_variants(id),FOREIGN KEY(packaging_id)REFERENCES product_packagings(id),FOREIGN KEY(customer_order_line_id)REFERENCES customer_order_lines(id),FOREIGN KEY(delivery_note_line_id)REFERENCES delivery_note_lines(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_document_links(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,document_type VARCHAR(30) NOT NULL,document_id BIGINT UNSIGNED NOT NULL,UNIQUE(customer_invoice_id,document_type,document_id),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id)ON DELETE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_status_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED,action VARCHAR(50) NOT NULL,old_status VARCHAR(30),new_status VARCHAR(30),old_values JSON,new_values JSON,comment TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(customer_invoice_id,created_at),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id)ON DELETE CASCADE,FOREIGN KEY(user_id)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_due_dates(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,due_date DATE NOT NULL,amount DECIMAL(19,4) NOT NULL,percentage DECIMAL(9,6),status VARCHAR(20) DEFAULT 'pending',comment VARCHAR(500),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id)ON DELETE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_attachments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,document_type VARCHAR(40),original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,mime_type VARCHAR(120) NOT NULL,size_bytes BIGINT UNSIGNED NOT NULL,sha256 CHAR(64) NOT NULL,uploaded_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(stored_name),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id)ON DELETE CASCADE,FOREIGN KEY(uploaded_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_emails(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,recipient VARCHAR(190) NOT NULL,subject VARCHAR(255) NOT NULL,status VARCHAR(20) NOT NULL,error_message VARCHAR(500),sent_by BIGINT UNSIGNED NOT NULL,sent_at DATETIME,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id),FOREIGN KEY(sent_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS invoice_reminders(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,level VARCHAR(30) NOT NULL,reminder_date DATE NOT NULL,responsible_id BIGINT UNSIGNED NOT NULL,comment TEXT,next_date DATE,status VARCHAR(20) DEFAULT 'planned',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id),FOREIGN KEY(responsible_id)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS recurring_invoice_templates(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,customer_id BIGINT UNSIGNED NOT NULL,frequency VARCHAR(20) NOT NULL,start_date DATE NOT NULL,end_date DATE,next_date DATE NOT NULL,is_active TINYINT(1) DEFAULT 1,payload JSON NOT NULL,created_by BIGINT UNSIGNED NOT NULL,FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(customer_id)REFERENCES third_parties(id),FOREIGN KEY(created_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS recurring_invoice_occurrences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,template_id BIGINT UNSIGNED NOT NULL,occurrence_date DATE NOT NULL,customer_invoice_id BIGINT UNSIGNED NOT NULL,UNIQUE(template_id,occurrence_date),FOREIGN KEY(template_id)REFERENCES recurring_invoice_templates(id),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS credit_notes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_invoice_id BIGINT UNSIGNED NOT NULL,credit_invoice_id BIGINT UNSIGNED NOT NULL,reason VARCHAR(500) NOT NULL,amount DECIMAL(19,4) NOT NULL,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(credit_invoice_id),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id),FOREIGN KEY(credit_invoice_id)REFERENCES customer_invoices(id),FOREIGN KEY(created_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO invoice_sequences(company_id,invoice_type,prefix,current_year)SELECT id,'standard','FAC-',YEAR(CURDATE())FROM companies;
INSERT IGNORE INTO invoice_sequences(company_id,invoice_type,prefix,current_year)SELECT id,'credit_note','AV-',YEAR(CURDATE())FROM companies;
INSERT IGNORE INTO permissions(module,action,name)VALUES('invoices','view','Factures — Afficher'),('invoices','create','Factures — Créer'),('invoices','update_draft','Factures — Modifier brouillon'),('invoices','validate','Factures — Valider'),('invoices','cancel','Factures — Annuler'),('invoices','archive','Factures — Archiver'),('invoices','print','Factures — Imprimer'),('invoices','export','Factures — Exporter'),('invoices','send','Factures — Envoyer'),('invoices','create_deposit','Factures — Acompte'),('invoices','create_balance','Factures — Solde'),('invoices','create_recurring','Factures — Récurrente'),('invoices','create_credit_note','Factures — Avoir'),('invoices','manage_due_dates','Factures — Échéances'),('invoices','manage_discount','Factures — Remises'),('invoices','edit_price','Factures — Prix'),('invoices','view_margin','Factures — Marges'),('invoices','manage_attachments','Factures — Pièces jointes'),('invoices','manage_reminders','Factures — Relances'),('invoices','override_validation','Factures — Forcer validation');
INSERT IGNORE INTO role_permissions(role_id,permission_id)SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='administrator'AND p.module='invoices';