SET NAMES utf8mb4;
ALTER TABLE companies ADD COLUMN IF NOT EXISTS trade_name VARCHAR(190),ADD COLUMN IF NOT EXISTS ice VARCHAR(40),ADD COLUMN IF NOT EXISTS tax_id VARCHAR(40),ADD COLUMN IF NOT EXISTS rc VARCHAR(40),ADD COLUMN IF NOT EXISTS cnss VARCHAR(40),ADD COLUMN IF NOT EXISTS address_line VARCHAR(255),ADD COLUMN IF NOT EXISTS city VARCHAR(100),ADD COLUMN IF NOT EXISTS region VARCHAR(100),ADD COLUMN IF NOT EXISTS postal_code VARCHAR(20),ADD COLUMN IF NOT EXISTS country_code CHAR(2) DEFAULT 'MA',ADD COLUMN IF NOT EXISTS phone VARCHAR(40),ADD COLUMN IF NOT EXISTS secondary_phone VARCHAR(40),ADD COLUMN IF NOT EXISTS whatsapp VARCHAR(40),ADD COLUMN IF NOT EXISTS email VARCHAR(190),ADD COLUMN IF NOT EXISTS website VARCHAR(190),ADD COLUMN IF NOT EXISTS locale VARCHAR(10) DEFAULT 'fr',ADD COLUMN IF NOT EXISTS fiscal_year_start DATE,ADD COLUMN IF NOT EXISTS fiscal_year_end DATE;
CREATE TABLE IF NOT EXISTS system_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,setting_group VARCHAR(50) NOT NULL,setting_key VARCHAR(100) NOT NULL,value_text TEXT,value_type VARCHAR(20) DEFAULT 'string',is_secret TINYINT(1) DEFAULT 0,updated_by BIGINT UNSIGNED,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,setting_group,setting_key),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(updated_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS branding_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,logo_path VARCHAR(255),dark_logo_path VARCHAR(255),favicon_path VARCHAR(255),primary_color CHAR(7) DEFAULT '#2563eb',secondary_color CHAR(7) DEFAULT '#132238',button_color CHAR(7) DEFAULT '#2563eb',app_short_name VARCHAR(50) DEFAULT 'GCPro',footer_text VARCHAR(255),login_background_path VARCHAR(255),UNIQUE(company_id),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS currencies(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,code CHAR(3) NOT NULL,name VARCHAR(80) NOT NULL,symbol VARCHAR(10) NOT NULL,decimal_places TINYINT DEFAULT 2,symbol_position VARCHAR(10) DEFAULT 'after',decimal_separator CHAR(1) DEFAULT ',',thousands_separator CHAR(1) DEFAULT ' ',is_default TINYINT(1) DEFAULT 0,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,code),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS currency_rates(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,from_currency CHAR(3) NOT NULL,to_currency CHAR(3) NOT NULL,rate DECIMAL(19,8) NOT NULL,effective_date DATE NOT NULL,created_by BIGINT UNSIGNED NOT NULL,UNIQUE(company_id,from_currency,to_currency,effective_date),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(created_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payment_terms(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(120) NOT NULL,description VARCHAR(500),days_count SMALLINT DEFAULT 0,deposit_percent DECIMAL(9,4) DEFAULT 0,due_rule VARCHAR(30) DEFAULT 'invoice_date',is_default TINYINT(1) DEFAULT 0,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,name),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS email_templates(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,template_type VARCHAR(50) NOT NULL,locale VARCHAR(10) DEFAULT 'fr',subject VARCHAR(255) NOT NULL,body_html TEXT NOT NULL,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,template_type,locale),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS smtp_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,host VARCHAR(190),port SMALLINT DEFAULT 587,encryption VARCHAR(10) DEFAULT 'tls',username VARCHAR(190),password_ciphertext TEXT,from_email VARCHAR(190),from_name VARCHAR(190),reply_to VARCHAR(190),timeout_seconds SMALLINT DEFAULT 15,debug_enabled TINYINT(1) DEFAULT 0,UNIQUE(company_id),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notification_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED,role_id BIGINT UNSIGNED,event_code VARCHAR(50) NOT NULL,channel VARCHAR(20) NOT NULL DEFAULT 'internal',is_enabled TINYINT(1) DEFAULT 1,INDEX(company_id,event_code),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(user_id)REFERENCES users(id),FOREIGN KEY(role_id)REFERENCES roles(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS backup_jobs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,frequency VARCHAR(20) NOT NULL,run_time TIME NOT NULL,retention_count SMALLINT DEFAULT 7,compression TINYINT(1) DEFAULT 1,is_active TINYINT(1) DEFAULT 0,UNIQUE(company_id,frequency),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS backup_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,backup_type VARCHAR(20) NOT NULL,stored_name VARCHAR(255),size_bytes BIGINT UNSIGNED,sha256 CHAR(64),status VARCHAR(20) NOT NULL,error_message VARCHAR(500),created_by BIGINT UNSIGNED,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(created_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cron_jobs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,code VARCHAR(50) NOT NULL,name VARCHAR(120) NOT NULL,schedule_expression VARCHAR(100) NOT NULL,is_active TINYINT(1) DEFAULT 1,last_run_at DATETIME,next_run_at DATETIME,last_status VARCHAR(20),last_duration_ms INT,last_error VARCHAR(500),UNIQUE(company_id,code),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cron_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,cron_job_id BIGINT UNSIGNED NOT NULL,started_at DATETIME NOT NULL,finished_at DATETIME,status VARCHAR(20) NOT NULL,error_message VARCHAR(500),FOREIGN KEY(cron_job_id)REFERENCES cron_jobs(id)ON DELETE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS maintenance_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,is_enabled TINYINT(1) DEFAULT 0,message VARCHAR(500),allowed_ips TEXT,allow_admins TINYINT(1) DEFAULT 1,UNIQUE(company_id),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS regional_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,locale VARCHAR(10) DEFAULT 'fr',timezone VARCHAR(64) DEFAULT 'Africa/Casablanca',date_format VARCHAR(20) DEFAULT 'd/m/Y',time_format VARCHAR(20) DEFAULT 'H:i',first_weekday TINYINT DEFAULT 1,currency_position VARCHAR(10) DEFAULT 'after',amount_words_locale VARCHAR(10) DEFAULT 'fr',UNIQUE(company_id),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS pdf_templates(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,document_type VARCHAR(40) NOT NULL,template_name VARCHAR(80) NOT NULL,page_format VARCHAR(10) DEFAULT 'A4',orientation VARCHAR(10) DEFAULT 'portrait',options_json JSON,is_default TINYINT(1) DEFAULT 0,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,document_type,template_name),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO branding_settings(company_id)SELECT id FROM companies;INSERT IGNORE INTO regional_settings(company_id)SELECT id FROM companies;INSERT IGNORE INTO maintenance_settings(company_id)SELECT id FROM companies;INSERT IGNORE INTO currencies(company_id,code,name,symbol,is_default)SELECT id,'MAD','Dirham marocain','MAD',1 FROM companies;INSERT IGNORE INTO currencies(company_id,code,name,symbol)SELECT id,'EUR','Euro','€'FROM companies;INSERT IGNORE INTO currencies(company_id,code,name,symbol)SELECT id,'USD','Dollar américain','$'FROM companies;
INSERT IGNORE INTO permissions(module,action,name)VALUES('settings','company','Paramètres — Société'),('settings','branding','Paramètres — Apparence'),('settings','numbering','Paramètres — Numérotation'),('settings','currency','Paramètres — Devises'),('settings','tax','Paramètres — Taxes'),('settings','payment_terms','Paramètres — Conditions paiement'),('settings','payment_methods','Paramètres — Modes paiement'),('settings','commercial','Paramètres — Commercial'),('settings','stock','Paramètres — Stock'),('settings','financial','Paramètres — Finance'),('settings','pdf','Paramètres — PDF'),('settings','email','Paramètres — Email'),('settings','notifications','Paramètres — Notifications'),('settings','backup','Paramètres — Sauvegarde'),('settings','restore','Paramètres — Restauration'),('settings','cron','Paramètres — Cron'),('settings','logs','Paramètres — Journaux'),('settings','security','Paramètres — Sécurité'),('settings','maintenance','Paramètres — Maintenance'),('settings','regional','Paramètres — Régional'),('settings','import_export','Paramètres — Import/export');
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='settings';