unit uPesadoDB;

{ Acceso a datos (FireDAC + MariaDB) para el control de pesado de lotes.
  Toma los parámetros de conexión de TPesadoConfig (almacenados cifrados en ControlPesado.cfg). }

interface

uses
  System.SysUtils, System.Classes, Data.DB,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt, FireDAC.DApt.Intf, FireDAC.Phys, FireDAC.Phys.Intf,
  FireDAC.Phys.MySQL, FireDAC.Phys.MySQLDef, FireDAC.UI.Intf, FireDAC.VCLUI.Wait,
  FireDAC.Comp.Client, FireDAC.Comp.DataSet,
  uPesadoConfig;

type
  TPesadaRegistro = record
    IdStockEnvasado: Integer;
    IdLote: Integer;
    IdArticulo: Integer;
    PesoTeoricoKg: Double;
    PesoRealKg: Double;
    PesoBrutoKg: Double;
    DesviacionPorc: Double;
    Conforme: Boolean;
  end;

  TPesadoDB = class
  private
    FConn: TFDConnection;
    FDriverLink: TFDPhysMySQLDriverLink;
    FQryLotes: TFDQuery;
    procedure ConfigurarVendorLib;
  public
    constructor Create(const AConfig: TPesadoConfig);
    destructor Destroy; override;
    procedure AplicarConfiguracion(const AConfig: TPesadoConfig);
    procedure Conectar;
    procedure AsegurarEsquema;
    procedure RefrescarLotes;
    function ContarPesadas(AIdStockEnvasado: Integer; AConformes: Boolean): Integer;
    procedure RegistrarPesada(const APesada: TPesadaRegistro);
    function FinalizarLote(AIdStockEnvasado: Integer): Integer;
    function LoteSigueAbierto(AIdStockEnvasado: Integer): Boolean;
    class function ProbarConexion(const AConfig: TPesadoConfig; out AMensaje: string): Boolean;
    property Conexion: TFDConnection read FConn;
    property QryLotes: TFDQuery read FQryLotes;
  end;

implementation

uses
  Winapi.Windows;

const
  SQL_CREATE_PESADAS =
    'CREATE TABLE IF NOT EXISTS `ge_stock_envasado_pesadas` (' +
    '  `id` int(11) NOT NULL AUTO_INCREMENT,' +
    '  `id_stock_envasado` int(11) NOT NULL COMMENT ''FK a ge_stock_envasado(id)'',' +
    '  `id_lote` int(11) NOT NULL COMMENT ''FK a ge_lotes(id)'',' +
    '  `id_articulo` int(11) NOT NULL COMMENT ''FK a ge_articulos(id)'',' +
    '  `peso_teorico` decimal(10,3) NOT NULL COMMENT ''Peso nominal del artículo en kg'',' +
    '  `peso_real` decimal(10,3) NOT NULL COMMENT ''Peso neto leído en kg'',' +
    '  `peso_bruto` decimal(10,3) NOT NULL DEFAULT 0.000 COMMENT ''Peso bruto leído en kg'',' +
    '  `desviacion_porc` decimal(8,2) NOT NULL DEFAULT 0.00 COMMENT ''Desviación porcentual'',' +
    '  `conforme` int(1) NOT NULL DEFAULT 1 COMMENT ''1 = Correcto, 0 = Rechazado'',' +
    '  `puesto` varchar(50) DEFAULT NULL COMMENT ''Equipo de pesaje'',' +
    '  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),' +
    '  PRIMARY KEY (`id`),' +
    '  KEY `idx_pesadas_stock_conforme` (`id_stock_envasado`,`conforme`),' +
    '  KEY `fk_pesadas_lote` (`id_lote`),' +
    '  KEY `fk_pesadas_articulo` (`id_articulo`),' +
    '  CONSTRAINT `fk_pesadas_stock_envasado` FOREIGN KEY (`id_stock_envasado`) REFERENCES `ge_stock_envasado` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,' +
    '  CONSTRAINT `fk_pesadas_lote` FOREIGN KEY (`id_lote`) REFERENCES `ge_lotes` (`id`) ON UPDATE CASCADE,' +
    '  CONSTRAINT `fk_pesadas_articulo` FOREIGN KEY (`id_articulo`) REFERENCES `ge_articulos` (`id`) ON UPDATE CASCADE' +
    ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT=''Registro individual de pesadas por lote envasado''';

  SQL_LOTES_PENDIENTES =
    'SELECT se.id AS id_stock_envasado, se.id_lote, se.id_articulo, ' +
    '       l.codigo_antiguo, l.fecha AS fecha_lote, ' +
    '       a.descripcion AS articulo, ' +
    '       COALESCE(fp.descripcion, '''') AS formato, ' +
    '       COALESCE(a.peso, 0) AS peso_teorico, ' +
    '       COALESCE(se.cantidad_producida, 0) AS cantidad_producida, ' +
    '       (SELECT COUNT(*) FROM ge_stock_envasado_pesadas p ' +
    '         WHERE p.id_stock_envasado = se.id AND p.conforme = 1) AS pesadas_ok ' +
    'FROM ge_stock_envasado se ' +
    'INNER JOIN ge_lotes l ON l.id = se.id_lote ' +
    'INNER JOIN ge_articulos a ON a.id = se.id_articulo ' +
    'LEFT JOIN ge_formatos_presentacion fp ON fp.id = se.id_formato_presentacion ' +
    'WHERE COALESCE(se.finalizado, 0) = 0 ' +
    'ORDER BY l.codigo_antiguo, a.descripcion';

