-- phpMyAdmin SQL Dump
-- Lead Management System (LMS) Database Schema
-- Database: `new_dashboard_db`

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------
-- Table structure for table `tbl_branches`
-- --------------------------------------------------------

CREATE TABLE `tbl_branches` (
  `branch_id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_name` varchar(255) NOT NULL,
  `branch_code` varchar(50) NOT NULL,
  `branch_address` text DEFAULT NULL,
  `branch_city` varchar(100) DEFAULT NULL,
  `branch_state` varchar(100) DEFAULT NULL,
  `branch_pincode` varchar(10) DEFAULT NULL,
  `branch_phone` varchar(20) DEFAULT NULL,
  `branch_email` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`branch_id`),
  UNIQUE KEY `branch_code` (`branch_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Sample branches
INSERT INTO `tbl_branches` (`branch_id`, `branch_name`, `branch_code`, `branch_address`, `branch_city`, `branch_state`, `branch_pincode`, `branch_phone`, `branch_email`, `status`) VALUES
(1, 'Mumbai Main Branch', 'MUM001', 'Nariman Point', 'Mumbai', 'Maharashtra', '400021', '022-12345678', 'mumbai@bank.com', 'Active'),
(2, 'Delhi Branch', 'DEL001', 'Connaught Place', 'New Delhi', 'Delhi', '110001', '011-12345678', 'delhi@bank.com', 'Active'),
(3, 'Bangalore Branch', 'BLR001', 'MG Road', 'Bangalore', 'Karnataka', '560001', '080-12345678', 'bangalore@bank.com', 'Active'),
(4, 'Chennai Branch', 'CHN001', 'Anna Salai', 'Chennai', 'Tamil Nadu', '600002', '044-12345678', 'chennai@bank.com', 'Active'),
(5, 'Kolkata Branch', 'KOL001', 'Park Street', 'Kolkata', 'West Bengal', '700016', '033-12345678', 'kolkata@bank.com', 'Active');

-- --------------------------------------------------------
-- Update table structure for table `tbl_admin`
-- --------------------------------------------------------

ALTER TABLE `tbl_admin` 
ADD COLUMN `branch_id` int(11) DEFAULT NULL AFTER `mobile`,
ADD COLUMN `role` enum('Admin','Manager','Executive') DEFAULT 'Executive' AFTER `branch_id`,
ADD COLUMN `status` enum('Active','Inactive') DEFAULT 'Active' AFTER `role`,
ADD KEY `fk_admin_branch` (`branch_id`),
ADD CONSTRAINT `fk_admin_branch` FOREIGN KEY (`branch_id`) REFERENCES `tbl_branches` (`branch_id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Update existing admin with branch and role
UPDATE `tbl_admin` SET `branch_id` = 1, `role` = 'Admin', `status` = 'Active' WHERE `admin_id` = 1;

-- Sample executives
INSERT INTO `tbl_admin` (`name`, `email`, `mobile`, `password`, `branch_id`, `role`, `status`) VALUES
('Rajesh Kumar', 'rajesh.kumar@bank.com', '9876543210', '$2y$10$xpLugmmcgrcqszb4eQiA/e0Ox/x1wxpiuT9AMGHJBDKSb0iQyM.b6', 1, 'Executive', 'Active'),
('Priya Sharma', 'priya.sharma@bank.com', '9876543211', '$2y$10$xpLugmmcgrcqszb4eQiA/e0Ox/x1wxpiuT9AMGHJBDKSb0iQyM.b6', 1, 'Executive', 'Active'),
('Amit Patel', 'amit.patel@bank.com', '9876543212', '$2y$10$xpLugmmcgrcqszb4eQiA/e0Ox/x1wxpiuT9AMGHJBDKSb0iQyM.b6', 2, 'Manager', 'Active'),
('Sneha Reddy', 'sneha.reddy@bank.com', '9876543213', '$2y$10$xpLugmmcgrcqszb4eQiA/e0Ox/x1wxpiuT9AMGHJBDKSb0iQyM.b6', 3, 'Executive', 'Active');

-- --------------------------------------------------------
-- Table structure for table `tbl_products`
-- --------------------------------------------------------

CREATE TABLE `tbl_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `product_code` varchar(50) NOT NULL,
  `product_description` text DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `product_code` (`product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert products
INSERT INTO `tbl_products` (`product_id`, `product_name`, `product_code`, `product_description`, `status`) VALUES
(1, 'Home Loans', 'HL', 'Home Loan Products', 'Active'),
(2, 'Car Loans', 'CL', 'Car Loan Products', 'Active'),
(3, 'Two Wheeler Loans', 'TWL', 'Two Wheeler Loan Products', 'Active'),
(4, 'Fixed Deposits', 'FD', 'Fixed Deposit Products', 'Active'),
(5, 'Savings Account', 'SA', 'Savings Account Products', 'Active');

-- --------------------------------------------------------
-- Table structure for table `tbl_leads`
-- --------------------------------------------------------

CREATE TABLE `tbl_leads` (
  `lead_id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_number` varchar(50) NOT NULL,
  `branch_id` int(11) NOT NULL,
  `executive_id` int(11) NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `customer_email` varchar(255) DEFAULT NULL,
  `customer_mobile` varchar(20) NOT NULL,
  `is_existing_customer` enum('Yes','No') DEFAULT 'No',
  `account_number` varchar(50) DEFAULT NULL,
  `relationship_number` varchar(50) DEFAULT NULL,
  `product_id` int(11) NOT NULL,
  `lead_status` enum('New Lead','Follow-up','Converted','Lost to Competition','Plan Dropped') DEFAULT 'New Lead',
  `next_followup_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`lead_id`),
  UNIQUE KEY `lead_number` (`lead_number`),
  KEY `fk_lead_branch` (`branch_id`),
  KEY `fk_lead_executive` (`executive_id`),
  KEY `fk_lead_product` (`product_id`),
  KEY `idx_lead_status` (`lead_status`),
  KEY `idx_next_followup` (`next_followup_date`),
  CONSTRAINT `fk_lead_branch` FOREIGN KEY (`branch_id`) REFERENCES `tbl_branches` (`branch_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_lead_executive` FOREIGN KEY (`executive_id`) REFERENCES `tbl_admin` (`admin_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_lead_product` FOREIGN KEY (`product_id`) REFERENCES `tbl_products` (`product_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `tbl_lead_products`
-- --------------------------------------------------------

CREATE TABLE `tbl_lead_products` (
  `lead_product_id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  
  -- Common fields for all products
  `remarks` text DEFAULT NULL,
  
  -- Home Loan specific fields
  `property_value` decimal(15,2) DEFAULT NULL COMMENT 'In Lakhs',
  `loan_amount` decimal(15,2) DEFAULT NULL COMMENT 'In Lakhs',
  `income_source` enum('Salaried','Business','Self Employed') DEFAULT NULL,
  `expected_purchase_month` varchar(20) DEFAULT NULL,
  `expected_purchase_year` varchar(4) DEFAULT NULL,
  `property_identified` enum('Yes','No') DEFAULT NULL,
  `property_address` text DEFAULT NULL,
  
  -- Car Loan specific fields
  `car_value` decimal(15,2) DEFAULT NULL COMMENT 'In Lakhs',
  `car_make` varchar(100) DEFAULT NULL,
  `car_grade` varchar(100) DEFAULT NULL,
  
  -- Two Wheeler specific fields
  `two_wheeler_value` decimal(15,2) DEFAULT NULL COMMENT 'In Thousands/Lakhs',
  `two_wheeler_make` varchar(100) DEFAULT NULL,
  `two_wheeler_grade` varchar(100) DEFAULT NULL,
  
  -- Fixed Deposit specific fields
  `deposit_amount` decimal(15,2) DEFAULT NULL COMMENT 'In Thousands/Lakhs',
  `tenure_days` int(11) DEFAULT NULL,
  `interest_rate` decimal(5,2) DEFAULT NULL,
  `maturity_value` decimal(15,2) DEFAULT NULL,
  `creation_month` varchar(20) DEFAULT NULL,
  `creation_year` varchar(4) DEFAULT NULL,
  
  -- Savings Account specific fields
  `account_creation_month` varchar(20) DEFAULT NULL,
  `account_creation_year` varchar(4) DEFAULT NULL,
  
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`lead_product_id`),
  KEY `fk_leadprod_lead` (`lead_id`),
  KEY `fk_leadprod_product` (`product_id`),
  CONSTRAINT `fk_leadprod_lead` FOREIGN KEY (`lead_id`) REFERENCES `tbl_leads` (`lead_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_leadprod_product` FOREIGN KEY (`product_id`) REFERENCES `tbl_products` (`product_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `tbl_lead_followups`
-- --------------------------------------------------------

CREATE TABLE `tbl_lead_followups` (
  `followup_id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `followup_date` datetime NOT NULL DEFAULT current_timestamp(),
  `followup_by` int(11) NOT NULL,
  `previous_status` enum('New Lead','Follow-up','Converted','Lost to Competition','Plan Dropped') NOT NULL,
  `updated_status` enum('New Lead','Follow-up','Converted','Lost to Competition','Plan Dropped') NOT NULL,
  `followup_notes` text NOT NULL,
  `next_followup_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`followup_id`),
  KEY `fk_followup_lead` (`lead_id`),
  KEY `fk_followup_user` (`followup_by`),
  KEY `idx_followup_date` (`followup_date`),
  CONSTRAINT `fk_followup_lead` FOREIGN KEY (`lead_id`) REFERENCES `tbl_leads` (`lead_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_followup_user` FOREIGN KEY (`followup_by`) REFERENCES `tbl_admin` (`admin_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Create view for lead listing with all details
-- --------------------------------------------------------

CREATE OR REPLACE VIEW `vw_leads_list` AS
SELECT 
    l.lead_id,
    l.lead_number,
    l.customer_name,
    l.customer_email,
    l.customer_mobile,
    l.is_existing_customer,
    l.account_number,
    l.relationship_number,
    l.lead_status,
    l.next_followup_date,
    l.created_at,
    l.updated_at,
    b.branch_name,
    b.branch_code,
    a.name as executive_name,
    a.email as executive_email,
    a.role as executive_role,
    p.product_name,
    p.product_code,
    lp.loan_amount,
    lp.property_value,
    lp.car_value,
    lp.two_wheeler_value,
    lp.deposit_amount,
    lp.remarks
FROM tbl_leads l
INNER JOIN tbl_branches b ON l.branch_id = b.branch_id
INNER JOIN tbl_admin a ON l.executive_id = a.admin_id
INNER JOIN tbl_products p ON l.product_id = p.product_id
LEFT JOIN tbl_lead_products lp ON l.lead_id = lp.lead_id;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
