CREATE TABLE "electrical_performance" (
snaplink text NOT NULL,
elec_perf_snapshot jsonb NULL,
    PRIMARY KEY (snaplink),
    FOREIGN KEY (snaplink) REFERENCES plant_record(snapkey)
);

First 3 rows:
snaplink    elec_perf_snapshot
----------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PV937101    {'grid': {'grid_voltage_v': None, 'grid_frequency_hz': 49.73, 'power_quality_idx': 0.467, 'reactive_power_var': 33, 'harmonic_distortion_pct': 4.01}, 'power': {'power_now_w': None, 'power_loss_w': None}, 'inverter': {'power_factor': 0.979, 'inverter_temp_c': 49.1, 'inverter_eff_pct': 98.43}, 'iv_curve': {'imp_now_a': 7.21, 'isc_now_a': 8.51, 'vmp_now_v': 36.98, 'voc_now_v': 43.32, 'imp_initial_a': 9.17, 'isc_initial_a': 9.09, 'shunt_res_ohm': 437.3, 'vmp_initial_v': 38.48, 'voc_initial_v': 49.74, 'series_res_ohm': '0.17 Ω', 'fill_factor_now': 0.71, 'fill_factor_initial': 0.773}, 'efficiency': {'eff_loss_pct': 2.32, 'soil_loss_pct': 13.41, 'instant_eff_pct': 18.54, 'spectral_mismatch': '100.50%', 'cumulative_deg_pct': 14.69}, 'energy_yield': {'availability_pct': 97.73, 'energy_yield_kwh': 15.64, 'performance_ratio': None, 'capacity_factor_pct': 27.47, 'specific_yield_kwh_kw': 5.18}}
PV945724    {'grid': {'grid_voltage_v': None, 'grid_frequency_hz': 49.6, 'power_quality_idx': 0.245, 'reactive_power_var': 65.1, 'harmonic_distortion_pct': 4.61}, 'power': {'power_now_w': 639.47, 'power_loss_w': 10.53}, 'inverter': {'power_factor': 0.985, 'inverter_temp_c': 51.2, 'inverter_eff_pct': 98.37}, 'iv_curve': {'imp_now_a': 7.3, 'isc_now_a': 8.41, 'vmp_now_v': 36.73, 'voc_now_v': 43.1, 'imp_initial_a': 9.93, 'isc_initial_a': 10.85, 'shunt_res_ohm': 880.7, 'vmp_initial_v': 38.25, 'voc_initial_v': None, 'series_res_ohm': '0.35 Ω', 'fill_factor_now': 0.788, 'fill_factor_initial': 0.775}, 'efficiency': {'eff_loss_pct': 1.7, 'soil_loss_pct': 11.27, 'instant_eff_pct': 19.95, 'spectral_mismatch': '99.60%', 'cumulative_deg_pct': 1.62}, 'energy_yield': {'availability_pct': 96.91, 'energy_yield_kwh': 340.32, 'performance_ratio': None, 'capacity_factor_pct': 25.37, 'specific_yield_kwh_kw': 6}}
PV596868    {'grid': {'grid_voltage_v': 220.8, 'grid_frequency_hz': 50.19, 'power_quality_idx': 0.26, 'reactive_power_var': None, 'harmonic_distortion_pct': 3.17}, 'power': {'power_now_w': None, 'power_loss_w': None}, 'inverter': {'power_factor': None, 'inverter_temp_c': 49, 'inverter_eff_pct': 97.74}, 'iv_curve': {'imp_now_a': None, 'isc_now_a': 8.39, 'vmp_now_v': 37.07, 'voc_now_v': 47.94, 'imp_initial_a': 9.49, 'isc_initial_a': 9.86, 'shunt_res_ohm': 998, 'vmp_initial_v': 36.44, 'voc_initial_v': None, 'series_res_ohm': '0.49 Ω', 'fill_factor_now': 0.764, 'fill_factor_initial': 0.812}, 'efficiency': {'eff_loss_pct': 3.5, 'soil_loss_pct': 10.87, 'instant_eff_pct': 17.82, 'spectral_mismatch': '98.70%', 'cumulative_deg_pct': 2.37}, 'energy_yield': {'availability_pct': 97.88, 'energy_yield_kwh': 1357.4, 'performance_ratio': None, 'capacity_factor_pct': 20.83, 'specific_yield_kwh_kw': 5.46}}
...


CREATE TABLE "environmental_conditions" (
snapref text NOT NULL,
env_snapshot jsonb NULL,
    PRIMARY KEY (snapref),
    FOREIGN KEY (snapref) REFERENCES plant_record(snapkey)
);

