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'),branch_id INT);
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);
CREATE TABLE donors(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150),mobile VARCHAR(20),address VARCHAR(200));
CREATE TABLE donations(id INT AUTO_INCREMENT PRIMARY KEY,donor_id INT,amount DECIMAL(12,2),donation_date DATE,remarks TEXT);
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);
CREATE TABLE ledger(id INT AUTO_INCREMENT PRIMARY KEY,type ENUM('income','expense'),amount DECIMAL(12,2),entry_date DATE,remarks TEXT);