import com.liferay.portal.kernel.dao.jdbc.DataAccess;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.service.GroupLocalServiceUtil;
import com.liferay.portal.kernel.service.LayoutSetLocalServiceUtil;
import com.liferay.portal.kernel.service.LayoutLocalServiceUtil;
import com.liferay.counter.kernel.service.CounterLocalServiceUtil;
import com.liferay.portal.kernel.model.LayoutSet;
import com.liferay.portal.kernel.model.Group;
import com.liferay.portal.kernel.model.Layout;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;

public class RemoveOrphanedLayouts {

public void doRemove() throws SQLException, SystemException {
		_log.info("Starting RemoveOrphanedLayouts process");

		if (_safeMode) {
			_log.info("Safe Mode Execution");
		}
		
		List<Layout> orphanedLayouts = getOrphanedLayouts();
		
		//Log the number of orphaned layouts
		_log.info("There are " + orphanedLayouts.size() + " orphaned " +
			"Layouts");
		List<Group> groupsToDelete = getOrphanedSites();	
		
		//Deleting orphaned records when safeMode set to false	
		 if (!_safeMode) {
			 for (Layout layout : orphanedLayouts) {
				
				 long layoutId=layout.getPlid();
				 LayoutLocalServiceUtil.deleteLayout(layoutId);
				
					_log.info("Layout with Id " +
					layout.getPlid() + " has been removed");
				
			}
		 }
		 else {
			 _log.info("The script has been executed in safe mode, set " +
				 "_safeMode to false to persist the changes");
		 }
		
		//Deleting recreated sites
		 if (!_safeMode) {
			
			 for (Group group : groupsToDelete) {
			
				 GroupLocalServiceUtil.deleteGroup(group);
				
				 _log.info("Group with Id " +
				 group.getGroupId() + " has been removed");
					
	}
 }
		
		_log.info("Finishing RemoveOrphanedLayouts process");
	}

	protected List<Layout> getOrphanedLayouts() throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<Layout> orphanedLayouts = new ArrayList<Layout>();

		try {
			con = DataAccess.getConnection();

			//Finding orphaned layouts.		
			ps = con.prepareStatement(
				"select plid, groupId, companyId from layout where groupId not in (select groupId from group_)");

			rs = ps.executeQuery();

			while (rs.next()) {
				long plid = rs.getLong("plid");
				long groupId = rs.getLong("groupId");
				long companyId = rs.getLong("companyId");

				_log.info("The layout with ID " + plid + " does not " +
					"have an associated Group");
				
				Layout layout =
					LayoutLocalServiceUtil.create();
					layout.setPlid(plid);
				
				orphanedLayouts.add(layout);
				
			}
		}
		finally {
				DataAccess.cleanUp(con, ps, rs);
		}

		return orphanedLayouts;
	}
	
	
		protected List<Group> getOrphanedSites() throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<Layout> orphanedSites = new ArrayList<Layout>();

		try {
			con = DataAccess.getConnection();

			//Finding orphaned layouts.		
			ps = con.prepareStatement(
				"select distinct groupId, companyId from layout where groupId not in (select groupId from group_)");

			rs = ps.executeQuery();

			while (rs.next()) {
				long groupId = rs.getLong("groupId");
				long companyId = rs.getLong("companyId");
				if(!_safeMode)	{
				//Recreating LayoutSets and groups.
				
				Group site= GroupLocalServiceUtil.createGroup(groupId);
				site.setName("RestoredSiteLiferay" + groupId);
				site.setClassNameId(groupId);
				site.setClassPK(groupId);
				site.setCompanyId(companyId);
				site.setFriendlyURL("/restoredsiteliferay" + groupId);
				site.setActive(false);
				site.setSite(true);
				
				GroupLocalServiceUtil.addGroup(site);					
				
				orphanedSites.add(site);
				
				LayoutSet layoutSet = LayoutSetLocalServiceUtil.create();
				layoutSet.setLayoutSetId(CounterLocalServiceUtil.increment());
				layoutSet.setGroupId(groupId);
				layoutSet.setCompanyId(companyId);
				layoutSet.setPrivateLayout(false);
				//layoutSet.setHead(false);
				
				LayoutSet layoutSetPrivate = LayoutSetLocalServiceUtil.create();
				layoutSetPrivate.setLayoutSetId(CounterLocalServiceUtil.increment());
				layoutSetPrivate.setGroupId(groupId);
				layoutSetPrivate.setCompanyId(companyId);
				layoutSetPrivate.setPrivateLayout(true);
				//layoutSetPrivate.setHead(false);

				LayoutSetLocalServiceUtil.addLayoutSet(layoutSet);
				LayoutSetLocalServiceUtil.addLayoutSet(layoutSetPrivate);
				}
			}
		}
		finally {
				DataAccess.cleanUp(con, ps, rs);
		}

		return orphanedSites;
	}
	

	//First run with safeMode set to true. Set it to false to remove the orphaned records.
	private static final boolean _safeMode = true;

	private static Log _log = LogFactoryUtil.getLog(
		RemoveOrphanedLayouts.class);
}

(new RemoveOrphanedLayouts()).doRemove()