-- Users Table
CREATE TABLE task8__User (
    pk_user INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    passwd VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Groups Table (Departments)
CREATE TABLE task8__Group (
    pk_group INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Permissions Table
CREATE TABLE task8__Permission (
    pk_permission INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    category VARCHAR(30) DEFAULT 'general',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User Permissions Junction Table
CREATE TABLE task8__hasPermission (
    pkfk_user INT,
    pkfk_permission INT,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    granted_by_user INT,
    is_denied BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (pkfk_user, pkfk_permission),
    FOREIGN KEY (pkfk_user) REFERENCES task8__User(pk_user) ON DELETE CASCADE,
    FOREIGN KEY (pkfk_permission) REFERENCES task8__Permission(pk_permission) ON DELETE CASCADE,
    FOREIGN KEY (granted_by_user) REFERENCES task8__User(pk_user)
);

-- Group Permissions Junction Table
CREATE TABLE task8__groupPermission (
    pkfk_group INT,
    pkfk_permission INT,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    granted_by_user INT,
    PRIMARY KEY (pkfk_group, pkfk_permission),
    FOREIGN KEY (pkfk_group) REFERENCES task8__Group(pk_group) ON DELETE CASCADE,
    FOREIGN KEY (pkfk_permission) REFERENCES task8__Permission(pk_permission) ON DELETE CASCADE,
    FOREIGN KEY (granted_by_user) REFERENCES task8__User(pk_user)
);

-- Workflows Table
CREATE TABLE task8__Workflow (
    pk_workflow INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- States Table
CREATE TABLE task8__State (
    pk_state INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    order_position INT NOT NULL,
    is_initial BOOLEAN DEFAULT FALSE,
    is_final BOOLEAN DEFAULT FALSE,
    color VARCHAR(7) DEFAULT '#cccccc',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fk_workflow INT NOT NULL,
    FOREIGN KEY (fk_workflow) REFERENCES task8__Workflow(pk_workflow) ON DELETE CASCADE
);

-- Requests Table (Must be defined before hasState because of foreign key)
CREATE TABLE task8__Request (
    pk_request INT PRIMARY KEY AUTO_INCREMENT,
    created_by INT NOT NULL,
    assigned_to INT,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    due_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    fk_current_state INT NOT NULL,
    fk_workflow INT NOT NULL,
    FOREIGN KEY (created_by) REFERENCES task8__User(pk_user),
    FOREIGN KEY (assigned_to) REFERENCES task8__User(pk_user),
    FOREIGN KEY (fk_current_state) REFERENCES task8__State(pk_state),
    FOREIGN KEY (fk_workflow) REFERENCES task8__Workflow(pk_workflow) ON DELETE CASCADE
);

-- State Transitions Table
CREATE TABLE task8__hasState (
    pk_hasState INT PRIMARY KEY AUTO_INCREMENT,
    action_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fk_required_permission INT,
    from_state INT,
    to_state INT NOT NULL,
    fk_request INT NULL,
    fk_workflow INT NOT NULL,
    FOREIGN KEY (fk_workflow) REFERENCES task8__Workflow(pk_workflow) ON DELETE CASCADE,
    FOREIGN KEY (from_state) REFERENCES task8__State(pk_state) ON DELETE CASCADE,
    FOREIGN KEY (to_state) REFERENCES task8__State(pk_state) ON DELETE CASCADE,
    FOREIGN KEY (fk_required_permission) REFERENCES task8__Permission(pk_permission),
    FOREIGN KEY (fk_request) REFERENCES task8__Request(pk_request) ON DELETE SET NULL
);

-- Dynamic Fields Table
CREATE TABLE task8__Field (
    pk_field INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    type ENUM('text', 'textarea', 'select', 'checkbox', 'radio', 'date', 'number', 'email') NOT NULL,
    is_required BOOLEAN DEFAULT FALSE,
    options JSON,
    field_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fk_workflow INT NOT NULL,
    FOREIGN KEY (fk_workflow) REFERENCES task8__Workflow(pk_workflow) ON DELETE CASCADE
);

-- Dynamic Data Table (Field Values)
CREATE TABLE task8__Data (
    pk_data INT PRIMARY KEY AUTO_INCREMENT,
    fk_request INT NOT NULL,
    fk_field INT NOT NULL,
    value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (fk_request) REFERENCES task8__Request(pk_request) ON DELETE CASCADE,
    FOREIGN KEY (fk_field) REFERENCES task8__Field(pk_field) ON DELETE CASCADE
);

-- Notifications Table
CREATE TABLE task8__Notification (
    pk_notification INT PRIMARY KEY AUTO_INCREMENT,
    fk_user INT NOT NULL,
    fk_request INT NOT NULL,
    fk_state INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fk_user) REFERENCES task8__User(pk_user) ON DELETE CASCADE,
    FOREIGN KEY (fk_request) REFERENCES task8__Request(pk_request) ON DELETE CASCADE,
    FOREIGN KEY (fk_state) REFERENCES task8__State(pk_state) ON DELETE CASCADE
);