SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS expense_categories (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 code VARCHAR(30) NOT NULL,
 name VARCHAR(120) NOT NULL,
 description VARCHAR(500),
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 UNIQUE(company_id,code),
 FOREIGN KEY(company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expense_sequences (
 company_id BIGINT UNSIGNED PRIMARY KEY,
 prefix VARCHAR(20) NOT NULL DEFAULT 'DEP-',
 next_value BIGINT UNSIGNED NOT NULL DEFAULT 1,
 padding TINYINT UNSIGNED NOT NULL DEFAULT 6,
 current_year SMALLINT UNSIGNED NOT NULL,
 FOREIGN KEY(company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 number VARCHAR(50) NOT NULL,
 category_id BIGINT UNSIGNED NOT NULL,
 supplier_id BIGINT UNSIGNED,
 expense_date DATE NOT NULL,
 due_date DATE,
 description VARCHAR(255) NOT NULL,
 reference VARCHAR(100),
 currency_code CHAR(3) NOT NULL DEFAULT 'MAD',
 subtotal DECIMAL(19,4) NOT NULL DEFAULT 0,
 tax_amount DECIMAL(19,4) NOT NULL DEFAULT 0,
 total DECIMAL(19,4) NOT NULL DEFAULT 0,
 status VARCHAR(20) NOT NULL DEFAULT 'draft',
 payment_source_type VARCHAR(20),
 cashbox_id BIGINT UNSIGNED,
 bank_account_id BIGINT UNSIGNED,
 paid_at DATETIME,
 cash_movement_id BIGINT UNSIGNED,
 bank_movement_id BIGINT UNSIGNED,
 notes TEXT,
 validated_by BIGINT UNSIGNED,
 validated_at DATETIME,
 cancelled_by BIGINT UNSIGNED,
 cancelled_at DATETIME,
 cancellation_reason VARCHAR(500),
 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),
 INDEX idx_expense_filters(company_id,status,expense_date,category_id),
 FOREIGN KEY(company_id) REFERENCES companies(id),
 FOREIGN KEY(category_id) REFERENCES expense_categories(id),
 FOREIGN KEY(supplier_id) REFERENCES third_parties(id),
 FOREIGN KEY(cashbox_id) REFERENCES cashboxes(id),
 FOREIGN KEY(bank_account_id) REFERENCES bank_accounts(id),
 FOREIGN KEY(cash_movement_id) REFERENCES cash_movements(id),
 FOREIGN KEY(bank_movement_id) REFERENCES bank_movements(id),
 FOREIGN KEY(validated_by) REFERENCES users(id),
 FOREIGN KEY(cancelled_by) REFERENCES users(id),
 FOREIGN KEY(created_by) REFERENCES users(id),
 CHECK(status IN('draft','validated','paid','cancelled','archived')),
 CHECK(payment_source_type IS NULL OR payment_source_type IN('cashbox','bank')),
 CHECK(subtotal>=0 AND tax_amount>=0 AND total>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expense_status_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 expense_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(40) NOT NULL,
 old_status VARCHAR(20),
 new_status VARCHAR(20),
 details JSON,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(expense_id) REFERENCES expenses(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id),
 INDEX(expense_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO expense_categories(company_id,code,name)
SELECT id,'GENERAL','Frais généraux' FROM companies;
INSERT IGNORE INTO expense_sequences(company_id,current_year)
SELECT id,YEAR(CURDATE()) FROM companies;
INSERT IGNORE INTO permissions(module,action,name) VALUES
('expenses','view','Dépenses — Consulter'),('expenses','create','Dépenses — Créer'),
('expenses','update','Dépenses — Modifier'),('expenses','validate','Dépenses — Valider'),
('expenses','pay','Dépenses — Payer'),('expenses','cancel','Dépenses — Annuler'),
('expenses','manage_categories','Dépenses — Gérer les catégories'),('expenses','export','Dépenses — Exporter');
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='expenses';