{ TPesadoDB }

constructor TPesadoDB.Create(const AConfig: TPesadoConfig);
begin
  inherited Create;
  FDriverLink := TFDPhysMySQLDriverLink.Create(nil);
  FConn := TFDConnection.Create(nil);
  FConn.LoginPrompt := False;
  FConn.ResourceOptions.AutoReconnect := True;
  FConn.FormatOptions.DefaultParamDataType := ftWideString;
  FConn.FormatOptions.StrsEmpty2Null := False;
  FQryLotes := TFDQuery.Create(nil);
  FQryLotes.Connection := FConn;
  FQryLotes.FetchOptions.Mode := fmAll;
  ConfigurarVendorLib;
  AplicarConfiguracion(AConfig);
end;

destructor TPesadoDB.Destroy;
begin
  FreeAndNil(FQryLotes);
  if Assigned(FConn) then
    FConn.Connected := False;
  FreeAndNil(FConn);
  FreeAndNil(FDriverLink);
  inherited;
end;

procedure TPesadoDB.ConfigurarVendorLib;
var
  LDir, LLib: string;
begin
  LDir := IncludeTrailingPathDelimiter(ExtractFilePath(ParamStr(0)));
  if DirectoryExists(LDir + 'librerias') then
  begin
    SetDllDirectory(PChar(LDir + 'librerias'));
    LLib := LDir + 'librerias\libmysql.dll';
  end
  else
    LLib := LDir + 'libmysql.dll';

  if not FileExists(LLib) then
  begin
    if FileExists('C:\GESTIGEST\librerias\libmysql.dll') then
      LLib := 'C:\GESTIGEST\librerias\libmysql.dll'
    else if FileExists('C:\GESTIGEST\libmysql.dll') then
      LLib := 'C:\GESTIGEST\libmysql.dll'
    else
      LLib := '';
  end;
  if LLib <> '' then
    FDriverLink.VendorLib := LLib;
end;

procedure TPesadoDB.AplicarConfiguracion(const AConfig: TPesadoConfig);
begin
  if FQryLotes.Active then
    FQryLotes.Close;
  FConn.Connected := False;
  FConn.Params.Clear;
  FConn.Params.Values['DriverID'] := 'MySQL';
  FConn.Params.Values['Server'] := AConfig.DBServer;
  FConn.Params.Values['Port'] := IntToStr(AConfig.DBPort);
  FConn.Params.Values['Database'] := AConfig.DBDatabase;
  FConn.Params.Values['User_Name'] := AConfig.DBUser;
  FConn.Params.Values['Password'] := AConfig.DBPassword;
  FConn.Params.Values['CharacterSet'] := 'utf8mb4';
  FConn.Params.Values['StringFormat'] := 'Unicode';
  FConn.Params.Values['TinyIntFormat'] := 'Int';
  FConn.Params.Values['Compress'] := 'True';
  FConn.Params.Values['LoginTimeout'] := '5';
end;

class function TPesadoDB.ProbarConexion(const AConfig: TPesadoConfig; out AMensaje: string): Boolean;
var
  LTestDB: TPesadoDB;
begin
  Result := False;
  AMensaje := '';
  LTestDB := TPesadoDB.Create(AConfig);
  try
    try
      LTestDB.Conectar;
      AMensaje := Format('Conexión correcta con %s @ %s:%d (usuario: %s)',
        [AConfig.DBDatabase, AConfig.DBServer, AConfig.DBPort, AConfig.DBUser]);
      Result := True;
    except
      on E: Exception do
        AMensaje := 'Error de conexión a BD: ' + E.Message;
    end;
  finally
    LTestDB.Free;
  end;
end;

procedure TPesadoDB.Conectar;
begin
  FConn.Connected := True;
end;

procedure TPesadoDB.AsegurarEsquema;
begin
  FConn.ExecSQL(SQL_CREATE_PESADAS);
end;

procedure TPesadoDB.RefrescarLotes;
begin
  FQryLotes.DisableControls;
  try
    FQryLotes.Close;
    FQryLotes.SQL.Text := SQL_LOTES_PENDIENTES;
    FQryLotes.Open;
  finally
    FQryLotes.EnableControls;
  end;
