/home/awneajlw/.trash/public/images/database.sql
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
user_type ENUM('Admin', 'User') NOT NULL,
role VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
father_name VARCHAR(50),
contact_no VARCHAR(20),
email VARCHAR(100) NOT NULL UNIQUE,
cnic VARCHAR(20) UNIQUE,
is_admin BOOLEAN DEFAULT FALSE,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE permissions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(100) NOT NULL,
can_view BOOLEAN DEFAULT FALSE,
can_add BOOLEAN DEFAULT FALSE,
can_edit BOOLEAN DEFAULT FALSE,
can_delete BOOLEAN DEFAULT FALSE,
can_authorize BOOLEAN DEFAULT FALSE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE user_actions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
action VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
