CREATE DATABASE ngo_db;
USE ngo_db;

CREATE TABLE branches(
id INT AUTO_INCREMENT PRIMARY KEY,
branch_name VARCHAR(150),
location VARCHAR(200)
);

CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) UNIQUE,
password VARCHAR(255),
role ENUM('admin','manager','user') DEFAULT 'user',
branch_id INT,
FOREIGN KEY (branch_id) REFERENCES branches(id)
);

CREATE TABLE beneficiaries(
id INT AUTO_INCREMENT PRIMARY KEY,
branch_id INT,
name VARCHAR(150),
cnic VARCHAR(15) UNIQUE,
mobile VARCHAR(20),
location VARCHAR(200),
family INT,
source VARCHAR(150),
FOREIGN KEY (branch_id) REFERENCES branches(id)
);

CREATE TABLE distributions(
id INT AUTO_INCREMENT PRIMARY KEY,
beneficiary_id INT,
branch_id INT,
distribution_type VARCHAR(150),
amount DECIMAL(12,2),
distribution_date DATE,
remarks TEXT,
FOREIGN KEY (beneficiary_id) REFERENCES beneficiaries(id),
FOREIGN KEY (branch_id) REFERENCES branches(id)
);