CREATE DATABASE IF NOT EXISTS siprt CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE siprt;

CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role_id INT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
);

INSERT INTO roles(name,description) VALUES
('Administrator','Akses penuh dan pengelolaan akun'),
('Ketua RT','Peran contoh; hak akses modul dapat dikonfigurasi'),
('Sekretaris','Peran contoh; hak akses modul dapat dikonfigurasi'),
('Bendahara','Peran contoh; hak akses modul dapat dikonfigurasi'),
('Operator','Peran contoh; hak akses modul dapat dikonfigurasi');