end;

function TPesadoDB.ContarPesadas(AIdStockEnvasado: Integer; AConformes: Boolean): Integer;
var
  LQry: TFDQuery;
begin
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := FConn;
    LQry.SQL.Text := 'SELECT COUNT(*) AS total FROM ge_stock_envasado_pesadas ' +
      'WHERE id_stock_envasado = :id AND conforme = :conf';
    LQry.ParamByName('id').AsInteger := AIdStockEnvasado;
    if AConformes then
      LQry.ParamByName('conf').AsInteger := 1
    else
      LQry.ParamByName('conf').AsInteger := 0;
    LQry.Open;
    Result := LQry.FieldByName('total').AsInteger;
  finally
    LQry.Free;
  end;
end;

procedure TPesadoDB.RegistrarPesada(const APesada: TPesadaRegistro);
var
  LQry: TFDQuery;
  LPuesto: string;
begin
  LPuesto := GetEnvironmentVariable('COMPUTERNAME');
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := FConn;
    LQry.SQL.Text :=
      'INSERT INTO ge_stock_envasado_pesadas ' +
      '(id_stock_envasado, id_lote, id_articulo, peso_teorico, peso_real, peso_bruto, ' +
      ' desviacion_porc, conforme, puesto) VALUES ' +
      '(:id_stock, :id_lote, :id_articulo, :teorico, :real, :bruto, :desv, :conforme, :puesto)';
    LQry.ParamByName('id_stock').AsInteger := APesada.IdStockEnvasado;
    LQry.ParamByName('id_lote').AsInteger := APesada.IdLote;
    LQry.ParamByName('id_articulo').AsInteger := APesada.IdArticulo;
    LQry.ParamByName('teorico').AsFloat := APesada.PesoTeoricoKg;
    LQry.ParamByName('real').AsFloat := APesada.PesoRealKg;
    LQry.ParamByName('bruto').AsFloat := APesada.PesoBrutoKg;
    LQry.ParamByName('desv').AsFloat := APesada.DesviacionPorc;
    if APesada.Conforme then
      LQry.ParamByName('conforme').AsInteger := 1
    else
      LQry.ParamByName('conforme').AsInteger := 0;
    LQry.ParamByName('puesto').DataType := ftWideString;
    LQry.ParamByName('puesto').AsWideString := Copy(LPuesto, 1, 50);
    LQry.ExecSQL;
  finally
    LQry.Free;
  end;
end;

function TPesadoDB.LoteSigueAbierto(AIdStockEnvasado: Integer): Boolean;
var
  LQry: TFDQuery;
begin
  LQry := TFDQuery.Create(nil);
  try
    LQry.Connection := FConn;
    LQry.SQL.Text := 'SELECT COALESCE(finalizado, 0) AS finalizado FROM ge_stock_envasado WHERE id = :id';
    LQry.ParamByName('id').AsInteger := AIdStockEnvasado;
    LQry.Open;
    Result := (not LQry.IsEmpty) and (LQry.FieldByName('finalizado').AsInteger = 0);
  finally
    LQry.Free;
  end;
end;

function TPesadoDB.FinalizarLote(AIdStockEnvasado: Integer): Integer;
var
  LQry: TFDQuery;
begin
  FConn.StartTransaction;
  try
    LQry := TFDQuery.Create(nil);
    try
      LQry.Connection := FConn;
      // Bloqueo de la fila para evitar cierres simultáneos desde dos puestos
      LQry.SQL.Text := 'SELECT id FROM ge_stock_envasado WHERE id = :id FOR UPDATE';
      LQry.ParamByName('id').AsInteger := AIdStockEnvasado;
      LQry.Open;
      LQry.Close;

      LQry.SQL.Text := 'SELECT COUNT(*) AS total FROM ge_stock_envasado_pesadas ' +
        'WHERE id_stock_envasado = :id AND conforme = 1';
      LQry.ParamByName('id').AsInteger := AIdStockEnvasado;
      LQry.Open;
      Result := LQry.FieldByName('total').AsInteger;
      LQry.Close;

      LQry.SQL.Text :=
        'UPDATE ge_stock_envasado ' +
        'SET cantidad_producida = :total, ' +
        '    cantidad_unidades = :total, ' +
        '    estado_fabricacion = ''ENVASADO'', ' +
        '    finalizado = 1, ' +
        '    updated_at = NOW() ' +
        'WHERE id = :id';
      LQry.ParamByName('total').AsFloat := Result;
      LQry.ParamByName('id').AsInteger := AIdStockEnvasado;
      LQry.ExecSQL;
    finally
      LQry.Free;
    end;
    FConn.Commit;
  except
    FConn.Rollback;
    raise;
  end;
end;

end.