First 3 rows:
snapref    env_snapshot
---------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PV937101   {'irradiance': {'dhi_w_m2': 44.1, 'dni_w_m2': 169.3, 'ghi_w_m2': None, 'poa_irr_w_m2': 135.1}, 'atmospheric': {'uv_index': 1.1, 'cloud_cover_pct': 12.4, 'air_pressure_hpa': 1099.2, 'dust_density_kg_m3': 0.056, 'relative_humidity_pct': 76.1}, 'temperatures': {'cell_temp_c': 47.3, 'ambient_temp_c': 41.2}, 'wind_rain_snow': {'rain_mm': 41.3, 'wind_dir_deg': 249.3, 'snow_cover_pct': 24.8, 'wind_speed_m_s': 11.6}}
PV945724   {'irradiance': {'dhi_w_m2': 81.9, 'dni_w_m2': 686.2, 'ghi_w_m2': 556.4, 'poa_irr_w_m2': 500.1}, 'atmospheric': {'uv_index': 3.6, 'cloud_cover_pct': 93.8, 'air_pressure_hpa': 958.3, 'dust_density_kg_m3': 3.519, 'relative_humidity_pct': 50}, 'temperatures': {'cell_temp_c': 36.1, 'ambient_temp_c': 22.7}, 'wind_rain_snow': {'rain_mm': 36.2, 'wind_dir_deg': 29.8, 'snow_cover_pct': 59.7, 'wind_speed_m_s': 22.6}}
PV617932   {'irradiance': {'dhi_w_m2': 21.4, 'dni_w_m2': 346.6, 'ghi_w_m2': None, 'poa_irr_w_m2': 224.8}, 'atmospheric': {'uv_index': 11, 'cloud_cover_pct': 43.7, 'air_pressure_hpa': 965.8, 'dust_density_kg_m3': 3.875, 'relative_humidity_pct': 70.8}, 'temperatures': {'cell_temp_c': None, 'ambient_temp_c': -1.6}, 'wind_rain_snow': {'rain_mm': 28.7, 'wind_dir_deg': 231.5, 'snow_cover_pct': 37.5, 'wind_speed_m_s': 11.3}}
...


CREATE TABLE "panel_models" (
modkey text NOT NULL,
makertag text NULL,
pnlkind text NULL,
rated_w real NULL,
effpct real NULL,
degyrrate real NULL,
tempcoeff real NULL,
nomoptempc text NULL,
    PRIMARY KEY (modkey)
);

First 3 rows:
modkey     makertag    pnlkind      rated_w    effpct    degyrrate    tempcoeff  nomoptempc
---------  ----------  ---------  ---------  --------  -----------  -----------  ------------
Model-102  Longi       Mono-PERC        650     20.86         0.36       -0.389  45.7 °C
Model-892              HJT              650     21.65         0.88       -0.446  45.5 °C
Model-677  Longi       Poly-PERC        450     21.93         0.94      nan      46.7 °C
...


CREATE TABLE "plants" (
sitekey text NOT NULL,
sitelabel text NULL,
cap_mw real NULL,
goliveon date NULL,
modhook text NULL,
tiltdeg real NULL,
azmdeg real NULL,
recyclenote text NULL,
docstate text NULL,
warrstate text NULL,
warrclaims bigint NULL,
insurstate text NULL,
complyflag text NULL,
envtag text NULL,
    PRIMARY KEY (sitekey),
    FOREIGN KEY (modhook) REFERENCES panel_models(modkey)
);

First 3 rows:
sitekey    sitelabel                     cap_mw  goliveon    modhook      tiltdeg    azmdeg  recyclenote     docstate    warrstate      warrclaims  insurstate    complyflag     envtag
---------  --------------------------  --------  ----------  ---------  ---------  --------  --------------  ----------  -----------  ------------  ------------  -------------  --------
SP9227     Solar Plant West Davidport    257.58  2018-02-28  Model-102        1.2     169.8                  Missing     Claimed                 2  Covered       Compliant      High
SP6740     Solar Plant Dillonmouth       437.71  2023-08-06  Model-892       13.1     158.8  In Development  Missing     Active                  4  Covered       Non-Compliant  Low
SP7738     Solar Plant North Xavier      397.96  2022-06-18  Model-677       27.1     223    In Development  Missing     Claimed                 2  Expired       Non-Compliant  High
...


CREATE TABLE "mechanical_condition" (
snapmk text NOT NULL,
mech_health_snapshot jsonb NULL,
    PRIMARY KEY (snapmk),
    FOREIGN KEY (snapmk) REFERENCES plant_record(snapkey)
);

