USE comptaai_maroc;

ALTER TABLE users
  MODIFY status ENUM('active','inactive','invited','suspended') NOT NULL DEFAULT 'active';

CREATE TABLE IF NOT EXISTS permissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  module VARCHAR(80) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY permissions_module_index (module)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT role_permissions_role_fk FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT role_permissions_permission_fk FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_permissions (
  user_id BIGINT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  is_allowed TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, permission_id),
  CONSTRAINT user_permissions_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT user_permissions_permission_fk FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_client_access (
  user_id BIGINT UNSIGNED NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, client_id),
  CONSTRAINT user_client_access_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT user_client_access_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS login_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  email VARCHAR(180) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  user_agent VARCHAR(255) NULL,
  succeeded TINYINT(1) NOT NULL DEFAULT 0,
  failure_reason VARCHAR(160) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY login_logs_user_index (user_id),
  KEY login_logs_email_index (email),
  CONSTRAINT login_logs_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

UPDATE roles SET name = 'Comptable legacy' WHERE slug = 'accountant' AND name = 'Comptable';

INSERT INTO roles (name, slug, description) VALUES
('Super Admin', 'super-admin', 'Acces complet a la plateforme'),
('Admin Cabinet', 'admin-cabinet', 'Administrateur du cabinet comptable'),
('Comptable', 'comptable', 'Comptable responsable de dossiers clients'),
('Assistant Comptable', 'assistant-comptable', 'Assistant avec acces operationnel limite'),
('Client', 'client', 'Utilisateur client limite a sa societe')
ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description);

INSERT INTO permissions (slug, name, module) VALUES
('clients.view', 'Voir les clients', 'Clients'),
('clients.create', 'Creer des clients', 'Clients'),
('clients.edit', 'Modifier les clients', 'Clients'),
('clients.delete', 'Supprimer les clients', 'Clients'),
('documents.view', 'Voir les documents', 'Documents'),
('documents.upload', 'Uploader des documents', 'Documents'),
('documents.delete', 'Supprimer les documents', 'Documents'),
('ocr.process', 'Lancer OCR', 'OCR'),
('accounting.validate', 'Valider les ecritures', 'Comptabilite'),
('tva.validate', 'Valider la TVA', 'TVA'),
('bank.manage', 'Gerer la banque', 'Banque'),
('exports.generate', 'Generer les exports', 'Exports'),
('reports.view', 'Voir les rapports', 'Rapports'),
('users.manage', 'Gerer les utilisateurs', 'Utilisateurs'),
('settings.manage', 'Gerer les parametres', 'Parametres')
ON DUPLICATE KEY UPDATE name = VALUES(name), module = VALUES(module);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM roles
CROSS JOIN permissions
WHERE roles.slug = 'super-admin';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM roles
CROSS JOIN permissions
WHERE roles.slug IN ('admin-cabinet', 'accounting-firm')
AND permissions.slug IN (
  'clients.view','clients.create','clients.edit','clients.delete',
  'documents.view','documents.upload','documents.delete','ocr.process',
  'accounting.validate','tva.validate','bank.manage','exports.generate',
  'reports.view','users.manage','settings.manage'
);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM roles
CROSS JOIN permissions
WHERE roles.slug IN ('comptable', 'accountant')
AND permissions.slug IN (
  'clients.view','clients.edit','documents.view','documents.upload',
  'ocr.process','accounting.validate','tva.validate','bank.manage',
  'exports.generate','reports.view'
);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM roles
CROSS JOIN permissions
WHERE roles.slug = 'assistant-comptable'
AND permissions.slug IN (
  'clients.view','documents.view','documents.upload','ocr.process','reports.view'
);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM roles
CROSS JOIN permissions
WHERE roles.slug IN ('client', 'company')
AND permissions.slug IN ('documents.view','documents.upload','reports.view');