SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_sequences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,document_type VARCHAR(30) NOT NULL,prefix VARCHAR(20) NOT NULL,padding TINYINT UNSIGNED DEFAULT 6,next_value BIGINT UNSIGNED DEFAULT 1,UNIQUE(company_id,document_type),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_requests(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(40) NOT NULL,request_date DATE NOT NULL,requester_id BIGINT UNSIGNED NOT NULL,warehouse_id BIGINT UNSIGNED NOT NULL,priority VARCHAR(20) DEFAULT 'normal',observations TEXT,status VARCHAR(20) DEFAULT 'draft',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,number),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(requester_id) REFERENCES users(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),CHECK(status IN('draft','pending','validated','rejected','cancelled','archived'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_request_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,purchase_request_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,quantity DECIMAL(19,6) NOT NULL,notes VARCHAR(255),FOREIGN KEY(purchase_request_id) REFERENCES purchase_requests(id) ON DELETE CASCADE,FOREIGN KEY(product_id) REFERENCES products(id),CHECK(quantity>0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_orders(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(40) NOT NULL,purchase_request_id BIGINT UNSIGNED,supplier_id BIGINT UNSIGNED NOT NULL,order_date DATE NOT NULL,expected_date DATE,warehouse_id BIGINT UNSIGNED NOT NULL,payment_terms VARCHAR(120),currency_code CHAR(3) DEFAULT 'MAD',observations TEXT,status VARCHAR(30) DEFAULT 'draft',subtotal DECIMAL(19,4) DEFAULT 0,discount_total DECIMAL(19,4) DEFAULT 0,tax_total DECIMAL(19,4) DEFAULT 0,total DECIMAL(19,4) DEFAULT 0,created_by BIGINT UNSIGNED NOT NULL,validated_by BIGINT UNSIGNED,validated_at DATETIME,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,number),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(purchase_request_id) REFERENCES purchase_requests(id),FOREIGN KEY(supplier_id) REFERENCES third_parties(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),FOREIGN KEY(created_by) REFERENCES users(id),CHECK(status IN('draft','validated','sent','partially_received','received','closed','cancelled'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_order_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,purchase_order_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,description VARCHAR(255) NOT NULL,quantity DECIMAL(19,6) NOT NULL,received_quantity DECIMAL(19,6) DEFAULT 0,invoiced_quantity DECIMAL(19,6) DEFAULT 0,unit_price DECIMAL(19,4) NOT NULL,tax_rate DECIMAL(9,6) DEFAULT 0,discount_rate DECIMAL(9,6) DEFAULT 0,line_total DECIMAL(19,4) NOT NULL,FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,FOREIGN KEY(product_id) REFERENCES products(id),CHECK(quantity>0 AND unit_price>=0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_receipts(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(40) NOT NULL,purchase_order_id BIGINT UNSIGNED NOT NULL,supplier_id BIGINT UNSIGNED NOT NULL,warehouse_id BIGINT UNSIGNED NOT NULL,receipt_date DATE NOT NULL,status VARCHAR(20) DEFAULT 'validated',observations TEXT,received_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,number),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id),FOREIGN KEY(supplier_id) REFERENCES third_parties(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),FOREIGN KEY(received_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_receipt_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,purchase_receipt_id BIGINT UNSIGNED NOT NULL,purchase_order_line_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,quantity DECIMAL(19,6) NOT NULL,unit_cost DECIMAL(19,4) NOT NULL,stock_movement_id BIGINT UNSIGNED NOT NULL,FOREIGN KEY(purchase_receipt_id) REFERENCES purchase_receipts(id) ON DELETE CASCADE,FOREIGN KEY(purchase_order_line_id) REFERENCES purchase_order_lines(id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(stock_movement_id) REFERENCES stock_movements(id),CHECK(quantity>0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_invoices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(40) NOT NULL,supplier_reference VARCHAR(100),supplier_id BIGINT UNSIGNED NOT NULL,purchase_order_id BIGINT UNSIGNED,purchase_receipt_id BIGINT UNSIGNED,invoice_date DATE NOT NULL,due_date DATE,currency_code CHAR(3) DEFAULT 'MAD',subtotal DECIMAL(19,4) DEFAULT 0,discount_total DECIMAL(19,4) DEFAULT 0,tax_total DECIMAL(19,4) DEFAULT 0,total DECIMAL(19,4) DEFAULT 0,paid_amount DECIMAL(19,4) DEFAULT 0,balance_due DECIMAL(19,4) DEFAULT 0,status VARCHAR(30) DEFAULT 'draft',created_by BIGINT UNSIGNED NOT NULL,validated_at DATETIME,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,number),UNIQUE(company_id,supplier_id,supplier_reference),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(supplier_id) REFERENCES third_parties(id),FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id),FOREIGN KEY(purchase_receipt_id) REFERENCES purchase_receipts(id),FOREIGN KEY(created_by) REFERENCES users(id),CHECK(status IN('draft','validated','partially_paid','paid','cancelled'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_invoice_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,supplier_invoice_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED,description VARCHAR(255) NOT NULL,quantity DECIMAL(19,6) NOT NULL,unit_price DECIMAL(19,4) NOT NULL,tax_rate DECIMAL(9,6) DEFAULT 0,discount_rate DECIMAL(9,6) DEFAULT 0,line_total DECIMAL(19,4) NOT NULL,FOREIGN KEY(supplier_invoice_id) REFERENCES supplier_invoices(id) ON DELETE CASCADE,FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_payments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,reference VARCHAR(80) NOT NULL,supplier_invoice_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(19,4) NOT NULL,payment_date DATE NOT NULL,payment_method VARCHAR(20) NOT NULL,bank_reference VARCHAR(120),cash_reference VARCHAR(120),user_id BIGINT UNSIGNED NOT NULL,observations TEXT,status VARCHAR(20) DEFAULT 'validated',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,reference),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(supplier_invoice_id) REFERENCES supplier_invoices(id),FOREIGN KEY(user_id) REFERENCES users(id),CHECK(amount>0),CHECK(payment_method IN('cash','transfer','check','card'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_returns(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(40) NOT NULL,supplier_id BIGINT UNSIGNED NOT NULL,purchase_receipt_id BIGINT UNSIGNED,warehouse_id BIGINT UNSIGNED NOT NULL,return_date DATE NOT NULL,reason VARCHAR(500) NOT NULL,user_id BIGINT UNSIGNED NOT NULL,status VARCHAR(20) DEFAULT 'validated',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,number),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(supplier_id) REFERENCES third_parties(id),FOREIGN KEY(purchase_receipt_id) REFERENCES purchase_receipts(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS supplier_return_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,supplier_return_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,quantity DECIMAL(19,6) NOT NULL,stock_movement_id BIGINT UNSIGNED NOT NULL,FOREIGN KEY(supplier_return_id) REFERENCES supplier_returns(id) ON DELETE CASCADE,FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(stock_movement_id) REFERENCES stock_movements(id),CHECK(quantity>0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,entity_type VARCHAR(40) NOT NULL,entity_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED,action VARCHAR(80) NOT NULL,description VARCHAR(500),old_values JSON,new_values JSON,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(user_id) REFERENCES users(id),INDEX(entity_type,entity_id,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO purchase_sequences(company_id,document_type,prefix) VALUES(1,'request','DA-'),(1,'order','CF-'),(1,'receipt','BRF-'),(1,'invoice','FF-'),(1,'return','RF-');
INSERT IGNORE INTO permissions(module,action,name) VALUES('purchases','update','Achats — Modifier'),('purchases','receive','Achats — Réceptionner'),('purchases','invoice','Achats — Facturer'),('purchases','payment','Achats — Payer'),('purchases','return','Achats — Retourner');
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='purchases';