-- Check if role_id column exists in users table
SET @columnExists = 0;
SELECT COUNT(*) INTO @columnExists FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'role_id';

-- Add role_id column if it doesn't exist
SET @alterTableSQL = IF(@columnExists = 0, 
    'ALTER TABLE `users` ADD COLUMN `role_id` int(11) DEFAULT NULL AFTER `user_type`', 
    'SELECT "Column already exists"');

PREPARE alterTableStmt FROM @alterTableSQL;
EXECUTE alterTableStmt;
DEALLOCATE PREPARE alterTableStmt;

-- Create roles table if it doesn't exist
CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `permissions` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default roles if they don't exist
INSERT IGNORE INTO `roles` (`name`, `description`, `permissions`) VALUES
('Super Admin', 'Full access to all features', '["*"]'),
('Admin', 'Administrative access with some restrictions', '["dashboard_view","users_view","users_add","users_edit","properties_view","properties_add","properties_edit","properties_delete","rooms_view","rooms_add","rooms_edit","rooms_delete","bookings_view","bookings_manage","payments_view","payments_manage","reports_view"]'),
('Manager', 'Manage properties and bookings', '["dashboard_view","properties_view","properties_add","properties_edit","rooms_view","rooms_add","rooms_edit","bookings_view","bookings_manage","payments_view"]');