import Treelogy from '../models/treelogy.js';
import Client from '../models/client.js';
import Dbname from '../models/dbname.js';
import sequelize from '../config/config.js';
import { successResponse, errorResponse } from '../utils/response.js';
import { getRoleNames } from '../middleware/auth.js';
import { createProjectDatabase } from '../utils/dbManager.js';
import { fileURLToPath } from 'url';
import path from 'path';
import fs from 'fs';
import { createAndApplySchema } from './treesController.js';


const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const schemaPath = path.join(__dirname, '../config/schema.sql');
const schemaSQL = fs.readFileSync(schemaPath, 'utf8');



// Function to filter unsupported lines
function filterSchema(schemaContent) {
  // Add more patterns if needed
  const unsupportedPatterns = [
    /^SET /i, // any line starting with SET
    /transaction_timeout/i, // specific line containing transaction_timeout
    /search_path/i, // optional: Postgres search_path lines
  ];

  return schemaContent
    .split('\n')
    .filter(line => !unsupportedPatterns.some(pattern => pattern.test(line)))
    .join('\n');
}



export async function createTreelogy(req, res) {
  const t = await sequelize.transaction();
  try {
    const {
      treelogy_code,
      treelogy_name,
      landmark,
      dbname,
      client_id,
      status = 'Y',
      createDbMap = false,
      locked = 'N',
      db_status = 0
    } = req.body;

    if (!treelogy_code || !treelogy_name || !landmark || !dbname || !client_id) {
      await t.rollback();
      return errorResponse(res, 400, 'Missing required fields');
    }

    // Get role names dynamically
    const roleNames = await getRoleNames();
    const CLIENT_ADMIN = roleNames.find(name => name === 'CLIENT_ADMIN');

    // For CLIENT_ADMIN, ensure they can only create projects under their own client
    if (req.user?.role_name === CLIENT_ADMIN && req.user?.client_id !== client_id) {
      await t.rollback();
      return errorResponse(res, 403, 'You can only create projects under your own client');
    }

    const client = await Client.findByPk(client_id);
    if (!client) {
      await t.rollback();
      return errorResponse(res, 404, 'Client not found');
    }

    const treelogy = await Treelogy.create(
      { treelogy_code, treelogy_name, landmark, dbname, client_id, status },
      { transaction: t }
    );

    if (createDbMap) {
      await Dbname.create(
        {
          treelogy_id: treelogy.treelogy_id || treelogy.id,
          dbname,
          locked,
          status: db_status
        },
        { transaction: t }
      );
    }

    // Create project database automatically
    try {
      // await createProjectDatabase(dbname);
      await createAndApplySchema(dbname)
      console.log(`Project database ${dbname} created successfully`);
    } catch (dbError) {
      console.error(`Error creating project database ${dbname}:`, dbError);
      // Don't fail the entire operation if database creation fails
      // The database can be created later when first survey is submitted
    }

    await t.commit();
    return successResponse(res, 201, 'Treelogy created successfully', treelogy);
  } catch (error) {
    await t.rollback();
    return errorResponse(res, 500, 'Error creating treelogy', error.message);
  }
}







// export const createAndApplySchema = async (dbName) => {
//   // const { dbName } = req.body;
//   // if (!dbName) return res.status(400).json({ error: 'dbName is required' });
//   const rootSequelize = sequelize
//   try {
//     // Step 1: Create the database
//     await rootSequelize.query(`CREATE DATABASE "${dbName}";`);
//     console.log(`✅ Database '${dbName}' created successfully!`);

//     // Step 2: Read and filter schema
//     const originalSchema = fs.readFileSync(schemaPath, 'utf8');
//     const filteredSchema = filterSchema(originalSchema);

//     // Step 3: Apply schema using native pg client
//     const client = new Client({
//       user: 'postgres',
//       password: '123456',
//       host: 'localhost',
//       database: dbName,
//       port: 5432,
//     });
//     await client.connect();

//     await client.query(filteredSchema);
//     console.log(`✅ Schema applied successfully to '${dbName}'`);

//     await client.end();
//     res.status(200).json({ message: `Database '${dbName}' created & schema applied successfully!` });
//   } catch (err) {
//     console.error('❌ Error:', err.message);
//     res.status(500).json({ error: err.message });
//   } finally {
//     await rootSequelize.close();
//   }
// };


