const mysql = require("mysql");
require("dotenv").config();
const db = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

db.connect((err) => {
  if (err) {
    console.error("Error connecting to MySQL:", err);
    process.exit(1);
  }
  console.log("MySQL connected with the DB:" + process.env.DB_NAME);
  
  const tables = [
    {
      name: "users",
      schema: `
        CREATE TABLE IF NOT EXISTS users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(100) NOT NULL,
          mobile VARCHAR(15) NOT NULL UNIQUE,
          district VARCHAR(100) NOT NULL,
          state VARCHAR(100) NOT NULL,
          password VARCHAR(100) NOT NULL
        )
      `,
    },
    {
      name: "orders",
      schema: `
        CREATE TABLE IF NOT EXISTS orders (
          id INT AUTO_INCREMENT PRIMARY KEY,
          enquiry_id VARCHAR(20) NOT NULL UNIQUE,
          name VARCHAR(100) NOT NULL,
          mobile VARCHAR(15) NOT NULL,
          order_date DATETIME NOT NULL,
          total_amount DECIMAL(10, 2) NOT NULL,
          order_details JSON,
          status VARCHAR(20) DEFAULT 'pending',
          payment_proof VARCHAR(255) DEFAULT NULL,
          payment_status ENUM('pending', 'pending_verification', 'verified', 'rejected') DEFAULT 'pending',
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )
      `,
    },
    {
      name: "crackers",
      schema: `
        CREATE TABLE IF NOT EXISTS crackers (
          id INT AUTO_INCREMENT PRIMARY KEY,
          code VARCHAR(50) NOT NULL,
          product_name VARCHAR(255) NOT NULL,
          content VARCHAR(255),
          actual_price DECIMAL(10, 2),
          price DECIMAL(10, 2),
          quantity INT,
          photo_url VARCHAR(500)
        )
      `,
    },
    {
      name: "chit_customers",
      schema: `
        CREATE TABLE IF NOT EXISTS chit_customers (
          id INT AUTO_INCREMENT PRIMARY KEY,
          chit_id VARCHAR(20) NOT NULL,
          name VARCHAR(100) NOT NULL,
          mobile VARCHAR(15) NOT NULL,
          scheme_plan VARCHAR(50) NOT NULL,
          join_date DATETIME NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `,
    },
    {
      name: "chit_payments",
      schema: `
        CREATE TABLE IF NOT EXISTS chit_payments (
          id INT AUTO_INCREMENT PRIMARY KEY,
          chit_id VARCHAR(20) NOT NULL,
          payment_month TEXT NOT NULL,
          payment_date DATE NOT NULL,
          payment_status ENUM('pending', 'paid') DEFAULT 'paid',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `,
    },
    {
      name: "chit_schemes",
      schema: `
        CREATE TABLE IF NOT EXISTS chit_schemes (
          id INT AUTO_INCREMENT PRIMARY KEY,
          plan VARCHAR(255) NOT NULL,
          installment_to_be_paid DECIMAL(10, 2) NOT NULL,
          you_pay DECIMAL(10, 2) NOT NULL,
          you_get DECIMAL(10, 2) NOT NULL
        )
      `,
    },
    {
      name: "uc_payments",
      schema: `
        CREATE TABLE IF NOT EXISTS uc_payments (
          id INT AUTO_INCREMENT PRIMARY KEY, 
          chit_id VARCHAR(20) NOT NULL, 
          scheme_plan VARCHAR(255) NOT NULL, 
          payment_month DATE NOT NULL, 
          screenshot_url VARCHAR(255), 
          payment_status ENUM('pending', 'confirmed', 'failed', 'rejected') DEFAULT 'pending',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
          UNIQUE KEY (chit_id, payment_month)
        );
      `,
    },
    {
      name: "payment_details",
      schema: `
        CREATE TABLE IF NOT EXISTS payment_details (
          id INT AUTO_INCREMENT PRIMARY KEY,
          qr_name VARCHAR(255) NOT NULL,
          qr_code_url VARCHAR(255) NOT NULL,
          account_name VARCHAR(255) NOT NULL,
          account_number VARCHAR(255) NOT NULL,
          account_type VARCHAR(50) NOT NULL,
          bank_name VARCHAR(255) NOT NULL,
          ifsc_code VARCHAR(20) NOT NULL
        );
      `,
    },
    {
      name: "chit_groups",
      schema: `
        CREATE TABLE IF NOT EXISTS chit_groups (
          id INT AUTO_INCREMENT PRIMARY KEY,
          chit_id VARCHAR(20) NOT NULL,
          group_name VARCHAR(255) NOT NULL,
          total_members INT NOT NULL,
          group_leader VARCHAR(100),
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
      `,
    },
    {
      name: "chit_payment_history",
      schema: `
        CREATE TABLE IF NOT EXISTS chit_payment_history (
          id INT AUTO_INCREMENT PRIMARY KEY,
          chit_id VARCHAR(20) NOT NULL,
          payment_date DATE NOT NULL,
          amount_paid DECIMAL(10, 2) NOT NULL,
          payment_mode VARCHAR(50),
          status ENUM('paid', 'pending', 'rejected') DEFAULT 'pending',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
      `,
    },
  ];

  // Function to create all tables
  tables.forEach((table) => {
    db.query(table.schema, (err, result) => {
      if (err) {
        console.error(`Error creating table ${table.name}:`, err);
      }
    });
  });
});

module.exports = { db };
