auth_only_repair.sql

-- Use this only if your existing database already has the project tables,
-- but login does not work. It recreates only roles/users in the expected format.
USE library_db;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE roles (
    role_id INT NOT NULL AUTO_INCREMENT,
    role_name VARCHAR(80) NOT NULL UNIQUE,
    permissions TEXT NULL,
    PRIMARY KEY (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(120) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    roles_role_id INT NOT NULL,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_users_roles FOREIGN KEY (roles_role_id) REFERENCES roles(role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @bcrypt_password = '$2y$12$9/F4opinTy3afoosBZdUOe2WeI8IMu/rU6XajDLXQHd9f9gRn4uza';

INSERT INTO roles (role_id, role_name, permissions) VALUES
(1, 'ADMIN', 'FULL_ACCESS'),
(2, 'LIBRARIAN', 'LIBRARY_OPERATIONS'),
(3, 'MEMBER', 'MEMBER_ACCESS');

INSERT INTO users (username, email, password, roles_role_id) VALUES
('admin', 'admin@library.test', @bcrypt_password, 1),
('librarian', 'librarian@library.test', @bcrypt_password, 2),
('member', 'member@library.test', @bcrypt_password, 3);