export async function listTreelogies(req, res) {
  try {
    const { role_name, client_id, login_id } = req.user || {};

    // Get role names dynamically
    const roleNames = await getRoleNames();
    const SUPER_ADMIN = roleNames.find(name => name === 'SUPER_ADMIN');
    const CLIENT_ADMIN = roleNames.find(name => name === 'CLIENT_ADMIN');
    const PROJECT_USER = roleNames.find(name => name === 'PROJECT_USER');

    let where = {};
    let include = [{ model: Client, attributes: ['id', 'client_name'] }];

    if (role_name === SUPER_ADMIN) {
      // SUPER_ADMIN can see all projects
      // No filtering needed
    } else if (role_name === CLIENT_ADMIN && client_id) {
      // CLIENT_ADMIN can only see their client's projects
      where.client_id = client_id;
    } else if (role_name === PROJECT_USER) {
      // PROJECT_USER can only see projects they have access to via mapping or direct assignment
      const Mapping = (await import('../models/mapping.js')).default;
      const maps = await Mapping.findAll({ where: { login_id } });
      const mappedIds = maps.map(m => m.treelogy_id).filter(Boolean);

      // Also check if user has direct treelogy_id assignment
      const directProject = await Treelogy.findOne({ where: { treelogy_id: req.user?.treelogy_id } });
      if (directProject && !mappedIds.includes(directProject.treelogy_id)) {
        mappedIds.push(directProject.treelogy_id);
      }

      if (mappedIds.length > 0) {
        where.treelogy_id = mappedIds;
      } else {
        // No projects accessible
        return successResponse(res, 200, 'OK', []);
      }
    } else if (role_name === 'QC_USER' || role_name === 'QC_USER_2') {
      // QC_USER and QC_USER_2 should not have access to projects list - they only need dashboard and map access
      return successResponse(res, 200, 'OK', []);
    } else {
      // Unknown role - no access
      return successResponse(res, 200, 'OK', []);
    }

    const rows = await Treelogy.findAll({
      where,
      include,
      order: [['treelogy_id', 'DESC']],
    });
    return successResponse(res, 200, 'OK', rows);
  } catch (error) {
    return errorResponse(res, 500, 'Error listing treelogies', error.message);
  }
}

export async function getTreelogyById(req, res) {
  try {
    const row = await Treelogy.findOne({
      where: { treelogy_id: req.params.id },
      include: [{ model: Client, attributes: ['id', 'client_name'] }],
    });
    if (!row) return errorResponse(res, 404, 'Treelogy not found');
    return successResponse(res, 200, 'OK', row);
  } catch (error) {
    return errorResponse(res, 500, 'Error fetching treelogy', error.message);
  }
}

export async function updateTreelogy(req, res) {
  const t = await sequelize.transaction();
  try {
    const { role_name, client_id } = req.user || {};

    // Get role names dynamically
    const roleNames = await getRoleNames();
    const SUPER_ADMIN = roleNames.find(name => name === 'SUPER_ADMIN');
    const CLIENT_ADMIN = roleNames.find(name => name === 'CLIENT_ADMIN');

    // Check permissions
    if (role_name !== SUPER_ADMIN && role_name !== CLIENT_ADMIN) {
      await t.rollback();
      return errorResponse(res, 403, 'Insufficient permissions to update projects');
    }

    const row = await Treelogy.findOne({
      where: { treelogy_id: req.params.id },
      transaction: t
    });
    if (!row) {
      await t.rollback();
      return errorResponse(res, 404, 'Treelogy not found');
    }

    // CLIENT_ADMIN can only update their own client's projects
    if (role_name === CLIENT_ADMIN && row.client_id !== client_id) {
      await t.rollback();
      return errorResponse(res, 403, 'You can only update projects under your own client');
    }

    const updatable = [
      'treelogy_code',
      'treelogy_name',
      'landmark',
      'dbname',
      'client_id',
      'status'
    ];
    updatable.forEach(k => {
      if (req.body[k] !== undefined) row[k] = req.body[k];
    });

    await row.save({ transaction: t });
    await t.commit();
    return successResponse(res, 200, 'Treelogy updated', row);
  } catch (error) {
    await t.rollback();
    return errorResponse(res, 500, 'Error updating treelogy', error.message);
  }
}

export async function deleteTreelogy(req, res) {
  try {
    const { role_name, client_id } = req.user || {};

    // Get role names dynamically
    const roleNames = await getRoleNames();
    const SUPER_ADMIN = roleNames.find(name => name === 'SUPER_ADMIN');
    const CLIENT_ADMIN = roleNames.find(name => name === 'CLIENT_ADMIN');

    // Check permissions
    if (role_name !== SUPER_ADMIN && role_name !== CLIENT_ADMIN) {
      return errorResponse(res, 403, 'Insufficient permissions to delete projects');
    }

    // For CLIENT_ADMIN, check if they own this project
    if (role_name === CLIENT_ADMIN) {
      const row = await Treelogy.findOne({ where: { treelogy_id: req.params.id } });
      if (!row) return errorResponse(res, 404, 'Treelogy not found');
      if (row.client_id !== client_id) {
        return errorResponse(res, 403, 'You can only delete projects under your own client');
      }
    }

    const n = await Treelogy.destroy({ where: { treelogy_id: req.params.id } });
    if (!n) return errorResponse(res, 404, 'Treelogy not found');
    return successResponse(res, 200, 'Treelogy deleted', { id: req.params.id });
  } catch (error) {
    return errorResponse(res, 500, 'Error deleting treelogy', error.message);
  }
}
