SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS quote_sequences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,prefix VARCHAR(20) DEFAULT 'DEV-',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),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quotes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(50) NOT NULL,version_no SMALLINT UNSIGNED DEFAULT 1,root_quote_id BIGINT UNSIGNED,previous_quote_id BIGINT UNSIGNED,customer_id BIGINT UNSIGNED NOT NULL,contact_id BIGINT UNSIGNED,billing_address_id BIGINT UNSIGNED,shipping_address_id BIGINT UNSIGNED,salesperson_id BIGINT UNSIGNED NOT NULL,warehouse_id BIGINT UNSIGNED,quote_date DATE NOT NULL,valid_until DATE,currency_code CHAR(3) DEFAULT 'MAD',price_level_id BIGINT UNSIGNED,payment_terms VARCHAR(120),delivery_terms VARCHAR(120),payment_method VARCHAR(80),customer_reference VARCHAR(100),subject VARCHAR(190),internal_notes TEXT,customer_notes TEXT,terms TEXT,status VARCHAR(30) DEFAULT 'draft',gross_subtotal DECIMAL(19,4) DEFAULT 0,line_discount_total DECIMAL(19,4) DEFAULT 0,global_discount_type VARCHAR(10),global_discount_value 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_amount DECIMAL(19,4) DEFAULT 0,net_due DECIMAL(19,4) DEFAULT 0,customer_snapshot JSON,contact_snapshot JSON,billing_snapshot JSON,shipping_snapshot JSON,validated_by BIGINT UNSIGNED,validated_at DATETIME,sent_at DATETIME,accepted_at DATETIME,rejected_at DATETIME,cancelled_at DATETIME,converted_order_id BIGINT UNSIGNED,converted_invoice_id BIGINT UNSIGNED,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,number,version_no),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(root_quote_id) REFERENCES quotes(id),FOREIGN KEY(previous_quote_id) REFERENCES quotes(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(shipping_address_id) REFERENCES third_party_addresses(id),FOREIGN KEY(salesperson_id) REFERENCES users(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),FOREIGN KEY(price_level_id) REFERENCES price_levels(id),FOREIGN KEY(created_by) REFERENCES users(id),CHECK(status IN('draft','validated','sent','viewed','accepted','rejected','expired','cancelled','converted','archived'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quote_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,quote_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,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_type VARCHAR(10) DEFAULT 'percent',discount_value DECIMAL(19,4) 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,FOREIGN KEY(quote_id) REFERENCES quotes(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),UNIQUE(quote_id,sort_order),CHECK(line_type IN('product','service','free','title','subtotal','comment'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quote_status_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,quote_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED,from_status VARCHAR(30),to_status VARCHAR(30) NOT NULL,contact_name VARCHAR(190),comment TEXT,rejection_reason_id BIGINT UNSIGNED,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(quote_id) REFERENCES quotes(id),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quote_rejection_reasons(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(190) NOT NULL,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,name),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE quote_status_history ADD CONSTRAINT fk_quote_rejection FOREIGN KEY(rejection_reason_id) REFERENCES quote_rejection_reasons(id);
CREATE TABLE IF NOT EXISTS quote_attachments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,quote_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(quote_id) REFERENCES quotes(id),FOREIGN KEY(uploaded_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quote_emails(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,quote_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(quote_id) REFERENCES quotes(id),FOREIGN KEY(sent_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quote_signatures(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,quote_id BIGINT UNSIGNED NOT NULL,signer_name VARCHAR(190) NOT NULL,signer_title VARCHAR(120),signature_path VARCHAR(255),signed_document_path VARCHAR(255),ip_address VARCHAR(45),comment TEXT,signed_at DATETIME NOT NULL,created_by BIGINT UNSIGNED NOT NULL,FOREIGN KEY(quote_id) REFERENCES quotes(id),FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quote_followups(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,quote_id BIGINT UNSIGNED NOT NULL,followup_date DATE NOT NULL,responsible_id BIGINT UNSIGNED NOT NULL,comment TEXT,status VARCHAR(20) DEFAULT 'planned',completed_at DATETIME,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(quote_id) REFERENCES quotes(id),FOREIGN KEY(responsible_id) REFERENCES users(id),CHECK(status IN('planned','completed','cancelled'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO quote_sequences(company_id,current_year) VALUES(1,YEAR(CURDATE()));
INSERT IGNORE INTO quote_rejection_reasons(company_id,name) VALUES(1,'Prix trop élevé'),(1,'Délai trop long'),(1,'Client a choisi un concurrent'),(1,'Projet annulé'),(1,'Autre');
INSERT IGNORE INTO permissions(module,action,name) VALUES('quotes','update','Devis — Modifier'),('quotes','send','Devis — Envoyer'),('quotes','accept','Devis — Accepter'),('quotes','reject','Devis — Refuser'),('quotes','cancel','Devis — Annuler'),('quotes','archive','Devis — Archiver'),('quotes','duplicate','Devis — Dupliquer'),('quotes','revise','Devis — Réviser'),('quotes','manage_discount','Devis — Gérer remises'),('quotes','override_discount_limit','Devis — Dépasser remise'),('quotes','edit_price','Devis — Modifier prix'),('quotes','view_margin','Devis — Voir marge'),('quotes','manage_signature','Devis — Signature'),('quotes','manage_attachments','Devis — Pièces jointes');
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='quotes';