First 3 rows:
snapmk    mech_health_snapshot
--------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PV937101  {'tracker': {'tracker_state': 'Maintenance', 'tracker_angle_deg': 20.5}, 'module_surface': {'lid_status': 'Unknown', 'pid_severity': None, 'hotspot_count': 1, 'glass_condition': 'Clear', 'busbar_corrosion': 'Severe', 'microcrack_count': 7, 'backsheet_condition': 'Poor', 'delamination_status': 'Minor', 'snail_trail_severity': 'Light', 'encapsulant_yellowing': 'Severe'}, 'mount_cleaning': {'last_clean_date': '2025-01-14', 'cleaning_cycle_days': 38, 'mount_structure_status': 'Stable'}, 'electrical_integrity': {'cable_condition': 'Good', 'grounding_status': 'Normal', 'bypass_diode_status': 'Partial', 'connector_condition': 'Fair', 'junction_box_condition': 'Good'}}
PV945724  {'tracker': {'tracker_state': 'Error', 'tracker_angle_deg': -37.5}, 'module_surface': {'lid_status': 'Stabilized', 'pid_severity': 'Low', 'hotspot_count': 3, 'glass_condition': 'Damaged', 'busbar_corrosion': 'Severe', 'microcrack_count': 3, 'backsheet_condition': 'Fair', 'delamination_status': 'Major', 'snail_trail_severity': 'Light', 'encapsulant_yellowing': None}, 'mount_cleaning': {'last_clean_date': '2025-01-23', 'cleaning_cycle_days': 52, 'mount_structure_status': 'Unstable'}, 'electrical_integrity': {'cable_condition': 'Fair', 'grounding_status': 'Failed', 'bypass_diode_status': 'Normal', 'connector_condition': 'Poor', 'junction_box_condition': 'Fair'}}
PV227567  {'tracker': {'tracker_state': 'Normal', 'tracker_angle_deg': 21.3}, 'module_surface': {'lid_status': 'Stabilized', 'pid_severity': 'Low', 'hotspot_count': None, 'glass_condition': 'Damaged', 'busbar_corrosion': 'Visible', 'microcrack_count': 5, 'backsheet_condition': 'Good', 'delamination_status': 'Major', 'snail_trail_severity': 'Light', 'encapsulant_yellowing': None}, 'mount_cleaning': {'last_clean_date': '2025-02-05', 'cleaning_cycle_days': 17, 'mount_structure_status': 'Check Required'}, 'electrical_integrity': {'cable_condition': None, 'grounding_status': 'Check Required', 'bypass_diode_status': 'Partial', 'connector_condition': 'Fair', 'junction_box_condition': 'Good'}}
...


CREATE TABLE "plant_panel_model" (
sitelink text NOT NULL,
modlink text NOT NULL,
    PRIMARY KEY (sitelink, modlink),
    FOREIGN KEY (sitelink) REFERENCES plants(sitekey),
    FOREIGN KEY (modlink) REFERENCES panel_models(modkey)
);

First 3 rows:
sitelink    modlink
----------  ---------
SP9227      Model-102
SP6740      Model-892
SP7738      Model-677
...


CREATE TABLE "plant_record" (
snapkey text NOT NULL,
sitetie text NULL,
snapts timestamp without time zone NULL,
    PRIMARY KEY (snapkey),
    FOREIGN KEY (sitetie) REFERENCES plants(sitekey)
);

First 3 rows:
snapkey    sitetie    snapts
---------  ---------  -------------------
PV937101   SP9227     2018-08-03 12:21:20
PV945724   SP6740     2023-03-05 14:10:48
PV227567   SP7738     2023-01-30 02:55:15
...


CREATE TABLE "operational_metrics" (
snapops text NOT NULL,
mtbfh real NULL,
mttrh real NULL,
maintcost real NULL,
cleancost real NULL,
replcost real NULL,
revloss text NULL,
optpot text NULL,
    PRIMARY KEY (snapops),
    FOREIGN KEY (snapops) REFERENCES plant_record(snapkey)
);

First 3 rows:
snapops      mtbfh    mttrh    maintcost    cleancost    replcost  revloss     optpot
---------  -------  -------  -----------  -----------  ----------  ----------  --------
PV937101     nan        8.8       178.82      1034.89    31529.1   $13,375.59  Medium
PV945724    3172.1     31.5      9549.83      2957.08    15984.1   $17,065.19  High
PV227567    9686.8     21.7      9298.61      1934.67     3604.59  $14,035.91  Low
...


CREATE TABLE "inspection" (
inspectmode text NOT NULL,
inspectres text NULL,
inspectdt date NULL,
maintsched text NULL,
dqscore real NULL,
    PRIMARY KEY (inspectmode)
);

First 3 rows:
inspectmode    inspectres    inspectdt    maintsched      dqscore
-------------  ------------  -----------  ------------  ---------
Visual         Minor Issues  2024-08-24   Delayed            97.7
IR Thermal     Major Issues  2024-12-23   Overdue            19.9
IV Curve       Pass          2025-02-13   Delayed            71.1
...


CREATE TABLE "alert" (
snapalrt text NOT NULL,
alrtstate text NULL,
alrtcnt text NULL,
maintprio text NULL,
replprio text NULL,
    PRIMARY KEY (snapalrt),
    FOREIGN KEY (snapalrt) REFERENCES plant_record(snapkey)
);

First 3 rows:
snapalrt    alrtstate    alrtcnt    maintprio    replprio
----------  -----------  ---------  -----------  ----------
PV937101                 6 events   High         High
PV945724    Warning      2 events   High         High
PV227567                 9 events                High
...
