server.ts

import express, { Request, Response } from "express";
import { Pool } from "pg";
import fs from "fs";
import path from "path";
import "dotenv/config";

const app = express();
const port = 3000;

app.use(express.json());
app.use(express.static("public"));

let caCert: string;
try {
  caCert = fs.readFileSync(path.join(__dirname, "ca.pem"), "utf8");
  console.log("CA certificate loaded successfully.");
} catch (error: any) {
  console.error("Failed to load CA certificate:", error.message);
  process.exit(1);
}

const pool = new Pool({
  connectionString: `postgres://${process.env.UNAME}:${process.env.PWD}@postgres-bs-bebb.l.aivencloud.com:21759/defaultdb`,
  ssl: {
    ca: caCert,
    rejectUnauthorized: true,
    checkServerIdentity: () => undefined,
  },
});

interface Entry {
  sl_no: number;
  work_completed: string;
  from_time: string;
  to_time: string;
}

interface SaveRequestBody {
  date: string;
  technology: string;
  entries: Entry[];
}

pool.connect((err, client, release) => {
  if (err) {
    console.error("Database connection error:", err.message);
    process.exit(1);
  }
  console.log("Successfully connected to PostgreSQL database.");
  release();
});

app.post(
  "/save",
  async (req: Request<{}, {}, SaveRequestBody>, res: Response) => {
    const { date, technology, entries } = req.body;
    const client = await pool.connect();
    try {
      await client.query("BEGIN");
      for (const entry of entries) {
        await client.query(
          "INSERT INTO daily_reports (report_date, technology, sl_no, work_completed, from_time, to_time) VALUES ($1, $2, $3, $4, $5, $6)",
          [
            date,
            technology,
            entry.sl_no,
            entry.work_completed,
            entry.from_time,
            entry.to_time,
          ]
        );
      }
      await client.query("COMMIT");
      res.json({ success: true });
    } catch (error: any) {
      await client.query("ROLLBACK");
      res.status(500).json({ error: error.message });
    } finally {
      client.release();
    }
  }
);

app.get("/fetch", async (req: Request, res: Response) => {
  const { date } = req.query;
  try {
    const result = await pool.query(
      "SELECT * FROM daily_reports WHERE report_date = $1 ORDER BY sl_no ASC",
      [date]
    );
    res.json(result.rows);
  } catch (error: any) {
    res.status(500).json({ error: error.message });
  }
});

app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});