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


--
-- Table structure for table `activity_log`
--

CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(100) DEFAULT NULL,
  `table_name` varchar(100) DEFAULT NULL,
  `record_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `activity_log`
--


--
-- 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`
--

INSERT INTO `customers` VALUES
(10,'Deepak Choudhary','deepaksc@gmail.com','9930314707','2026-04-01 04:35:28','B-806 Pinnacle CHS \r\nVasant Oscar \r\nLBS marg \r\nMulund west \r\nMumbai 400080'),
(11,'Anurag Golipkar','anurag.golipkar@gmail.com','9890083830','2026-04-01 08:37:17','Mumbai'),
(12,'Swati Indulkar','swati@adhyay.in','9820415915','2026-04-01 09:33:25','Mulund'),
(13,'Bhavin Sharma','Info@srishchem.com','8692017589','2026-04-01 09:41:58','B 39, Meghdhara Market Railway Station Road, Anjurphata, Bhiwandi, Dist.Thane, Maharashtra 421302'),
(14,'Raman Shreeram Shipping','raman@shreeramshipping.com','9137738563','2026-04-01 12:40:29','Thane'),
(15,'Jagdish Ahuja','info@japsandassociates.in','9323144390','2026-04-01 12:42:56','301, 3rd Floor, ARK 7, B Wing, Raghoba Shankar Cross Road, Behind American Watch Company, Thane West, Pin -400601'),
(16,'Subharao','subbarao@capitalconsulting.co.in','9820844965','2026-04-03 08:06:59','Delhi'),
(18,'Raghav Rao','thinkdirectindia@gmail.com','9819491489','2026-04-03 09:29:33','Thane'),
(19,'Nitesh Saxena','aritradigimedia@gmail.com','8591099253','2026-04-17 08:01:12','Address: FLAT-C-903, Niharika Absolute, Plot-A-10, Sect-39A , Kharghar, Kharghar (CT) Ta:PANVEL Di:RAIGAD 410210.'),
(20,'Gaurav Gupte','gaurav@g4i.in','9619571723','2026-04-22 07:23:06','Office No. 23, Bharat Industrial Premises, LBS Rd, Rajiv Gandhi Nagar, Bhandup West, Mumbai.'),
(21,'Prem Bhange','prem@alliancegroups.in','8080819009','2026-04-24 06:15:10','411, Jaydeep Emphasis,\r\nNear, LBS Rd, opp.\r\nPunjab National Bank,\r\nWagle Estate, Thane\r\nWest, Mumbai,\r\nMaharashtra 400604');

--
-- 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`
--

INSERT INTO `expenses` VALUES
(1,'Purchsae G Suite for srishchem.com monthly billing',1364.00,'2026-04-01',NULL,NULL);

--
-- 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),
(7,7,9,NULL,8000.00),
(8,4,7,'weaverbird maintenance',3000.00),
(9,3,5,NULL,5000.00),
(10,3,6,NULL,3000.00),
(11,8,10,'The knowledge Worker monthly maintenance',10000.00),
(14,11,11,'shreeramshipping.com   domain renewal',2000.00),
(19,14,14,'aritradigimedia.com',2500.00),
(20,15,15,'AMC for Loc india',4500.00),
(21,16,12,'japsandassociates.in Domain Renewal',1500.00),
(22,16,13,'japsandassociates.in (Hosting)',2500.00),
(23,17,8,'adhyayfoundation.org',1700.00),
(26,19,16,'gfalliance.net',2200.00),
(27,19,17,'gfalliance.net',8000.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`
--

INSERT INTO `invoices` VALUES
(3,10,8000.00,'2026-04-01 04:44:21','unpaid','2026-04-01','2026-04-01','paid',NULL),
(4,11,3000.00,'2026-04-01 09:21:17','unpaid','2026-04-01','2026-04-01','paid',NULL),
(7,13,8000.00,'2026-04-01 09:43:10','unpaid','2026-04-01','2026-04-01','paid',NULL),
(8,16,10000.00,'2026-04-03 08:08:22','unpaid','2026-04-01','2026-04-01','paid',NULL),
(11,14,2000.00,'2026-04-03 09:11:20','unpaid','2026-04-15','2026-04-29','paid',NULL),
(14,19,2500.00,'2026-04-17 08:02:45','unpaid','2026-04-17','2026-05-02','paid',NULL),
(15,20,4500.00,'2026-04-22 07:24:39','unpaid','2026-04-22','2026-04-22','paid',NULL),
(16,15,4000.00,'2026-04-23 13:04:45','unpaid','2026-04-23','2026-04-29','paid',NULL),
(17,12,1700.00,'2026-04-24 06:11:54','unpaid','2026-04-24','2026-04-29','paid',NULL),
(19,21,10200.00,'2026-04-24 06:18:41','unpaid','2026-04-24','2026-04-29','paid',NULL);

--
-- 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','Bank');

--
-- 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`
--

INSERT INTO `services` VALUES
(5,10,'AMC for thekidwhotravelled.com','amc','AMC',5000.00,'2026-04-01','2027-03-31','active',NULL,0.00),
(6,10,'Hosting for thekidwhotravelled.com','hosting','Hosting',3000.00,'2026-04-01','2027-03-31','active',NULL,0.00),
(7,11,'weaverbird maintenance','amc','weaverbird maintenance',3000.00,'2026-04-01','2027-03-31','active','2026-04-01',0.00),
(8,12,'adhyayfoundation.org','domain','adhyayfoundation.org',1700.00,'2026-04-07','2027-04-06','active','2026-04-24',1580.00),
(9,13,'G Suite Renewal','other','G Suite',8000.00,'2026-04-01','2027-04-01','active','2026-04-01',7000.00),
(10,16,'The knowledge Worker monthly maintenance','amc',NULL,10000.00,'2026-04-01','2026-05-01','active','2026-04-01',0.00),
(11,14,'shreeramshipping.com   domain renewal','domain',NULL,2000.00,'2026-04-15','2027-04-29','active','2026-04-15',1356.00),
(12,15,'japsandassociates.in Domain Renewal','domain',NULL,1500.00,'2026-04-15','2027-04-28','active','2026-04-23',884.00),
(13,15,'japsandassociates.in (Hosting)','hosting',NULL,2500.00,'2026-04-15','2027-04-28','active','2026-04-23',0.00),
(14,19,'aritradigimedia.com','hosting',NULL,2500.00,'2026-04-17','2027-05-02','active','2026-04-17',0.00),
(15,20,'AMC for Loc india','amc',NULL,4500.00,'2026-04-01','2027-04-01','active','2026-04-22',0.00),
(16,21,'gfalliance.net (Domain)','domain',NULL,2200.00,'2026-04-24','2027-04-29','active','2026-04-24',1669.00),
(17,21,'gfalliance.net (Hosting)','hosting',NULL,8000.00,'2026-04-24','2027-04-29','active','2026-04-24',0.00);


