-- https://www.phpmyadmin.net/
--

START TRANSACTION;



-- --------------------------------------------------------

--
-- Table structure for table `application_types`
--

CREATE TABLE `application_types` (
  `id` int NOT NULL,
  `application` text NOT NULL,
  `phase` int NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `billing_data`
--

CREATE TABLE `billing_data` (
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `meter_datetime` datetime DEFAULT NULL COMMENT 'Meter Date & Time',
  `active_energy_pos_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Active kWh (Import)',
  `active_energy_pos_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Active kWh (Import)',
  `active_energy_pos_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Active kWh (Import)',
  `active_energy_pos_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Active kWh (Import)',
  `active_energy_pos_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Active kWh (Import)',
  `active_energy_neg_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Active kWh (Export)',
  `active_energy_neg_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Active kWh (Export)',
  `active_energy_neg_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Active kWh (Export)',
  `active_energy_neg_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Active kWh (Export)',
  `active_energy_neg_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Active kWh (Export)',
  `active_energy_abs_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Absolute Active kWh',
  `active_energy_abs_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Absolute Active kWh',
  `active_energy_abs_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Absolute Active kWh',
  `active_energy_abs_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Absolute Active kWh',
  `active_energy_abs_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Absolute Active kWh',
  `reactive_energy_pos_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Reactive kVARh (Import)',
  `reactive_energy_pos_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Reactive kVARh (Import)',
  `reactive_energy_pos_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Reactive kVARh (Import)',
  `reactive_energy_pos_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Reactive kVARh (Import)',
  `reactive_energy_pos_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Reactive kVARh (Import)',
  `reactive_energy_neg_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Reactive kVARh (Export)',
  `reactive_energy_neg_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Reactive kVARh (Export)',
  `reactive_energy_neg_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Reactive kVARh (Export)',
  `reactive_energy_neg_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Reactive kVARh (Export)',
  `reactive_energy_neg_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Reactive kVARh (Export)',
  `reactive_energy_abs_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Absolute Reactive kVARh as per Pakistan standard',
  `active_mdi_pos_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Active MDI kW (Import)',
  `active_mdi_pos_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Active MDI kW (Import)',
  `active_mdi_pos_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Active MDI kW (Import)',
  `active_mdi_pos_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Active MDI kW (Import)',
  `active_mdi_pos_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Active MDI kW (Import)',
  `active_mdi_neg_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Active MDI kW (Export)',
  `active_mdi_neg_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Active MDI kW (Export)',
  `active_mdi_neg_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Active MDI kW (Export)',
  `active_mdi_neg_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Active MDI kW (Export)',
  `active_mdi_neg_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Active MDI kW (Export)',
  `active_mdi_abs_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Absolute Active MDI kW',
  `active_mdi_abs_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Absolute Active MDI kW',
  `active_mdi_abs_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Absolute Active MDI kW',
  `active_mdi_abs_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Absolute Active MDI kW',
  `active_mdi_abs_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Absolute Active MDI kW',
  `cumulative_mdi_pos_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Cumulative Active MDI kW (Import)',
  `cumulative_mdi_neg_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Cumulative Active MDI kW (Export)',
  `cumulative_mdi_abs_t1` decimal(15,5) DEFAULT NULL COMMENT 'T1 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t2` decimal(15,5) DEFAULT NULL COMMENT 'T2 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t3` decimal(15,5) DEFAULT NULL COMMENT 'T3 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t4` decimal(15,5) DEFAULT NULL COMMENT 'T4 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_tl` decimal(15,5) DEFAULT NULL COMMENT 'Total Absolute Cumulative Active MDI kW',
  `mdc_read_datetime` datetime DEFAULT NULL COMMENT 'Reading Date & Time of MDC',
  `db_datetime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Record Entry Date & Time in Database',
  `is_synced` int DEFAULT '0' COMMENT '0 By default, 1 if data is safe to delete from MDC',
  `id` bigint NOT NULL COMMENT 'Auto-increment (Not NULL) Unique-ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `commands`
--

CREATE TABLE `commands` (
  `id` int NOT NULL,
  `command` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `gid` varchar(255) NOT NULL,
  `a` varchar(255) NOT NULL,
  `at` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `companys`
--

CREATE TABLE `companys` (
  `id` int NOT NULL,
  `title` text,
  `address` text,
  `contact` text,
  `email` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `device_communication_history`
--

CREATE TABLE `device_communication_history` (
  `id` bigint NOT NULL COMMENT 'Unique-ID',
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `meter_datetime` datetime DEFAULT NULL COMMENT 'Meter Date & Time',
  `communication_status` int DEFAULT NULL COMMENT '1 for Successful, 0 for Failure',
  `message_log` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Message Log showing communication insights using UDIL Codes, including timestamps.',
  `longitude` decimal(20,10) DEFAULT NULL COMMENT 'Longitude of Device',
  `latitude` decimal(20,10) DEFAULT NULL COMMENT 'Latitude of Device',
  `bytes_sent` int DEFAULT NULL COMMENT 'Size of Total Data sent to Meter (in Bytes)',
  `bytes_received` int DEFAULT NULL COMMENT 'Size of Total Data received from Meter (in Bytes)',
  `bytes_remaining` int DEFAULT NULL COMMENT 'Remaining bytes of data package in SIM for meter communication (Optional)',
  `sim_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Inserted SIM ID in Meter Modem',
  `connection_duration` int DEFAULT NULL COMMENT 'Total Duration in Seconds for which Connection between MDC & Meter lasted',
  `mdc_read_datetime` datetime DEFAULT NULL COMMENT 'Reading Date & Time of MDC',
  `db_datetime` datetime DEFAULT NULL COMMENT 'Record Entry Date & Time in Database',
  `is_synced` int DEFAULT NULL COMMENT '0 By default, 1 if data is safe to delete from MDC'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table for storing device communication history';

-- --------------------------------------------------------

--
-- Table structure for table `device_configuration`
--

CREATE TABLE `device_configuration` (
  `id` int NOT NULL,
  `global_device_id` varchar(100) NOT NULL,
  `data` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` bigint NOT NULL COMMENT 'Unique-ID',
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `event_datetime` datetime DEFAULT NULL COMMENT 'Meter Date & Time when event occurred',
  `event_code` int DEFAULT NULL COMMENT 'Event Code',
  `event_counter` bigint DEFAULT NULL COMMENT 'Optional Event Counter',
  `event_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Event Name/Description',
  `mdc_read_datetime` datetime DEFAULT NULL COMMENT 'Reading Date & Time of MDC',
  `db_datetime` datetime DEFAULT NULL COMMENT 'Record Entry Date & Time in Database',
  `is_synced` int DEFAULT '0' COMMENT '0 By default, 1 if data is safe to delete from MDC'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table for storing events data';

-- --------------------------------------------------------

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint UNSIGNED NOT NULL,
  `uuid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `feeders`
--

CREATE TABLE `feeders` (
  `id` int NOT NULL,
  `fname` text NOT NULL,
  `code` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `instantaneous_data`
--

CREATE TABLE `instantaneous_data` (
  `id` bigint NOT NULL COMMENT 'Auto-increment (Not NULL) Unique-ID',
  `current_tariff_register` int DEFAULT NULL COMMENT 'Current/Active Tariff Register',
  `signal_strength` decimal(10,2) DEFAULT NULL COMMENT 'Signal Strength in dBm',
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `frequency` decimal(10,2) DEFAULT NULL COMMENT 'Frequency in Hertz',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `meter_datetime` datetime DEFAULT NULL COMMENT 'Meter Date & Time',
  `current_phase_a` decimal(10,2) DEFAULT NULL COMMENT 'Current Phase A',
  `current_phase_b` decimal(10,2) DEFAULT NULL COMMENT 'Current Phase B',
  `current_phase_c` decimal(10,2) DEFAULT NULL COMMENT 'Current Phase C',
  `voltage_phase_a` decimal(10,2) DEFAULT NULL COMMENT 'Voltage Phase A',
  `voltage_phase_b` decimal(10,2) DEFAULT NULL COMMENT 'Voltage Phase B',
  `voltage_phase_c` decimal(10,2) DEFAULT NULL COMMENT 'Voltage Phase C',
  `aggregate_active_pwr_pos` decimal(10,2) DEFAULT NULL COMMENT 'Aggregate Active Power Import',
  `aggregate_active_pwr_neg` decimal(10,2) DEFAULT NULL COMMENT 'Aggregate Active Power Export',
  `aggregate_active_pwr_abs` decimal(10,2) DEFAULT NULL COMMENT 'Aggregate Active Power Absolute',
  `aggregate_reactive_pwr_pos` decimal(10,2) DEFAULT NULL COMMENT 'Aggregate Reactive Power Import',
  `aggregate_reactive_pwr_neg` decimal(10,2) DEFAULT NULL COMMENT 'Aggregate Reactive Power Export',
  `aggregate_reactive_pwr_abs` decimal(10,2) DEFAULT NULL COMMENT 'Aggregate Reactive Power Absolute as per Pakistan standard',
  `thdva` double DEFAULT NULL,
  `thdvb` double DEFAULT NULL,
  `thdvc` double DEFAULT NULL,
  `load_direction_a` tinyint DEFAULT NULL,
  `load_direction_b` tinyint DEFAULT NULL,
  `load_direction_c` tinyint DEFAULT NULL,
  `average_pf` decimal(10,2) DEFAULT NULL COMMENT 'Average Power Factor',
  `mdc_read_datetime` datetime DEFAULT NULL COMMENT 'Reading Date & Time of MDC',
  `db_datetime` datetime DEFAULT NULL COMMENT 'Record Entry Date & Time in Database',
  `is_synced` int DEFAULT '0' COMMENT '0 By default, 1 if data is safe to delete from MDC',
  `kva1` double DEFAULT NULL,
  `kva2` double DEFAULT NULL,
  `kva3` double DEFAULT NULL,
  `kvar1` double DEFAULT NULL,
  `kvar2` double DEFAULT NULL,
  `kvar3` double DEFAULT NULL,
  `kw1` double DEFAULT NULL,
  `kw2` double DEFAULT NULL,
  `kw3` double DEFAULT NULL,
  `kvarhp1` double DEFAULT NULL,
  `kvarhp2` double DEFAULT NULL,
  `kvarhp3` double DEFAULT NULL,
  `kvarho1` double DEFAULT NULL,
  `kvarho2` double DEFAULT NULL,
  `kvarho3` double DEFAULT NULL,
  `rkvarhp1` double DEFAULT NULL,
  `rkvarhp2` double DEFAULT NULL,
  `rkvarhp3` double DEFAULT NULL,
  `rkvarho1` double DEFAULT NULL,
  `rkvarho2` double DEFAULT NULL,
  `rkvarho3` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `kwh_readings`
--

CREATE TABLE `kwh_readings` (
  `id` int NOT NULL,
  `global_device_id` varchar(100) NOT NULL,
  `pesco_total_kwho` double NOT NULL,
  `pesco_total_kwhp` double NOT NULL,
  `total_kwho` double NOT NULL,
  `total_kwhp` double NOT NULL,
  `pesco_images` text,
  `cisnr_images` text,
  `rdate` date NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `load_profile_data`
--

CREATE TABLE `load_profile_data` (
  `id` bigint NOT NULL COMMENT 'Unique-ID',
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `meter_datetime` datetime DEFAULT NULL COMMENT 'Meter Date & Time',
  `frequency` decimal(20,10) DEFAULT NULL COMMENT 'Frequency in Hertz',
  `channel_id` int DEFAULT NULL COMMENT 'Load Profile Channel ID where applicable',
  `interval1` int DEFAULT NULL COMMENT 'Interval of Profile',
  `active_energy_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active kWh (Import)',
  `active_energy_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active kWh (Import)',
  `active_energy_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active kWh (Import)',
  `active_energy_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active kWh (Import)',
  `active_energy_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active kWh (Import)',
  `active_energy_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active kWh (Export)',
  `active_energy_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active kWh (Export)',
  `active_energy_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active kWh (Export)',
  `active_energy_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active kWh (Export)',
  `active_energy_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active kWh (Export)',
  `active_energy_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Active kWh',
  `active_energy_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Active kWh',
  `active_energy_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Active kWh',
  `active_energy_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Active kWh',
  `active_energy_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Active kWh',
  `reactive_energy_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Reactive kVARh (Import)',
  `reactive_energy_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Reactive kVARh (Import)',
  `reactive_energy_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Reactive kVARh (Import)',
  `reactive_energy_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Reactive kVARh (Import)',
  `reactive_energy_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Reactive kVARh (Import)',
  `reactive_energy_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Reactive kVARh (Export)',
  `reactive_energy_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Reactive kVARh (Export)',
  `reactive_energy_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Reactive kVARh (Export)',
  `reactive_energy_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Reactive kVARh (Export)',
  `reactive_energy_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Reactive kVARh (Export)',
  `reactive_energy_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Reactive kVARh as per Pakistan standard',
  `active_mdi_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active MDI kW (Import)',
  `active_mdi_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active MDI kW (Import)',
  `active_mdi_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active MDI kW (Import)',
  `active_mdi_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active MDI kW (Import)',
  `active_mdi_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active MDI kW (Import)',
  `active_mdi_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active MDI kW (Export)',
  `active_mdi_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active MDI kW (Export)',
  `active_mdi_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active MDI kW (Export)',
  `active_mdi_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active MDI kW (Export)',
  `active_mdi_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active MDI kW (Export)',
  `active_mdi_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Active MDI kW',
  `active_mdi_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Active MDI kW',
  `active_mdi_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Active MDI kW',
  `active_mdi_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Active MDI kW',
  `active_mdi_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Active MDI kW',
  `cumulative_mdi_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Cumulative Active MDI kW (Import)',
  `cumulative_mdi_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Cumulative Active MDI kW (Export)',
  `cumulative_mdi_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Cumulative Active MDI kW',
  `current_phase_a` decimal(20,10) DEFAULT NULL COMMENT 'Current Phase A',
  `current_phase_b` decimal(20,10) DEFAULT NULL COMMENT 'Current Phase B',
  `current_phase_c` decimal(20,10) DEFAULT NULL COMMENT 'Current Phase C',
  `voltage_phase_a` decimal(20,10) DEFAULT NULL COMMENT 'Voltage Phase A',
  `voltage_phase_b` decimal(20,10) DEFAULT NULL COMMENT 'Voltage Phase B',
  `voltage_phase_c` decimal(20,10) DEFAULT NULL COMMENT 'Voltage Phase C',
  `active_pwr_pos_phase_a` decimal(20,10) DEFAULT NULL COMMENT 'Active Power Import Phase A',
  `active_pwr_pos_phase_b` decimal(20,10) DEFAULT NULL COMMENT 'Active Power Import Phase B',
  `active_pwr_pos_phase_c` decimal(20,10) DEFAULT NULL COMMENT 'Active Power Import Phase C',
  `aggregate_active_pwr_pos` decimal(20,10) DEFAULT NULL COMMENT 'Aggregate Active Power Import',
  `active_pwr_neg_phase_a` decimal(20,10) DEFAULT NULL COMMENT 'Active Power Export Phase A',
  `active_pwr_neg_phase_b` decimal(20,10) DEFAULT NULL COMMENT 'Active Power Export Phase B',
  `active_pwr_neg_phase_c` decimal(20,10) DEFAULT NULL COMMENT 'Active Power Export Phase C',
  `aggregate_active_pwr_neg` decimal(20,10) DEFAULT NULL COMMENT 'Aggregate Active Power Export',
  `aggregate_active_pwr_abs` decimal(20,10) DEFAULT NULL COMMENT 'Aggregate Active Power Absolute',
  `reactive_pwr_pos_phase_a` decimal(20,10) DEFAULT NULL COMMENT 'Reactive Power Import Phase A',
  `reactive_pwr_pos_phase_b` decimal(20,10) DEFAULT NULL COMMENT 'Reactive Power Import Phase B',
  `reactive_pwr_pos_phase_c` decimal(20,10) DEFAULT NULL COMMENT 'Reactive Power Import Phase C',
  `aggregate_reactive_pwr_pos` decimal(20,10) DEFAULT NULL COMMENT 'Aggregate Reactive Power Import',
  `reactive_pwr_neg_phase_a` decimal(20,10) DEFAULT NULL COMMENT 'Reactive Power Export Phase A',
  `reactive_pwr_neg_phase_b` decimal(20,10) DEFAULT NULL COMMENT 'Reactive Power Export Phase B',
  `reactive_pwr_neg_phase_c` decimal(20,10) DEFAULT NULL COMMENT 'Reactive Power Export Phase C',
  `aggregate_reactive_pwr_neg` decimal(20,10) DEFAULT NULL COMMENT 'Aggregate Reactive Power Export',
  `aggregate_reactive_pwr_abs` decimal(20,10) DEFAULT NULL COMMENT 'Aggregate Reactive Power Absolute as per Pakistan standard',
  `thdva` double DEFAULT NULL,
  `thdvb` double DEFAULT NULL,
  `thdvc` double DEFAULT NULL,
  `load_direction_a` tinyint DEFAULT NULL,
  `load_direction_b` tinyint DEFAULT NULL,
  `load_direction_c` tinyint DEFAULT NULL,
  `average_pf` decimal(20,10) DEFAULT NULL COMMENT 'Average Power Factor',
  `mdc_read_datetime` datetime DEFAULT NULL COMMENT 'Reading Date & Time of MDC',
  `db_datetime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Record Entry Date & Time in Database',
  `is_synced` int DEFAULT NULL COMMENT '0 By default, 1 if data is safe to delete from MDC'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table for storing load profile data';

-- --------------------------------------------------------

--
-- Table structure for table `logs`
--

CREATE TABLE `logs` (
  `id` int NOT NULL,
  `gid` text,
  `info` text,
  `data` text,
  `ttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `meter_visuals`
--

CREATE TABLE `meter_visuals` (
  `id` bigint NOT NULL COMMENT 'Unique-ID',
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `last_command` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Last Command Sent to Meter',
  `last_command_datetime` datetime DEFAULT NULL COMMENT 'Date & Time of Last Command',
  `last_command_resp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Last Command Response',
  `last_command_resp_datetime` datetime DEFAULT NULL COMMENT 'Response Date & Time of Last Command',
  `meter_datetime` datetime DEFAULT NULL COMMENT 'Meter Date & Time',
  `mdc_read_datetime` datetime DEFAULT NULL COMMENT 'Reading Date & Time of MDC',
  `db_datetime` datetime DEFAULT NULL COMMENT 'Record Entry/Update Date & Time in Database',
  `active_energy_pos_tl` decimal(20,6) DEFAULT NULL COMMENT 'Value of Positive Absolute kWh',
  `active_energy_pos_tl_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for kWh',
  `active_energy_neg_tl` decimal(20,6) DEFAULT NULL COMMENT 'Value of Negative Absolute kWh',
  `active_energy_neg_tl_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for kWh',
  `reactive_energy_pos_tl` decimal(20,6) DEFAULT NULL COMMENT 'Value of Positive Absolute kVARh',
  `reactive_energy_pos_tl_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for kVARh',
  `reactive_energy_neg_tl` decimal(20,6) DEFAULT NULL COMMENT 'Value of Negative Absolute kVARh',
  `reactive_energy_neg_tl_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for kVARh',
  `aggregate_active_energy_abs_tl` decimal(20,6) DEFAULT NULL COMMENT 'Total absolute Active kVARh',
  `aggregate_active_energy_abs_tl_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for Total absolute Active kVARh',
  `aggregate_active_pwr_pos` decimal(20,6) DEFAULT NULL COMMENT 'Aggregate Active Power Import',
  `aggregate_active_pwr_pos_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for positive kW',
  `aggregate_active_pwr_neg` decimal(20,6) DEFAULT NULL COMMENT 'Aggregate Active Power Export',
  `aggregate_active_pwr_neg_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for negative kW',
  `aggregate_reactive_pwr_pos` decimal(20,6) DEFAULT NULL COMMENT 'Aggregate Reactive Power Import',
  `aggregate_reactive_pwr_pos_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for positive kVAR',
  `aggregate_reactive_pwr_neg` decimal(20,6) DEFAULT NULL COMMENT 'Aggregate Reactive Power Export',
  `aggregate_reactive_pwr_neg_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for negative kVAR',
  `aggregate_reactive_energy_abs_tl` decimal(20,6) DEFAULT NULL COMMENT 'Total Reactive kVARh as per Pakistan standard',
  `aggregate_reactive_energy_abs_tl_datetime` datetime DEFAULT NULL COMMENT 'Latest Reporting Date & Time for Reactive kVArh',
  `current_phase_a` decimal(20,6) DEFAULT NULL COMMENT 'Current Phase A',
  `current_phase_b` decimal(20,6) DEFAULT NULL COMMENT 'Current Phase B',
  `current_phase_c` decimal(20,6) DEFAULT NULL COMMENT 'Current Phase C',
  `voltage_phase_a` decimal(20,6) DEFAULT NULL COMMENT 'Voltage Phase A',
  `voltage_phase_b` decimal(20,6) DEFAULT NULL COMMENT 'Voltage Phase B',
  `voltage_phase_c` decimal(20,6) DEFAULT NULL COMMENT 'Voltage Phase C',
  `frequency` decimal(10,6) DEFAULT NULL COMMENT 'Frequency in Hertz',
  `average_pf` decimal(10,6) DEFAULT NULL COMMENT 'Average Power Factor',
  `last_contactor_on_datetime` datetime DEFAULT NULL COMMENT 'Last Contactor ‘On Date & Time’',
  `last_contactor_off_datetime` datetime DEFAULT NULL COMMENT 'Last Contactor ‘Off Date & Time’',
  `last_communication_datetime` datetime DEFAULT NULL COMMENT 'Last Date & Time of MDC on which device communicated',
  `last_signal_strength` decimal(10,6) DEFAULT NULL COMMENT 'Latest Signal Strength in dBm',
  `power_status` int DEFAULT NULL COMMENT '0 for power off (power failure start event), 1 for power on (power failure end event)',
  `power_status_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which power failure start or end event occurred',
  `auxr_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which meter relay investigated',
  `auxr_status` int DEFAULT NULL COMMENT 'Current Contactor status. 1 for ‘On’, 0 for ‘Off’',
  `is_aux_relay` tinyint DEFAULT '0',
  `dvtm_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which meter clock investigated',
  `dvtm_meter_clock` datetime DEFAULT NULL COMMENT 'Actual Meter Date Time',
  `sanc_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which meter sanctioned load investigated',
  `sanc_load_limit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Threshold Limit for kW',
  `sanc_maximum_retries` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Maximum Retries in number',
  `sanc_retry_interval` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Time Interval for retry in seconds',
  `sanc_threshold_duration` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Duration to accept threshold crossing limit in seconds',
  `sanc_retry_clear_interval` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Time after retries count is cleared in seconds',
  `lsch_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which meter sanctioned load investigated',
  `lsch_start_datetime` datetime DEFAULT NULL COMMENT 'Date Time for starting load shedding schedule',
  `lsch_end_datetime` datetime DEFAULT NULL COMMENT 'Date Time till load shedding schedule lasts',
  `lsch_load_shedding_slabs` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'JSON/XML Array Containing action time & relay operate values for multiple time slabs with relay status. Relay Operate value will be 0 for Disconnect & 1 for Connect',
  `tiou_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which Time of use investigated',
  `tiou_day_profile` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Associative Array containing multiple list of day profiles having name and tariff slabs array containing starting time (hh:mm) of tariffs in ascending order',
  `tiou_week_profile` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Associative Array containing multiple list of week profiles having name and weekly day profiles array containing day profiles in ascending order of week starting from Monday to Sunday',
  `tiou_season_profile` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Associative Array containing multiple list of season profiles having name mapped with week profile name and start date (dd-MM)',
  `tiou_holiday_profile` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Associative Array containing multiple list of holiday profiles having name mapped with execution date (dd-MM) and day profile name to be implemented',
  `tiou_activation_datetime` datetime DEFAULT NULL COMMENT 'Date Time for the activation of condition',
  `ippo_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which IP & Port investigated',
  `ippo_primary_ip_address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'New Primary IP Address',
  `ippo_secondary_ip_address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'New Secondary IP Address',
  `ippo_primary_port` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'New Primary Port Address',
  `ippo_secondary_port` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'New Secondary Port Address',
  `mdsm_datetime` datetime DEFAULT NULL COMMENT 'Date Time at meter data sampling investigated',
  `mdsm_activation_datetime` datetime DEFAULT NULL COMMENT 'Date Time for the activation of condition',
  `mdsm_data_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Valid data type values will be “INST” for Instantaneous Data, “BILL” for Billing Profiles, “LPRO” for Load Profile',
  `mdsm_sampling_interval` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Sampling Interval in Minutes ranges from 1 – 59',
  `mdsm_sampling_initial_time` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Start Time Slot ranges from 0 to 59 e.g. If Sampling interval is set to 15 and Sampling Initial Time is 0 then data will be stored in meter at following time interval 00:00, 15:00, 30:00, 45:00 of every hour on daily basis',
  `oppo_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which Optical Port investigated',
  `oppo_optical_port_on_datetime` datetime DEFAULT NULL COMMENT 'Date & Time to enable Optical Port',
  `oppo_optical_port_off_datetime` datetime DEFAULT NULL COMMENT 'Date & Time to disable Optical Port',
  `wsim_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which wakeup SIM number investigated',
  `wsim_wakeup_number_1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'SIM NUMBER1 for wakeup',
  `wsim_wakeup_number_2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'SIM NUMBER2 for wakeup',
  `wsim_wakeup_number_3` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'SIM NUMBER3 for wakeup',
  `mtst_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which meter status investigated',
  `mtst_meter_activation_status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '1 for Active, 0 for Inactive Meters',
  `dmdt_datetime` datetime DEFAULT NULL COMMENT 'Date Time at which device metadata investigated',
  `dmdt_communication_mode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '1 for GPRS/3G/4G, 2 for RF, 3 for PLC, 4 for Ethernet, 5 for other',
  `dmdt_bidirectional_device` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '0 for False, 1 for True',
  `dmdt_communication_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '1 for Mode-I/Non-Keep-alive, 2 for Mode-II/Keep-alive',
  `dmdt_communication_interval` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Communication Interval in Minutes if Communication Type value is SET for “1” Otherwise 0',
  `dmdt_initial_communication_time` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Initial Communication Time for meter in 24 Hrs in “HH:mm:ss” Format',
  `dmdt_phase` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '1 for Single, 2 for other, 3 for Three-phase',
  `dmdt_meter_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '1 for Normal, 2 for Whole Current, 3 for CTO, 4 for CTPT, 5 for other',
  `mdi_reset_date` int DEFAULT NULL COMMENT 'Date of Every Month Ranges from 1 to 28',
  `mdi_reset_time` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Time at which MDI Reset Occurs',
  `msim_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'SIM ID of SIM that is used by meter for communication',
  `device_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sim_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `kwhp1` double NOT NULL DEFAULT '0',
  `kwhp2` double NOT NULL DEFAULT '0',
  `kwhp3` double NOT NULL DEFAULT '0',
  `kwho1` double NOT NULL DEFAULT '0',
  `kwho2` double NOT NULL DEFAULT '0',
  `kwho3` double NOT NULL DEFAULT '0',
  `kwhdevp1` float NOT NULL DEFAULT '0',
  `kwhdevp2` float NOT NULL DEFAULT '0',
  `kwhdevp3` float NOT NULL DEFAULT '0',
  `kwhdevo1` float NOT NULL DEFAULT '0',
  `kwhdevo2` float NOT NULL DEFAULT '0',
  `kwhdevo3` float NOT NULL DEFAULT '0',
  `offpeak` bigint NOT NULL DEFAULT '0',
  `peak` bigint NOT NULL DEFAULT '0',
  `NL` double NOT NULL DEFAULT '0',
  `NUL` double NOT NULL DEFAULT '0',
  `NC` double NOT NULL DEFAULT '0',
  `latitude` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `longitude` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `server_time_update_our_value` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pf1` double NOT NULL DEFAULT '0',
  `pf2` double NOT NULL DEFAULT '0',
  `pf3` double NOT NULL DEFAULT '0',
  `peak_dev` double NOT NULL DEFAULT '0',
  `offpeak_dev` double NOT NULL DEFAULT '0',
  `rkwho1` double DEFAULT NULL,
  `rkwho2` double DEFAULT NULL,
  `rkwho3` double DEFAULT NULL,
  `rkwhp1` double DEFAULT NULL,
  `rkwhp2` double DEFAULT NULL,
  `rkwhp3` double DEFAULT NULL,
  `installed_on` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `feeder_id` int DEFAULT NULL,
  `transformer_id` int DEFAULT NULL,
  `pool_id` int DEFAULT NULL,
  `kva` int NOT NULL DEFAULT '0',
  `category` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'NOR',
  `parent_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table for storing meter visual data';

--
-- Dumping data for table `meter_visuals`
--

INSERT INTO `meter_visuals` (`id`, `msn`, `global_device_id`, `last_command`, `last_command_datetime`, `last_command_resp`, `last_command_resp_datetime`, `meter_datetime`, `mdc_read_datetime`, `db_datetime`, `active_energy_pos_tl`, `active_energy_pos_tl_datetime`, `active_energy_neg_tl`, `active_energy_neg_tl_datetime`, `reactive_energy_pos_tl`, `reactive_energy_pos_tl_datetime`, `reactive_energy_neg_tl`, `reactive_energy_neg_tl_datetime`, `aggregate_active_energy_abs_tl`, `aggregate_active_energy_abs_tl_datetime`, `aggregate_active_pwr_pos`, `aggregate_active_pwr_pos_datetime`, `aggregate_active_pwr_neg`, `aggregate_active_pwr_neg_datetime`, `aggregate_reactive_pwr_pos`, `aggregate_reactive_pwr_pos_datetime`, `aggregate_reactive_pwr_neg`, `aggregate_reactive_pwr_neg_datetime`, `aggregate_reactive_energy_abs_tl`, `aggregate_reactive_energy_abs_tl_datetime`, `current_phase_a`, `current_phase_b`, `current_phase_c`, `voltage_phase_a`, `voltage_phase_b`, `voltage_phase_c`, `frequency`, `average_pf`, `last_contactor_on_datetime`, `last_contactor_off_datetime`, `last_communication_datetime`, `last_signal_strength`, `power_status`, `power_status_datetime`, `auxr_datetime`, `auxr_status`, `is_aux_relay`, `dvtm_datetime`, `dvtm_meter_clock`, `sanc_datetime`, `sanc_load_limit`, `sanc_maximum_retries`, `sanc_retry_interval`, `sanc_threshold_duration`, `sanc_retry_clear_interval`, `lsch_datetime`, `lsch_start_datetime`, `lsch_end_datetime`, `lsch_load_shedding_slabs`, `tiou_datetime`, `tiou_day_profile`, `tiou_week_profile`, `tiou_season_profile`, `tiou_holiday_profile`, `tiou_activation_datetime`, `ippo_datetime`, `ippo_primary_ip_address`, `ippo_secondary_ip_address`, `ippo_primary_port`, `ippo_secondary_port`, `mdsm_datetime`, `mdsm_activation_datetime`, `mdsm_data_type`, `mdsm_sampling_interval`, `mdsm_sampling_initial_time`, `oppo_datetime`, `oppo_optical_port_on_datetime`, `oppo_optical_port_off_datetime`, `wsim_datetime`, `wsim_wakeup_number_1`, `wsim_wakeup_number_2`, `wsim_wakeup_number_3`, `mtst_datetime`, `mtst_meter_activation_status`, `dmdt_datetime`, `dmdt_communication_mode`, `dmdt_bidirectional_device`, `dmdt_communication_type`, `dmdt_communication_interval`, `dmdt_initial_communication_time`, `dmdt_phase`, `dmdt_meter_type`, `mdi_reset_date`, `mdi_reset_time`, `msim_id`, `device_type`, `sim_number`, `kwhp1`, `kwhp2`, `kwhp3`, `kwho1`, `kwho2`, `kwho3`, `kwhdevp1`, `kwhdevp2`, `kwhdevp3`, `kwhdevo1`, `kwhdevo2`, `kwhdevo3`, `offpeak`, `peak`, `NL`, `NUL`, `NC`, `latitude`, `longitude`, `description`, `server_time_update_our_value`, `pf1`, `pf2`, `pf3`, `peak_dev`, `offpeak_dev`, `rkwho1`, `rkwho2`, `rkwho3`, `rkwhp1`, `rkwhp2`, `rkwhp3`, `installed_on`, `feeder_id`, `transformer_id`, `pool_id`, `kva`, `category`, `parent_id`) VALUES
(1, 0, 'TR01', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, NULL, NULL, '3', NULL, NULL, NULL, NULL, NULL, '0', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '0', '0', NULL, '2025-07-01 09:27:16', 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, 'transformer', NULL, NULL, NULL, 0, 'STDB', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int UNSIGNED NOT NULL,
  `migration` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `monthly_billing_data`
--

CREATE TABLE `monthly_billing_data` (
  `id` bigint NOT NULL COMMENT 'Auto-increment (Not NULL) Unique-ID',
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `meter_datetime` datetime DEFAULT NULL COMMENT 'Meter Date & Time',
  `active_energy_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active kWh (Import)',
  `active_energy_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active kWh (Import)',
  `active_energy_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active kWh (Import)',
  `active_energy_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active kWh (Import)',
  `active_energy_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active kWh (Import)',
  `active_energy_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active kWh (Export)',
  `active_energy_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active kWh (Export)',
  `active_energy_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active kWh (Export)',
  `active_energy_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active kWh (Export)',
  `active_energy_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active kWh (Export)',
  `active_energy_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Active kWh',
  `active_energy_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Active kWh',
  `active_energy_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Active kWh',
  `active_energy_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Active kWh',
  `active_energy_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Active kWh',
  `reactive_energy_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Reactive kVARh (Import)',
  `reactive_energy_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Reactive kVARh (Import)',
  `reactive_energy_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Reactive kVARh (Import)',
  `reactive_energy_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Reactive kVARh (Import)',
  `reactive_energy_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Reactive kVARh (Import)',
  `reactive_energy_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Reactive kVARh (Export)',
  `reactive_energy_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Reactive kVARh (Export)',
  `reactive_energy_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Reactive kVARh (Export)',
  `reactive_energy_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Reactive kVARh (Export)',
  `reactive_energy_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Reactive kVARh (Export)',
  `reactive_energy_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Reactive kVARh as per Pakistan standard',
  `reactive_energy_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Reactive kVARh as per Pakistan standard',
  `active_mdi_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active MDI kW (Import)',
  `active_mdi_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active MDI kW (Import)',
  `active_mdi_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active MDI kW (Import)',
  `active_mdi_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active MDI kW (Import)',
  `active_mdi_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active MDI kW (Import)',
  `active_mdi_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Active MDI kW (Export)',
  `active_mdi_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Active MDI kW (Export)',
  `active_mdi_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Active MDI kW (Export)',
  `active_mdi_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Active MDI kW (Export)',
  `active_mdi_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Active MDI kW (Export)',
  `active_mdi_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Active MDI kW',
  `active_mdi_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Active MDI kW',
  `active_mdi_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Active MDI kW',
  `active_mdi_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Active MDI kW',
  `active_mdi_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Active MDI kW',
  `cumulative_mdi_pos_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Cumulative Active MDI kW (Import)',
  `cumulative_mdi_pos_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Cumulative Active MDI kW (Import)',
  `cumulative_mdi_neg_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Cumulative Active MDI kW (Export)',
  `cumulative_mdi_neg_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Cumulative Active MDI kW (Export)',
  `cumulative_mdi_abs_t1` decimal(20,10) DEFAULT NULL COMMENT 'T1 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t2` decimal(20,10) DEFAULT NULL COMMENT 'T2 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t3` decimal(20,10) DEFAULT NULL COMMENT 'T3 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_t4` decimal(20,10) DEFAULT NULL COMMENT 'T4 Absolute Cumulative Active MDI kW',
  `cumulative_mdi_abs_tl` decimal(20,10) DEFAULT NULL COMMENT 'Total Absolute Cumulative Active MDI kW',
  `Longitude` decimal(20,10) DEFAULT NULL COMMENT 'Longitude of Device',
  `Latitude` decimal(20,10) DEFAULT NULL COMMENT 'Latitude of Device',
  `picture_1` blob COMMENT 'Meter Screen Picture 1',
  `picture_2` blob COMMENT 'Meter Screen Picture 2',
  `reading_mode` int DEFAULT NULL COMMENT '0 for Manual Reading, 1 for Automatic Reading',
  `mdi_reset_datetime` datetime DEFAULT NULL COMMENT 'MDI Reset Date & Time',
  `reset_count` int DEFAULT NULL COMMENT 'MDI Reset Count Number',
  `mdc_read_datetime` datetime DEFAULT NULL COMMENT 'Reading Date & Time of MDC',
  `db_datetime` datetime DEFAULT NULL COMMENT 'Record Entry Date & Time in Database',
  `is_synced` int DEFAULT '0' COMMENT '0 By default, 1 if data is safe to delete from MDC'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `oauth_auth_codes`
--

CREATE TABLE `oauth_auth_codes` (
  `id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `client_id` bigint UNSIGNED NOT NULL,
  `scopes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `revoked` tinyint(1) NOT NULL,
  `expires_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `oauth_personal_access_clients`
--

CREATE TABLE `oauth_personal_access_clients` (
  `id` bigint UNSIGNED NOT NULL,
  `client_id` bigint UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int NOT NULL,
  `tile` text NOT NULL,
  `code` varchar(30) DEFAULT NULL,
  `section_id` int NOT NULL,
  `url` text NOT NULL,
  `view_order` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permission_sections`
--

CREATE TABLE `permission_sections` (
  `id` int NOT NULL,
  `title` text NOT NULL,
  `view_order` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `raw_transfocure_log`
--

CREATE TABLE `raw_transfocure_log` (
  `id` bigint NOT NULL,
  `global_device_id` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  `dtime` varchar(30) DEFAULT NULL,
  `v1` double DEFAULT NULL,
  `v2` double DEFAULT NULL,
  `v3` double DEFAULT NULL,
  `i1` double DEFAULT NULL,
  `i2` double DEFAULT NULL,
  `i3` double DEFAULT NULL,
  `pf1` double DEFAULT NULL,
  `pf2` double DEFAULT NULL,
  `pf3` double DEFAULT NULL,
  `kwho1` double DEFAULT NULL,
  `kwho2` double DEFAULT NULL,
  `kwho3` double DEFAULT NULL,
  `kwhp1` double DEFAULT NULL,
  `kwhp2` double DEFAULT NULL,
  `kwhp3` double DEFAULT NULL,
  `rkwho1` double DEFAULT NULL,
  `rkwho2` double DEFAULT NULL,
  `rkwho3` double DEFAULT NULL,
  `rkwhp1` double DEFAULT NULL,
  `rkwhp2` double DEFAULT NULL,
  `rkwhp3` double DEFAULT NULL,
  `thdva` double DEFAULT NULL,
  `thdvb` double DEFAULT NULL,
  `thdvc` double DEFAULT NULL,
  `load_direction_a` tinyint DEFAULT NULL,
  `load_direction_b` tinyint DEFAULT NULL,
  `load_direction_c` tinyint DEFAULT NULL,
  `isPk` int NOT NULL DEFAULT '0',
  `isRv` int NOT NULL DEFAULT '0',
  `stime` datetime DEFAULT NULL,
  `remarks` text,
  `kva1` double DEFAULT NULL,
  `kva2` double DEFAULT NULL,
  `kva3` double DEFAULT NULL,
  `kvar1` double DEFAULT NULL,
  `kvar2` double DEFAULT NULL,
  `kvar3` double DEFAULT NULL,
  `kw1` double DEFAULT NULL,
  `kw2` double DEFAULT NULL,
  `kw3` double DEFAULT NULL,
  `kvarhp1` double DEFAULT NULL,
  `kvarhp2` double DEFAULT NULL,
  `kvarhp3` double DEFAULT NULL,
  `kvarho1` double DEFAULT NULL,
  `kvarho2` double DEFAULT NULL,
  `kvarho3` double DEFAULT NULL,
  `rkvarhp1` double DEFAULT NULL,
  `rkvarhp2` double DEFAULT NULL,
  `rkvarhp3` double DEFAULT NULL,
  `rkvarho1` double DEFAULT NULL,
  `rkvarho2` double DEFAULT NULL,
  `rkvarho3` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `readings`
--

CREATE TABLE `readings` (
  `id` int NOT NULL,
  `global_device_id` varchar(100) NOT NULL,
  `pv1` double NOT NULL,
  `pv2` double NOT NULL,
  `pv3` double NOT NULL,
  `pi1` double NOT NULL,
  `pi2` double NOT NULL,
  `pi3` double NOT NULL,
  `ppf1` double NOT NULL,
  `ppf2` double NOT NULL,
  `ppf3` double NOT NULL,
  `cv1` double NOT NULL DEFAULT '0',
  `cv2` double NOT NULL DEFAULT '0',
  `cv3` double NOT NULL DEFAULT '0',
  `ci1` double NOT NULL DEFAULT '0',
  `ci2` double NOT NULL DEFAULT '0',
  `ci3` double NOT NULL DEFAULT '0',
  `cpf1` double NOT NULL DEFAULT '0',
  `cpf2` double NOT NULL DEFAULT '0',
  `cpf3` double NOT NULL DEFAULT '0',
  `rdate` date NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `time_domain_graph_data`
--

CREATE TABLE `time_domain_graph_data` (
  `id` int NOT NULL,
  `device_id` varchar(100) NOT NULL,
  `v1` int DEFAULT NULL,
  `v2` int NOT NULL,
  `v3` int NOT NULL,
  `i1` int NOT NULL,
  `i2` int NOT NULL,
  `i3` int NOT NULL,
  `channel_group` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transaction_status`
--

CREATE TABLE `transaction_status` (
  `id` int NOT NULL,
  `transactionid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Transaction ID of command from MDM',
  `msn` bigint DEFAULT NULL COMMENT 'Meter Serial Number',
  `global_device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Unique ID for each device by MDM',
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Type of Request',
  `command_receiving_datetime` datetime DEFAULT NULL COMMENT 'Date & Time of command received by MDC',
  `status_level` int DEFAULT NULL COMMENT 'Status level range from 0 – 5. Latest value of status will be stored here. MDC will continuously update this column according to status.',
  `status_1_datetime` datetime DEFAULT NULL COMMENT 'Date & Time when command processing started by MDC',
  `status_2_datetime` datetime DEFAULT NULL COMMENT 'Date & Time when request was forwarded to meter from MDC',
  `status_3_datetime` datetime DEFAULT NULL COMMENT 'Date & Time when meter gets connected with MDC (Hand Shake)',
  `status_4_datetime` datetime DEFAULT NULL COMMENT 'Date & Time when request forwarded to meter from MDC',
  `status_5_datetime` datetime DEFAULT NULL COMMENT 'Date & Time when meter successfully executed command',
  `indv_status` int DEFAULT NULL COMMENT '0 for In-Progress, 1 for Complete, 2 for Failed',
  `request_cancelled` int DEFAULT NULL COMMENT '0 or otherwise 1 if MDC cancels the request',
  `request_cancel_reason` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Reason for cancellation of request',
  `request_cancel_datetime` datetime DEFAULT NULL COMMENT 'Date & time of cancellation of request',
  `response_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Optional Message'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Table for storing transaction status information';

-- --------------------------------------------------------

--
-- Table structure for table `transformers`
--

CREATE TABLE `transformers` (
  `id` int NOT NULL,
  `tname` text,
  `code` text,
  `feeder_id` int DEFAULT NULL,
  `parent_code` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `trapping_event_setting`
--

CREATE TABLE `trapping_event_setting` (
  `id` int NOT NULL,
  `global_device_id` text,
  `request_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` varchar(50) DEFAULT NULL,
  `critical_event_threshold_limit` varchar(20) NOT NULL,
  `critical_event_log_time` int NOT NULL,
  `tripping_event_threshold_limit` varchar(20) NOT NULL,
  `tripping_event_log_time` int NOT NULL,
  `enable_tripping` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `tr_current_logs`
--

CREATE TABLE `tr_current_logs` (
  `id` bigint NOT NULL,
  `global_device_id` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  `dtime` varchar(30) DEFAULT NULL,
  `v1` double DEFAULT NULL,
  `v2` double DEFAULT NULL,
  `v3` double DEFAULT NULL,
  `i1` double DEFAULT NULL,
  `i2` double DEFAULT NULL,
  `i3` double DEFAULT NULL,
  `nc` double NOT NULL DEFAULT '0',
  `ncl` double DEFAULT NULL,
  `d1` int NOT NULL DEFAULT '1',
  `d2` int NOT NULL DEFAULT '1',
  `d3` int NOT NULL DEFAULT '1',
  `pf1` double DEFAULT NULL,
  `pf2` double DEFAULT NULL,
  `pf3` double DEFAULT NULL,
  `kwho1` double DEFAULT NULL,
  `kwho2` double DEFAULT NULL,
  `kwho3` double DEFAULT NULL,
  `kwhp1` double DEFAULT NULL,
  `kwhp2` double DEFAULT NULL,
  `kwhp3` double DEFAULT NULL,
  `rkwho1` double DEFAULT NULL,
  `rkwho2` double DEFAULT NULL,
  `rkwho3` double DEFAULT NULL,
  `rkwhp1` double DEFAULT NULL,
  `rkwhp2` double DEFAULT NULL,
  `rkwhp3` double DEFAULT NULL,
  `isPk` int NOT NULL DEFAULT '0',
  `isRv` int NOT NULL DEFAULT '0',
  `stime` datetime DEFAULT NULL,
  `kva1` double DEFAULT NULL,
  `kva2` double DEFAULT NULL,
  `kva3` double DEFAULT NULL,
  `kvar1` double DEFAULT NULL,
  `kvar2` double DEFAULT NULL,
  `kvar3` double DEFAULT NULL,
  `kw1` double DEFAULT NULL,
  `kw2` double DEFAULT NULL,
  `kw3` double DEFAULT NULL,
  `kvarhp1` double DEFAULT NULL,
  `kvarhp2` double DEFAULT NULL,
  `kvarhp3` double DEFAULT NULL,
  `kvarho1` double DEFAULT NULL,
  `kvarho2` double DEFAULT NULL,
  `kvarho3` double DEFAULT NULL,
  `rkvarhp1` double DEFAULT NULL,
  `rkvarhp2` double DEFAULT NULL,
  `rkvarhp3` double DEFAULT NULL,
  `rkvarho1` double DEFAULT NULL,
  `rkvarho2` double DEFAULT NULL,
  `rkvarho3` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `tr_kwh_logs`
--

CREATE TABLE `tr_kwh_logs` (
  `id` int NOT NULL,
  `trid` varchar(14) NOT NULL,
  `offpeak` double NOT NULL,
  `peak` double DEFAULT NULL,
  `offpeak_dev` float DEFAULT NULL,
  `peak_dev` float DEFAULT NULL,
  `peak_dev_temp` float NOT NULL DEFAULT '0',
  `stime` datetime DEFAULT NULL,
  `dtime` datetime NOT NULL,
  `offpkunits` double DEFAULT NULL,
  `pkunits` double DEFAULT NULL,
  `offpkunits_dev` float DEFAULT NULL,
  `pkunits_dev` float DEFAULT NULL,
  `v1` double NOT NULL DEFAULT '0',
  `v2` double NOT NULL DEFAULT '0',
  `v3` double NOT NULL DEFAULT '0',
  `i1` double NOT NULL DEFAULT '0',
  `i2` double NOT NULL DEFAULT '0',
  `i3` double NOT NULL DEFAULT '0',
  `pf1` double NOT NULL DEFAULT '0',
  `pf2` double NOT NULL DEFAULT '0',
  `pf3` double NOT NULL DEFAULT '0',
  `kwhp1` double NOT NULL DEFAULT '0',
  `kwhp2` double NOT NULL DEFAULT '0',
  `kwhp3` double NOT NULL DEFAULT '0',
  `kwho1` double NOT NULL DEFAULT '0',
  `kwho2` double NOT NULL DEFAULT '0',
  `kwho3` double NOT NULL DEFAULT '0',
  `pkflg` int NOT NULL DEFAULT '0',
  `rkwhp1` double DEFAULT NULL,
  `rkwhp2` double DEFAULT NULL,
  `rkwhp3` double DEFAULT NULL,
  `rkwho1` double DEFAULT NULL,
  `rkwho2` double DEFAULT NULL,
  `rkwho3` double DEFAULT NULL,
  `kw1` double DEFAULT NULL,
  `kw2` double DEFAULT NULL,
  `kw3` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- --------------------------------------------------------

--
-- Table structure for table `websocket_connections`
--

CREATE TABLE `websocket_connections` (
  `id` int NOT NULL,
  `global_device_id` varchar(255) NOT NULL,
  `resource_id` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for table `application_types`
--
ALTER TABLE `application_types`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `billing_data`
--
ALTER TABLE `billing_data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `global_device_id` (`global_device_id`),
  ADD KEY `meter_datetime` (`meter_datetime`);

--
-- Indexes for table `commands`
--
ALTER TABLE `commands`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `companys`
--
ALTER TABLE `companys`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `device_communication_history`
--
ALTER TABLE `device_communication_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `global_device_id` (`global_device_id`),
  ADD KEY `meter_datetime` (`meter_datetime`);

--
-- Indexes for table `device_configuration`
--
ALTER TABLE `device_configuration`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `events`
--
ALTER TABLE `events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `global_device_id` (`global_device_id`),
  ADD KEY `event_datetime` (`event_datetime`);

--
-- Indexes for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`);

--
-- Indexes for table `feeders`
--
ALTER TABLE `feeders`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `instantaneous_data`
--
ALTER TABLE `instantaneous_data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `global_device_id` (`global_device_id`),
  ADD KEY `meter_datetime` (`meter_datetime`);

--
-- Indexes for table `kwh_readings`
--
ALTER TABLE `kwh_readings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `load_profile_data`
--
ALTER TABLE `load_profile_data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `global_device_id` (`global_device_id`),
  ADD KEY `meter_datetime` (`meter_datetime`);

--
-- Indexes for table `logs`
--
ALTER TABLE `logs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `meter_visuals`
--
ALTER TABLE `meter_visuals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `global_device_id` (`global_device_id`);

--
-- Indexes for table `migrations`
--
ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `monthly_billing_data`
--
ALTER TABLE `monthly_billing_data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `global_device_id` (`global_device_id`),
  ADD KEY `meter_datetime` (`meter_datetime`);

--
-- Indexes for table `oauth_auth_codes`
--
ALTER TABLE `oauth_auth_codes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `oauth_auth_codes_user_id_index` (`user_id`);

--
-- Indexes for table `oauth_personal_access_clients`
--
ALTER TABLE `oauth_personal_access_clients`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `permissions`
--
ALTER TABLE `permissions`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `permission_sections`
--
ALTER TABLE `permission_sections`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `raw_transfocure_log`
--
ALTER TABLE `raw_transfocure_log`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `readings`
--
ALTER TABLE `readings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `time_domain_graph_data`
--
ALTER TABLE `time_domain_graph_data`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `transaction_status`
--
ALTER TABLE `transaction_status`
  ADD PRIMARY KEY (`id`),
  ADD KEY `transactionid` (`transactionid`),
  ADD KEY `global_device_id` (`global_device_id`);

--
-- Indexes for table `transformers`
--
ALTER TABLE `transformers`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `trapping_event_setting`
--
ALTER TABLE `trapping_event_setting`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tr_current_logs`
--
ALTER TABLE `tr_current_logs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tr_kwh_logs`
--
ALTER TABLE `tr_kwh_logs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `websocket_connections`
--
ALTER TABLE `websocket_connections`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `application_types`
--
ALTER TABLE `application_types`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `billing_data`
--
ALTER TABLE `billing_data`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment (Not NULL) Unique-ID';

--
-- AUTO_INCREMENT for table `commands`
--
ALTER TABLE `commands`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `companys`
--
ALTER TABLE `companys`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `device_communication_history`
--
ALTER TABLE `device_communication_history`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Unique-ID';

--
-- AUTO_INCREMENT for table `device_configuration`
--
ALTER TABLE `device_configuration`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Unique-ID';

--
-- AUTO_INCREMENT for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `feeders`
--
ALTER TABLE `feeders`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `instantaneous_data`
--
ALTER TABLE `instantaneous_data`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment (Not NULL) Unique-ID';

--
-- AUTO_INCREMENT for table `kwh_readings`
--
ALTER TABLE `kwh_readings`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `load_profile_data`
--
ALTER TABLE `load_profile_data`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Unique-ID';

--
-- AUTO_INCREMENT for table `logs`
--
ALTER TABLE `logs`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `meter_visuals`
--
ALTER TABLE `meter_visuals`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Unique-ID', AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `monthly_billing_data`
--
ALTER TABLE `monthly_billing_data`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment (Not NULL) Unique-ID';

--
-- AUTO_INCREMENT for table `oauth_personal_access_clients`
--
ALTER TABLE `oauth_personal_access_clients`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `permission_sections`
--
ALTER TABLE `permission_sections`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `raw_transfocure_log`
--
ALTER TABLE `raw_transfocure_log`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `readings`
--
ALTER TABLE `readings`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `time_domain_graph_data`
--
ALTER TABLE `time_domain_graph_data`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transaction_status`
--
ALTER TABLE `transaction_status`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transformers`
--
ALTER TABLE `transformers`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `trapping_event_setting`
--
ALTER TABLE `trapping_event_setting`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tr_current_logs`
--
ALTER TABLE `tr_current_logs`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tr_kwh_logs`
--
ALTER TABLE `tr_kwh_logs`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `websocket_connections`
--
ALTER TABLE `websocket_connections`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;
COMMIT;

