-- Mirkashi Para Humanitarian Organization V1 schema
-- MySQL 8.0+ / compatible MariaDB. Import into a NEW database.
SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  full_name VARCHAR(190) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(32) NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','inactive','locked') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_users_email (email), KEY idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_key VARCHAR(64) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  PRIMARY KEY (id), UNIQUE KEY uq_roles_key (role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  permission_key VARCHAR(100) NOT NULL,
  description VARCHAR(255) NOT NULL,
  PRIMARY KEY (id), UNIQUE KEY uq_permissions_key (permission_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id),
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_code VARCHAR(40) NOT NULL,
  full_name VARCHAR(190) NOT NULL,
  phone VARCHAR(32) NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  joined_on DATE NULL,
  status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  private_notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_members_code (member_code),
  KEY idx_members_phone (phone), KEY idx_members_status (status),
  CONSTRAINT fk_members_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS projects (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_code VARCHAR(40) NOT NULL,
  name VARCHAR(190) NOT NULL,
  slug VARCHAR(220) NOT NULL,
  summary TEXT NULL,
  description LONGTEXT NULL,
  founder_member_id BIGINT UNSIGNED NULL,
  manager_user_id BIGINT UNSIGNED NULL,
  target_budget DECIMAL(14,2) NULL,
  visibility ENUM('private','summary_public','full_public') NOT NULL DEFAULT 'summary_public',
  status ENUM('draft','active','paused','completed','archived') NOT NULL DEFAULT 'draft',
  start_date DATE NULL,
  end_date DATE NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_projects_code (project_code), UNIQUE KEY uq_projects_slug (slug),
  KEY idx_projects_status_visibility (status, visibility),
  CONSTRAINT fk_projects_founder FOREIGN KEY (founder_member_id) REFERENCES members(id) ON DELETE SET NULL,
  CONSTRAINT fk_projects_manager FOREIGN KEY (manager_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_projects_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  role_id BIGINT UNSIGNED NULL,
  project_title VARCHAR(120) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  joined_at DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_project_member_user (project_id, member_id, user_id),
  KEY idx_project_members_user (user_id, project_id, status),
  CONSTRAINT fk_pm_project FOREIGN KEY (project_id) REFERENCES projects(id),
  CONSTRAINT fk_pm_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL,
  CONSTRAINT fk_pm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_pm_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fund_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  account_name VARCHAR(150) NOT NULL,
  account_type ENUM('cash','bank','mobile_wallet','gateway_clearing','other') NOT NULL,
  provider_name VARCHAR(100) NULL,
  account_reference_masked VARCHAR(100) NULL,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_fund_accounts_project (project_id, is_active),
  CONSTRAINT fk_fund_accounts_project FOREIGN KEY (project_id) REFERENCES projects(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS budgets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  budget_year SMALLINT UNSIGNED NOT NULL,
  category VARCHAR(120) NOT NULL,
  approved_amount DECIMAL(14,2) NOT NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_budget_project_year_category (project_id, budget_year, category),
  CONSTRAINT fk_budgets_project FOREIGN KEY (project_id) REFERENCES projects(id),
  CONSTRAINT fk_budgets_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS donations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  donor_member_id BIGINT UNSIGNED NULL,
  donor_name VARCHAR(190) NULL,
  donor_phone VARCHAR(32) NULL,
  donor_email VARCHAR(190) NULL,
  anonymous_publicly TINYINT(1) NOT NULL DEFAULT 0,
  amount DECIMAL(14,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  purpose VARCHAR(190) NULL,
  status ENUM('initiated','pending_verification','confirmed','rejected','refunded') NOT NULL DEFAULT 'initiated',
  public_reference VARCHAR(64) NOT NULL,
  confirmed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_donations_public_ref (public_reference),
  KEY idx_donations_project_status_date (project_id, status, created_at),
  CONSTRAINT fk_donations_project FOREIGN KEY (project_id) REFERENCES projects(id),
  CONSTRAINT fk_donations_member FOREIGN KEY (donor_member_id) REFERENCES members(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  donation_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(40) NOT NULL,
  merchant_order_id VARCHAR(100) NOT NULL,
  provider_transaction_id VARCHAR(150) NULL,
  amount DECIMAL(14,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  status ENUM('created','pending','success','failed','cancelled','verification_required','refunded') NOT NULL DEFAULT 'created',
  idempotency_key VARCHAR(100) NOT NULL,
  raw_status VARCHAR(100) NULL,
  verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_payment_order (merchant_order_id),
  UNIQUE KEY uq_payment_idempotency (idempotency_key),
  UNIQUE KEY uq_provider_transaction (provider, provider_transaction_id),
  KEY idx_payment_donation_status (donation_id, status),
  CONSTRAINT fk_payment_donation FOREIGN KEY (donation_id) REFERENCES donations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_webhooks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider VARCHAR(40) NOT NULL,
  event_key VARCHAR(190) NULL,
  merchant_order_id VARCHAR(100) NULL,
  provider_transaction_id VARCHAR(150) NULL,
  signature_valid TINYINT(1) NULL,
  processing_status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
  payload_sha256 CHAR(64) NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  PRIMARY KEY (id), UNIQUE KEY uq_webhook_provider_event (provider, event_key),
  KEY idx_webhook_order (merchant_order_id, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id BIGINT UNSIGNED NOT NULL,
  fund_account_id BIGINT UNSIGNED NOT NULL,
  transaction_code VARCHAR(50) NOT NULL,
  transaction_type ENUM('income','expense','transfer_in','transfer_out','adjustment') NOT NULL,
  category VARCHAR(120) NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  transaction_date DATE NOT NULL,
  description TEXT NOT NULL,
  source_type VARCHAR(50) NULL,
  source_id BIGINT UNSIGNED NULL,
  transfer_group_id CHAR(36) NULL,
  status ENUM('draft','pending_approval','approved','rejected','reversed') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  reversed_transaction_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id), UNIQUE KEY uq_transactions_code (transaction_code),
  KEY idx_transactions_project_date_status (project_id, transaction_date, status),
  KEY idx_transactions_fund_date (fund_account_id, transaction_date),
  KEY idx_transactions_source (source_type, source_id),
  CONSTRAINT fk_transactions_project FOREIGN KEY (project_id) REFERENCES projects(id),
  CONSTRAINT fk_transactions_fund FOREIGN KEY (fund_account_id) REFERENCES fund_accounts(id),
  CONSTRAINT fk_transactions_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_transactions_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_transactions_reversed FOREIGN KEY (reversed_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
  CONSTRAINT chk_transaction_amount CHECK (amount > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transaction_approvals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  transaction_id BIGINT UNSIGNED NOT NULL,
  action ENUM('submitted','approved','rejected','reversed') NOT NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  comments TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_approval_transaction (transaction_id, created_at),
  CONSTRAINT fk_approval_transaction FOREIGN KEY (transaction_id) REFERENCES transactions(id),
  CONSTRAINT fk_approval_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  receipt_number VARCHAR(60) NOT NULL,
  transaction_id BIGINT UNSIGNED NULL,
  donation_id BIGINT UNSIGNED NULL,
  issued_to VARCHAR(190) NULL,
  amount DECIMAL(14,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'BDT',
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('issued','void') NOT NULL DEFAULT 'issued',
  PRIMARY KEY (id), UNIQUE KEY uq_receipts_number (receipt_number),
  UNIQUE KEY uq_receipts_transaction (transaction_id),
  UNIQUE KEY uq_receipts_donation (donation_id),
  CONSTRAINT fk_receipt_transaction FOREIGN KEY (transaction_id) REFERENCES transactions(id),
  CONSTRAINT fk_receipt_donation FOREIGN KEY (donation_id) REFERENCES donations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_type VARCHAR(50) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  storage_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  sha256 CHAR(64) NOT NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_attachments_entity (entity_type, entity_id),
  CONSTRAINT fk_attachments_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(60) NULL,
  entity_id BIGINT UNSIGNED NULL,
  metadata_json JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id), KEY idx_audit_user_date (user_id, created_at), KEY idx_audit_entity (entity_type, entity_id, created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO roles (role_key, display_name, description) VALUES
('super_admin','সুপার অ্যাডমিন','সিস্টেমের পূর্ণ নিয়ন্ত্রণ'),
('central_officer','কেন্দ্রীয় কর্তৃপক্ষ','কেন্দ্রীয় পর্যবেক্ষণ ও অনুমোদিত প্রতিবেদন'),
('treasurer','কোষাধ্যক্ষ','আর্থিক যাচাই ও ব্যয়ের অনুমোদন'),
('project_manager','প্রকল্প পরিচালক','নিজ প্রকল্প পরিচালনা ও লেনদেন জমা'),
('accountant','হিসাব সহকারী','অনুমোদিত প্রকল্পে আর্থিক এন্ট্রি'),
('auditor','নিরীক্ষক','পঠনযোগ্য নিরীক্ষা প্রবেশাধিকার'),
('member','সদস্য','নিজের সদস্য তথ্য ও প্রকাশযোগ্য তথ্য');

INSERT IGNORE INTO permissions (permission_key, description) VALUES
('dashboard.view','ড্যাশবোর্ড দেখা'),
('projects.view','অনুমোদিত প্রকল্প দেখা'),
('projects.manage','প্রকল্প তৈরি ও সম্পাদনা'),
('members.view','সদস্য তালিকা দেখা'),
('members.manage','সদস্য ব্যবস্থাপনা'),
('transactions.view','অনুমোদিত লেনদেন দেখা'),
('transactions.create','লেনদেন এন্ট্রি তৈরি'),
('transactions.submit','লেনদেন অনুমোদনের জন্য জমা'),
('transactions.approve','লেনদেন অনুমোদন বা প্রত্যাখ্যান'),
('reports.private','অভ্যন্তরীণ আর্থিক প্রতিবেদন'),
('reports.public','প্রকাশযোগ্য প্রতিবেদন'),
('users.manage','ব্যবহারকারী ও ভূমিকা পরিচালনা'),
('audit.view','অডিট লগ দেখা'),
('payments.reconcile','পেমেন্ট মিলানো ও যাচাই');

-- Role-permission assignments
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.role_key = 'super_admin';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.role_key = 'central_officer'
AND p.permission_key IN ('dashboard.view','projects.view','members.view','transactions.view','reports.private','reports.public','audit.view');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.role_key = 'treasurer'
AND p.permission_key IN ('dashboard.view','projects.view','members.view','transactions.view','transactions.approve','reports.private','reports.public','payments.reconcile');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.role_key = 'project_manager'
AND p.permission_key IN ('dashboard.view','projects.view','members.view','transactions.view','transactions.create','transactions.submit','reports.public');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.role_key = 'accountant'
AND p.permission_key IN ('dashboard.view','projects.view','members.view','transactions.view','transactions.create','transactions.submit');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.role_key = 'auditor'
AND p.permission_key IN ('dashboard.view','projects.view','members.view','transactions.view','reports.private','reports.public','audit.view','payments.reconcile');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.role_key = 'member'
AND p.permission_key IN ('dashboard.view','projects.view','reports.public');

SET FOREIGN_KEY_CHECKS = 1;