--
-- Table structure for table `fines`
--

CREATE TABLE `fines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `installment_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `fine_date` date NOT NULL,
  `status` enum('pending','paid','waived') DEFAULT 'pending',
  `created_by` int(11) DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `installment_id` (`installment_id`),
  CONSTRAINT `fines_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`),
  CONSTRAINT `fines_ibfk_2` FOREIGN KEY (`installment_id`) REFERENCES `installments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `fines`
--


--
-- Table structure for table `installments`
--

CREATE TABLE `installments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_date` date NOT NULL,
  `installment_type` enum('weekly','monthly','quarterly','yearly','initial') NOT NULL,
  `payment_method` enum('cash','bank','mobile_banking') DEFAULT 'cash',
  `transaction_id` varchar(100) DEFAULT NULL,
  `status` enum('paid','pending','partial') DEFAULT 'pending',
  `shares` int(11) DEFAULT 0,
  `verified_by` int(11) DEFAULT NULL,
  `verification_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `payment_date` (`payment_date`),
  KEY `verified_by` (`verified_by`),
  KEY `idx_installments_member_status` (`member_id`,`status`),
  CONSTRAINT `installments_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `installments_ibfk_2` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`),
  CONSTRAINT `chk_amount_positive` CHECK (`amount` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `installments`
--


--
-- Table structure for table `investment_categories`
--

CREATE TABLE `investment_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `investment_categories`
--


--
-- Table structure for table `investment_returns`
--

CREATE TABLE `investment_returns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `investment_id` int(11) NOT NULL,
  `return_amount` decimal(15,2) NOT NULL,
  `return_date` date NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `investment_id` (`investment_id`),
  CONSTRAINT `investment_returns_ibfk_1` FOREIGN KEY (`investment_id`) REFERENCES `investments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `investment_returns`
--


--
-- Table structure for table `investments`
--

CREATE TABLE `investments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `investment_type` enum('agriculture','business','livestock','other') DEFAULT 'agriculture',
  `amount` decimal(15,2) NOT NULL,
  `invested_date` date NOT NULL,
  `expected_profit_percentage` decimal(5,2) DEFAULT NULL,
  `duration_months` int(11) DEFAULT NULL,
  `status` enum('active','completed','loss','pending') DEFAULT 'active',
  `manager_id` int(11) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `actual_profit` decimal(15,2) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `manager_id` (`manager_id`),
  KEY `investment_type` (`investment_type`),
  KEY `idx_investments_status_date` (`status`,`invested_date`),
  CONSTRAINT `investments_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `investment_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `investments_ibfk_2` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `investments`
--


--
-- Table structure for table `meeting_attendances`
--

CREATE TABLE `meeting_attendances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meeting_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `attended` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `meeting_id` (`meeting_id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `meeting_attendances_ibfk_1` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`id`),
  CONSTRAINT `meeting_attendances_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `meeting_attendances`
--


--
-- Table structure for table `meetings`
--

CREATE TABLE `meetings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meeting_date` date NOT NULL,
  `agenda` text DEFAULT NULL,
  `decisions` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `meetings_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `meetings`
--


--
-- Table structure for table `members`
--

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `nid_number` varchar(20) DEFAULT NULL,
  `nominee_name` varchar(100) DEFAULT NULL,
  `nominee_relation` varchar(50) DEFAULT NULL,
  `initial_shares` int(11) DEFAULT 0,
  `total_shares` int(11) DEFAULT 0,
  `total_investment` decimal(15,2) DEFAULT 0.00,
  `join_date` date NOT NULL,
  `status` enum('active','inactive','suspended') DEFAULT 'active',
  `deactivation_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `member_id` (`member_id`),
  KEY `email` (`email`),
  KEY `status` (`status`),
  KEY `nid_number` (`nid_number`),
  KEY `idx_members_join_status` (`join_date`,`status`),
  CONSTRAINT `chk_initial_shares` CHECK (`initial_shares` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `members`
--


--
-- Table structure for table `profit_distributions`
--

CREATE TABLE `profit_distributions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `investment_id` int(11) NOT NULL,
  `distribution_date` date NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `profit_per_share` decimal(10,4) DEFAULT NULL,
  `shares_at_time` int(11) DEFAULT NULL,
  `financial_year` year(4) DEFAULT NULL,
  `distribution_type` enum('interim','final','special') DEFAULT 'final',
  `approved_by` int(11) DEFAULT NULL,
  `approved_date` datetime DEFAULT NULL,
  `status` enum('pending','approved','distributed') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `investment_id` (`investment_id`),
  KEY `approved_by` (`approved_by`),
  KEY `financial_year` (`financial_year`),
  CONSTRAINT `profit_distributions_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `profit_distributions_ibfk_2` FOREIGN KEY (`investment_id`) REFERENCES `investments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `profit_distributions_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `profit_distributions`
--


--
-- Table structure for table `share_payouts`
--

CREATE TABLE `share_payouts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `total_shares` int(11) NOT NULL,
  `share_value` decimal(15,2) NOT NULL,
  `total_investment` decimal(15,2) NOT NULL,
  `total_installments` decimal(15,2) DEFAULT 0.00,
  `total_profit` decimal(15,2) DEFAULT 0.00,
  `total_fine` decimal(15,2) DEFAULT 0.00,
  `payout_amount` decimal(15,2) NOT NULL,
  `payout_method` enum('cash','bank','check','mobile_banking') NOT NULL,
  `transaction_details` text DEFAULT NULL,
  `processed_by` int(11) NOT NULL,
  `payout_date` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `processed_by` (`processed_by`),
  CONSTRAINT `share_payouts_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`),
  CONSTRAINT `share_payouts_ibfk_2` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `share_payouts`
--


--
-- Table structure for table `share_transfers`
--

CREATE TABLE `share_transfers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_member_id` int(11) NOT NULL,
  `to_member_id` int(11) DEFAULT NULL,
  `shares` int(11) NOT NULL,
  `transfer_date` date NOT NULL,
  `amount_per_share` decimal(10,2) DEFAULT 500.00,
  `total_amount` decimal(10,2) DEFAULT NULL,
  `transfer_fee` decimal(10,2) DEFAULT 0.00,
  `approval_status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_date` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `transfer_type` varchar(50) NOT NULL DEFAULT 'return',
  `reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `from_member_id` (`from_member_id`),
  KEY `to_member_id` (`to_member_id`),
  KEY `approved_by` (`approved_by`),
  CONSTRAINT `share_transfers_ibfk_1` FOREIGN KEY (`from_member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE,
  CONSTRAINT `share_transfers_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `share_transfers`
--


--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `description` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `system_settings`
--


--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `role` enum('admin','management','accountant','viewer') DEFAULT 'management',
  `status` enum('active','inactive') DEFAULT 'active',
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `email`, `full_name`, `role`, `status`, `last_login`, `created_at`) VALUES ('1', 'admin', '$2y$10$ptTHRtTG3s20WmJJTdmVAe6gJIfe0.8c3q14N23JffhT/WU5FlV/O', 'admin@cooperative.com', 'System Administrator', 'admin', 'active', '2025-09-24 22:52:15', '2025-09-17 19:15:59');
INSERT INTO `users` (`id`, `username`, `password`, `email`, `full_name`, `role`, `status`, `last_login`, `created_at`) VALUES ('2', 'juba', '$2y$10$NDJ1wRK0lReNBAZgP56DcuH3levnFgRAWjQeUbmGhTd2v16F9GXvG', 'juba@nccbank.com.bd', 'Juba Das', 'accountant', 'active', '2025-09-24 22:45:06', '2025-09-22 00:31:00');