SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS user_dashboard_preferences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,widgets JSON,default_period VARCHAR(30) DEFAULT 'month',UNIQUE(user_id),FOREIGN KEY(user_id)REFERENCES users(id)ON DELETE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS saved_report_filters(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,name VARCHAR(120) NOT NULL,report_type VARCHAR(50) NOT NULL,filters JSON NOT NULL,is_shared TINYINT(1) DEFAULT 0,UNIQUE(user_id,report_type,name),FOREIGN KEY(user_id)REFERENCES users(id)ON DELETE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS salesperson_targets(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,period_start DATE NOT NULL,period_end DATE NOT NULL,revenue_target DECIMAL(19,4) DEFAULT 0,collection_target DECIMAL(19,4) DEFAULT 0,margin_target DECIMAL(19,4) DEFAULT 0,new_customers_target INT DEFAULT 0,UNIQUE(user_id,period_start,period_end),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(user_id)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS report_cache(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,cache_key CHAR(64) NOT NULL,payload JSON NOT NULL,expires_at DATETIME NOT NULL,UNIQUE(company_id,cache_key),INDEX(expires_at),FOREIGN KEY(company_id)REFERENCES companies(id)ON DELETE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS report_generation_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,report_type VARCHAR(50) NOT NULL,format VARCHAR(10) NOT NULL,filters JSON,row_count BIGINT UNSIGNED DEFAULT 0,status VARCHAR(20) NOT NULL,error_message VARCHAR(500),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(user_id)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO permissions(module,action,name)VALUES('dashboard','view_financial','Dashboard — Financier'),('dashboard','view_margin','Dashboard — Marges'),('dashboard','customize','Dashboard — Personnaliser'),('reports','sales','Rapports — Ventes'),('reports','revenue','Rapports — CA'),('reports','margin','Rapports — Marges'),('reports','purchases','Rapports — Achats'),('reports','stock','Rapports — Stock'),('reports','customers','Rapports — Clients'),('reports','suppliers','Rapports — Fournisseurs'),('reports','payments','Rapports — Paiements'),('reports','cashboxes','Rapports — Caisses'),('reports','banks','Rapports — Banques'),('reports','salespeople','Rapports — Commerciaux'),('reports','products','Rapports — Produits'),('reports','tax','Rapports — TVA'),('reports','quotes','Rapports — Devis'),('reports','orders','Rapports — Commandes'),('reports','deliveries','Rapports — Livraisons'),('reports','export','Rapports — Exporter'),('reports','view_cost','Rapports — Coûts'),('reports','view_profit','Rapports — Bénéfices');
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 IN('dashboard','reports');