--
-- Host: localhost    Database: digitalv_billing_db
-- ------------------------------------------------------


--
-- Table structure for table `bills`
--

CREATE TABLE `bills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `month` varchar(20) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `status` varchar(20) DEFAULT 'unpaid',
  `bill_date` date DEFAULT NULL,
  `previous_due` decimal(10,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) DEFAULT 0.00,
  `discount` decimal(10,2) DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `member_id` (`member_id`,`month`),
  CONSTRAINT `bills_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`),
  CONSTRAINT `fk_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `bills`
--

INSERT INTO `bills` VALUES
(14,6,'FY 2025',33120.00,'unpaid','2025-04-01',0.00,33120.00,0.00),
(17,1,'FY 2025',12000.00,'unpaid','2025-04-01',0.00,11000.00,1000.00),
(18,2,'FY 2025',12240.00,'unpaid','2025-04-01',0.00,11220.00,1020.00),
(19,5,'FY 2025',11280.00,'unpaid','2025-04-01',0.00,10340.00,940.00),
(20,8,'FY 2025',16560.00,'unpaid','2025-04-01',0.00,15180.00,1380.00),
(21,7,'FY 2025',13200.00,'unpaid','2025-04-01',0.00,12100.00,1100.00),
(22,11,'FY 2025',12000.00,'unpaid','2025-04-01',0.00,11000.00,1000.00),
(23,12,'FY 2025',13200.00,'unpaid','2025-04-01',0.00,12100.00,1100.00),
(24,14,'FY 2025',12240.00,'unpaid','2025-04-01',0.00,11220.00,1020.00),
(25,13,'FY 2025',15600.00,'unpaid','2025-04-01',0.00,14300.00,1300.00),
(26,16,'FY 2025',12220.00,'unpaid','2025-04-01',0.00,12220.00,0.00),
(27,15,'FY 2025',17940.00,'unpaid','2025-04-01',0.00,17940.00,0.00),
(28,17,'FY 2025',19536.00,'unpaid','2025-04-01',0.00,17910.00,1626.00),
(29,18,'FY 2025',13200.00,'unpaid','2025-04-01',0.00,12100.00,1100.00),
(30,19,'FY 2025',15600.00,'unpaid','2025-04-01',0.00,15600.00,0.00),
(31,20,'FY 2025',30108.00,'unpaid','2025-04-01',0.00,30108.00,0.00),
(32,21,'FY 2025',29760.00,'unpaid','2025-04-01',0.00,29760.00,0.00);

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `address` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `customers`
--


--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `expense_date` date DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `expenses`
--


--
-- Table structure for table `invoice_items`
--

CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `invoice_items`
--

INSERT INTO `invoice_items` VALUES
(1,2,2,'hosting',3500.00);

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `total` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` enum('paid','unpaid') DEFAULT 'unpaid',
  `invoice_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `payment_status` enum('pending','paid') DEFAULT 'pending',
  `invoice_no` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `invoices`
--


--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `method` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` VALUES
(1,7,8000.00,'2026-04-01','Bank'),
(2,4,3000.00,'2026-04-02','Bank'),
(3,8,10000.00,'2026-04-02','UPI'),
(4,14,2500.00,'2026-04-17','UPI'),
(5,3,8000.00,'2026-04-18','UPI'),
(6,15,4500.00,'2026-04-22','Bank'),
(7,16,4000.00,'2026-04-23','Bank'),
(8,17,1700.00,'2026-04-24','UPI'),
(9,19,10200.00,'2026-04-24','Bank'),
(10,11,2000.00,'2026-04-30','UPI');

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `service_name` varchar(255) DEFAULT NULL,
  `type` enum('domain','hosting','amc','other') DEFAULT NULL,
  `description` text DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `renewal_date` date DEFAULT NULL,
  `status` enum('active','expired') DEFAULT 'active',
  `last_billed` date DEFAULT NULL,
  `cost` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `services`
--



