-- Osama Electric Backup
-- Generated: 2025-12-25 09:11:43



CREATE TABLE `amperes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `amperes` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `audit_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` mediumtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `audit_log` VALUES ('1','1','User management: edit - test','2025-12-24 06:30:10');
INSERT INTO `audit_log` VALUES ('2','1','Security: Updated perms for osama','2025-12-24 06:34:20');
INSERT INTO `audit_log` VALUES ('3','3','Login: User logged into the system','2025-12-24 06:37:30');
INSERT INTO `audit_log` VALUES ('4','1','Login: User logged into the system','2025-12-24 06:38:00');
INSERT INTO `audit_log` VALUES ('5','3','Login: User logged into the system','2025-12-24 06:39:39');
INSERT INTO `audit_log` VALUES ('6','3','Login: User logged into the system','2025-12-24 06:40:21');
INSERT INTO `audit_log` VALUES ('7','3','Security: Updated perms for test','2025-12-24 06:41:13');
INSERT INTO `audit_log` VALUES ('8','4','Login: User logged into the system','2025-12-24 06:41:26');
INSERT INTO `audit_log` VALUES ('9','3','Login: User authorized via professional portal','2025-12-24 06:44:48');
INSERT INTO `audit_log` VALUES ('10','3','Linked Meter 565852 to Customer ID 8','2025-12-24 06:57:34');
INSERT INTO `audit_log` VALUES ('11','3','Updated Meter 56263 (ID: 3)','2025-12-24 06:59:52');
INSERT INTO `audit_log` VALUES ('12','3','Updated customer: ابراهيم (ID: 7)','2025-12-24 07:20:15');
INSERT INTO `audit_log` VALUES ('13','3','Updated customer: basel (ID: 3)','2025-12-24 07:20:27');
INSERT INTO `audit_log` VALUES ('14','3','Set prices for period 12/2025: kWh=0.3, Amp=30','2025-12-24 07:25:17');
INSERT INTO `audit_log` VALUES ('15','3','Set prices for period 11/2025: kWh=0.29, Amp=29','2025-12-24 07:30:29');
INSERT INTO `audit_log` VALUES ('16','3','Generated 2 bills for 11/2025','2025-12-24 07:35:31');
INSERT INTO `audit_log` VALUES ('17','3','Deleted single bill ID: 1','2025-12-24 07:39:33');
INSERT INTO `audit_log` VALUES ('18','3','Paid bill ID: 4','2025-12-24 07:39:40');
INSERT INTO `audit_log` VALUES ('19','3','Paid bill ID: 3','2025-12-24 07:39:45');
INSERT INTO `audit_log` VALUES ('20','3','Paid bill ID: 3','2025-12-24 07:39:54');
INSERT INTO `audit_log` VALUES ('21','3','Paid bill ID: 3','2025-12-24 07:42:09');
INSERT INTO `audit_log` VALUES ('22','3','Paid bill ID: 3','2025-12-24 07:44:06');
INSERT INTO `audit_log` VALUES ('23','3','Deleted customer ID: 8','2025-12-24 07:48:12');
INSERT INTO `audit_log` VALUES ('24','3','Deleted customer ID: 7','2025-12-24 07:48:15');
INSERT INTO `audit_log` VALUES ('25','3','Deleted customer ID: 3','2025-12-24 07:48:18');
INSERT INTO `audit_log` VALUES ('26','3','Deleted customer ID: 2','2025-12-24 07:48:21');
INSERT INTO `audit_log` VALUES ('27','3','Added new customer: باسل (5)','2025-12-24 07:48:48');
INSERT INTO `audit_log` VALUES ('28','3','Added new customer: مومن (4)','2025-12-24 07:55:52');
INSERT INTO `audit_log` VALUES ('29','3','Updated customer: باسل (ID: 9)','2025-12-24 07:56:50');
INSERT INTO `audit_log` VALUES ('30','3','Paid bill ID: 6','2025-12-24 08:06:53');
INSERT INTO `audit_log` VALUES ('31','3','Reverted bill to unpaid ID: 6','2025-12-24 08:07:00');
INSERT INTO `audit_log` VALUES ('32','3','Updated customer: مومن (ID: 10) with fee: 10','2025-12-24 08:31:50');
INSERT INTO `audit_log` VALUES ('33','3','Added new customer: اسامة (10) with fee: 15','2025-12-24 08:32:43');
INSERT INTO `audit_log` VALUES ('34','3','Added new customer: احمد (55) with fee: 18','2025-12-24 08:33:58');
INSERT INTO `audit_log` VALUES ('35','3','Linked New Meter 234524 to Customer 12','2025-12-24 08:34:31');
INSERT INTO `audit_log` VALUES ('36','3','Added new customer: خالد (14) with fee: 15','2025-12-24 08:35:48');
INSERT INTO `audit_log` VALUES ('37','3','Updated customer: خالد (ID: 13) with fee: 15.00','2025-12-24 08:36:01');
INSERT INTO `audit_log` VALUES ('38','3','Generated 3 bills for 12/2025','2025-12-24 08:55:07');
INSERT INTO `audit_log` VALUES ('39','3','Updated customer: خالد (ID: 13) with fee: 15.00','2025-12-24 09:19:07');
INSERT INTO `audit_log` VALUES ('40','3','Updated customer: باسل (ID: 9) with fee: 20','2025-12-24 09:19:27');
INSERT INTO `audit_log` VALUES ('41','3','Login: User authorized via professional portal','2025-12-24 09:57:53');
INSERT INTO `audit_log` VALUES ('42','3','Updated customer: خالد (ID: 13) with fee: 15.00','2025-12-24 10:09:19');
INSERT INTO `audit_log` VALUES ('43','3','Login: User authorized via professional portal','2025-12-24 10:13:18');
INSERT INTO `audit_log` VALUES ('44','3','Updated customer: خالد (ID: 13) with fee: 0','2025-12-24 10:27:45');
INSERT INTO `audit_log` VALUES ('45','3','Added new customer: علاء ميعاري (34) with fee: 0','2025-12-24 10:33:44');
INSERT INTO `audit_log` VALUES ('46','3','Login: User authorized via professional portal','2025-12-24 11:09:04');
INSERT INTO `audit_log` VALUES ('47','3','Updated customer: بيبي (ID: 15)','2025-12-24 11:14:59');
INSERT INTO `audit_log` VALUES ('48','3','Login: User authorized via professional portal','2025-12-24 12:36:09');
INSERT INTO `audit_log` VALUES ('49','3','Login: User authorized via professional portal','2025-12-24 12:38:25');
INSERT INTO `audit_log` VALUES ('50','3','Security: Updated perms for test','2025-12-24 12:40:32');
INSERT INTO `audit_log` VALUES ('51','4','Login: User authorized via professional portal','2025-12-24 12:42:30');
INSERT INTO `audit_log` VALUES ('52',NULL,'Security: Updated perms for ali','2025-12-24 14:18:34');
INSERT INTO `audit_log` VALUES ('53',NULL,'Security: Updated perms for test','2025-12-24 14:19:20');
INSERT INTO `audit_log` VALUES ('54','5','Login: User authorized via professional portal','2025-12-24 14:21:13');
INSERT INTO `audit_log` VALUES ('55','5','Deleted customer ID: 15','2025-12-24 14:21:53');
INSERT INTO `audit_log` VALUES ('56','5','Deleted customer ID: 14','2025-12-24 14:22:00');
INSERT INTO `audit_log` VALUES ('57','3','Login: User authorized via professional portal','2025-12-24 14:22:28');
INSERT INTO `audit_log` VALUES ('58','3','Security: Updated perms for ali','2025-12-24 14:23:08');
INSERT INTO `audit_log` VALUES ('59','3','Login: User authorized via professional portal','2025-12-24 14:33:56');
INSERT INTO `audit_log` VALUES ('60','3','Security: Updated perms for ali','2025-12-24 14:35:01');
INSERT INTO `audit_log` VALUES ('61','5','Login: User authorized via professional portal','2025-12-24 14:37:26');
INSERT INTO `audit_log` VALUES ('62','3','Login: User authorized via professional portal','2025-12-24 14:45:34');
INSERT INTO `audit_log` VALUES ('63','3','Login: User authorized via professional portal','2025-12-24 17:52:43');
INSERT INTO `audit_log` VALUES ('64','3','Set prices for period 12/2025: kWh=32850 LBP, Amp=30 USD, Exchange=90000','2025-12-24 17:59:41');
INSERT INTO `audit_log` VALUES ('65','3','Login: User authorized via professional portal','2025-12-25 03:35:57');


CREATE TABLE `bills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bill_number` varchar(50) DEFAULT NULL,
  `customer_id` int(11) NOT NULL,
  `type` enum('meter','ampere') DEFAULT 'meter',
  `month` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `consumption` decimal(10,2) DEFAULT NULL,
  `amount_usd` decimal(10,2) DEFAULT NULL,
  `amount_lbp` decimal(15,2) DEFAULT NULL,
  `exchange_rate` decimal(10,2) DEFAULT NULL,
  `status` enum('paid','unpaid') DEFAULT 'unpaid',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `bills` VALUES ('36','B-202512-55','12','meter','12','2025',NULL,'19.46',NULL,NULL,'paid','2025-12-25 04:08:27');
INSERT INTO `bills` VALUES ('37','B-202512-14','13','meter','12','2025',NULL,'300.00',NULL,NULL,'paid','2025-12-25 04:08:27');
INSERT INTO `bills` VALUES ('35','B-202512-10','11','meter','12','2025',NULL,'36.54',NULL,NULL,'unpaid','2025-12-25 04:08:27');
INSERT INTO `bills` VALUES ('34','B-202512-4','10','meter','12','2025',NULL,'160.00',NULL,NULL,'unpaid','2025-12-25 04:08:27');
INSERT INTO `bills` VALUES ('33','B-202512-5','9','meter','12','2025',NULL,'37.89',NULL,NULL,'unpaid','2025-12-25 04:08:27');


CREATE TABLE `closed_months` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `closed_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `month` (`month`,`year`),
  KEY `closed_by` (`closed_by`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `address` mediumtext DEFAULT NULL,
  `area` varchar(100) DEFAULT NULL,
  `notes` mediumtext DEFAULT NULL,
  `status` tinyint(4) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `meter_id` varchar(100) DEFAULT NULL,
  `system_type` enum('meter','ampere') DEFAULT 'meter',
  `sub_no` varchar(50) DEFAULT NULL,
  `initial_reading` decimal(10,2) DEFAULT 0.00,
  `ampere_qty` int(11) DEFAULT 0,
  `custom_amp_price` decimal(10,2) DEFAULT 0.00,
  `monthly_fixed_fee` decimal(10,2) DEFAULT 0.00,
  `old_balance` decimal(10,2) DEFAULT 0.00,
  `insurance` decimal(10,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `customers` VALUES ('9','باسل','7000050','حد','محل اسامة','','1','2025-12-24 07:48:48',NULL,'meter','5','1.00','15','0.00','20.00','0.00','100.00');
INSERT INTO `customers` VALUES ('10','مومن','7000050','شارح','محل اسامة','','1','2025-12-24 07:55:52',NULL,'ampere','4','0.00','5','0.00','10.00','0.00','100.00');
INSERT INTO `customers` VALUES ('11','اسامة','','حد','محل اسامة','','1','2025-12-24 08:32:43',NULL,'meter','10','1.00','10','0.00','15.00','0.00','100.00');
INSERT INTO `customers` VALUES ('12','احمد','7162525','حد','حي التاني','','1','2025-12-24 08:33:58','234524','meter','55','1.00','10','0.00','18.00','0.00','100.00');
INSERT INTO `customers` VALUES ('13','خالد','766','شارع','ergg','','1','2025-12-24 08:35:48',NULL,'ampere','14','1.00','10','0.00','0.00','0.00','150.00');


CREATE TABLE `exchange_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `usd_to_lbp` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `meter_readings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meter_id` int(11) NOT NULL,
  `previous_reading` decimal(10,2) DEFAULT NULL,
  `current_reading` decimal(10,2) DEFAULT NULL,
  `collector_id` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_meter_period` (`meter_id`,`month`,`year`),
  KEY `collector_id` (`collector_id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `meter_readings` VALUES ('11','9','1.00','50.00',NULL,'12','2025','2025-12-25 04:07:41');
INSERT INTO `meter_readings` VALUES ('10','12','1.00','5.00',NULL,'12','2025','2025-12-25 04:07:37');
INSERT INTO `meter_readings` VALUES ('12','11','1.00','60.00',NULL,'12','2025','2025-12-25 04:07:51');


CREATE TABLE `meters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `meter_number` varchar(50) DEFAULT NULL,
  `last_reading` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `meters` VALUES ('1','1','11111','0.00','2025-12-24 04:29:29');
INSERT INTO `meters` VALUES ('2','1','11111','0.00','2025-12-24 04:29:40');
INSERT INTO `meters` VALUES ('3','2','56263','0.00','2025-12-24 04:31:17');
INSERT INTO `meters` VALUES ('4','8','565852','5.00','2025-12-24 06:57:34');
INSERT INTO `meters` VALUES ('5','12','234524','5.00','2025-12-24 08:34:31');


CREATE TABLE `monthly_pricing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `price_kwh` decimal(10,4) NOT NULL,
  `price_amp` decimal(10,2) NOT NULL,
  `fee_per_rated_amp` decimal(10,2) DEFAULT 0.00,
  `fixed_fee` decimal(10,2) DEFAULT 0.00,
  `exchange_rate` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_period` (`month`,`year`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `monthly_pricing` VALUES ('1','12','2025','32850.0000','30.00','0.00','0.00','90000','2025-12-24 07:25:17');
INSERT INTO `monthly_pricing` VALUES ('2','11','2025','0.2900','29.00','0.00',NULL,'90000','2025-12-24 07:30:29');


CREATE TABLE `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bill_id` int(11) NOT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `currency` enum('USD','LBP') DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `bill_id` (`bill_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `pricing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system_type` enum('meter','ampere') NOT NULL,
  `price` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `currency` varchar(20) DEFAULT 'USD',
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `kilo_price_usd` decimal(10,2) NOT NULL,
  `ampere_price_usd` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `month` (`month`,`year`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `pricing` VALUES ('3','meter','0.3000','USD','0','0','0.00','0.00','2025-12-24 06:06:02');


CREATE TABLE `settings` (
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `settings` VALUES ('exchange_rate','90000');
