-- Role permissions for the active PET SPA Customer and Owner areas.
CREATE TABLE IF NOT EXISTS permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_key VARCHAR(100) NOT NULL UNIQUE,
    permission_name VARCHAR(150) NOT NULL,
    module VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role ENUM('customer', 'owner') NOT NULL,
    permission_key VARCHAR(100) NOT NULL,
    allowed TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_permissions_role_key (role, permission_key),
    CONSTRAINT fk_role_permissions_permission
        FOREIGN KEY (permission_key) REFERENCES permissions(permission_key)
        ON DELETE CASCADE,
    INDEX idx_role_permissions_role_allowed (role, allowed)
) ENGINE=InnoDB;

INSERT INTO permissions (permission_key, permission_name, module) VALUES
('customer.dashboard', 'Tổng quan khách hàng', 'customer'),
('customer.booking.create', 'Đặt lịch', 'customer'),
('customer.booking.view', 'Lịch hẹn của tôi', 'customer'),
('customer.notification.view', 'Thông báo', 'customer'),
('customer.profile.manage', 'Hồ sơ và mật khẩu', 'customer'),
('owner.dashboard', 'Tổng quan Owner', 'owner'),
('owner.booking.manage', 'Quản lý lịch hẹn', 'owner'),
('owner.service.manage', 'Quản lý dịch vụ', 'owner'),
('owner.service_category.manage', 'Danh mục dịch vụ', 'owner'),
('owner.business_hours.manage', 'Giờ phục vụ', 'owner'),
('owner.customer.view', 'Khách hàng', 'owner'),
('owner.post.manage', 'Quản lý bài viết', 'owner'),
('owner.post_category.manage', 'Danh mục bài viết', 'owner'),
('owner.gallery.manage', 'Thư viện', 'owner'),
('owner.contact.manage', 'Liên hệ', 'owner'),
('owner.settings.manage', 'Cài đặt', 'owner')
ON DUPLICATE KEY UPDATE
    permission_name = VALUES(permission_name),
    module = VALUES(module);

INSERT INTO role_permissions (role, permission_key, allowed)
SELECT
    CASE WHEN p.module = 'customer' THEN 'customer' ELSE 'owner' END,
    p.permission_key,
    1
FROM permissions p
ON DUPLICATE KEY UPDATE permission_key = VALUES(permission_key);