// swad_notice_database.c: notices (yellow notes) operations with database

/*
    SWAD (Shared Workspace At a Distance),
    is a web platform developed at the University of Granada (Spain),
    and used to support university teaching.

    This file is part of SWAD core.
    Copyright (C) 1999-2025 Antonio Caas Vargas

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Affero General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Affero General Public License for more details.

    You should have received a copy of the GNU Affero General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
/*****************************************************************************/
/********************************* Headers ***********************************/
/*****************************************************************************/

#include "swad_database.h"
#include "swad_error.h"
#include "swad_global.h"
#include "swad_notice.h"

/*****************************************************************************/
/************** External global variables from others modules ****************/
/*****************************************************************************/

extern struct Globals Gbl;

/*****************************************************************************/
/******************* Insert a notice in the table of notices *****************/
/*****************************************************************************/
// Return the code of the new inserted notice

long Not_DB_InsertNotice (const char *Content,PriPub_PrivateOrPublic_t PrivateOrPublic)
  {
   extern const char PriPub_Public_YN[PriPub_NUM_PRIVATE_PUBLIC];
   extern const unsigned HidVis_Hidden_01[HidVis_NUM_HIDDEN_VISIBLE];

   return
   DB_QueryINSERTandReturnCode ("can not create notice",
				"INSERT INTO not_notices"
				" (CrsCod,UsrCod,CreatTime,Content,Public,Status)"
				" VALUES"
				" (%ld,%ld,NOW(),'%s','%c',%u)",
				Gbl.Hierarchy.Node[Hie_CRS].HieCod,
				Gbl.Usrs.Me.UsrDat.UsrCod,
				Content,
				PriPub_Public_YN[PrivateOrPublic],
				HidVis_Hidden_01[HidVis_VISIBLE]);	// 0: Visible, 1: Hidden
  }

/*****************************************************************************/
/********************** Mark a notice as hidden/visible **********************/
/*****************************************************************************/

void Not_DB_ChangeNoticeStatus (long NotCod,HidVis_HiddenOrVisible_t HiddenOrVisible)
  {
   extern const unsigned HidVis_Hidden_01[HidVis_NUM_HIDDEN_VISIBLE];

   DB_QueryUPDATE ("can not reveal notice",
		   "UPDATE not_notices"
		     " SET Status=%u"	// 0: Visible, 1: Hidden
		   " WHERE NotCod=%ld"
		     " AND CrsCod=%ld",
	           HidVis_Hidden_01[HiddenOrVisible],
	           NotCod,
	           Gbl.Hierarchy.Node[Hie_CRS].HieCod);
  }

/*****************************************************************************/
/****************** Copy notice to table of deleted notices ******************/
/*****************************************************************************/

void Not_DB_CopyNoticeToDeleted (long NotCod)
  {
   DB_QueryINSERT ("can not remove notice",
		   "INSERT IGNORE INTO not_deleted"
		   " (NotCod,CrsCod,UsrCod,CreatTime,Content,Public,NumNotif)"
		   " SELECT NotCod,"
		           "CrsCod,"
		           "UsrCod,"
		           "CreatTime,"
		           "Content,"
			   "Public,"
		           "NumNotif"
		     " FROM not_notices"
		    " WHERE NotCod=%ld"
		      " AND CrsCod=%ld",	// Extra check
                   NotCod,
                   Gbl.Hierarchy.Node[Hie_CRS].HieCod);
  }

/*****************************************************************************/
/*********** Update number of users notified in table of notices *************/
/*****************************************************************************/

void Not_DB_UpdateNumUsrsNotifiedByEMailAboutNotice (long NotCod,
                                                     unsigned NumUsrsToBeNotifiedByEMail)
  {
   DB_QueryUPDATE ("can not update the number of notifications of a notice",
		   "UPDATE not_notices"
		     " SET NumNotif=%u"
		   " WHERE NotCod=%ld",
	           NumUsrsToBeNotifiedByEMail,
	           NotCod);
  }

/*****************************************************************************/
/**************************** Get data of a notice ***************************/
/*****************************************************************************/

Exi_Exist_t Not_DB_GetNoticeData (MYSQL_RES **mysql_res,long NotCod)
  {
   return
   DB_QuerySELECTunique (mysql_res,"can not get notice from database",
			 "SELECT NotCod,"				// row[0]
				"UNIX_TIMESTAMP(CreatTime) AS F,"	// row[1]
				"UsrCod,"				// row[2]
				"Content,"				// row[3]
				"Public,"				// row[4]
				"Status"				// row[5]
			  " FROM not_notices"
			 " WHERE NotCod=%ld"
			   " AND CrsCod=%ld",	// Extra check
			 NotCod,
			 Gbl.Hierarchy.Node[Hie_CRS].HieCod);
  }

/*****************************************************************************/
/********************* Get content of message from database ******************/
/*****************************************************************************/

Exi_Exist_t Not_DB_ContentNotice (MYSQL_RES **mysql_res,long NotCod)
  {
   return
   DB_QuerySELECTunique (mysql_res,"can not get content of notice",
			 "SELECT Content"	// row[0]
			  " FROM not_notices"
			 " WHERE NotCod=%ld",
			 NotCod);
  }

/*****************************************************************************/
/***************************** Get active notices ****************************/
/*****************************************************************************/

unsigned Not_DB_GetNotices (MYSQL_RES **mysql_res,long HieCod,
			    Not_Listing_t TypeNoticesListing)
  {
   extern const unsigned HidVis_Hidden_01[HidVis_NUM_HIDDEN_VISIBLE];
   static const char *Not_DB_SubQueryVisible[Not_NUM_TYPES_LISTING] =
     {
      [Not_LIST_BRIEF_NOTICES] = " AND Status=0",	// Only visible notices
      [Not_LIST_FULL_NOTICES ] = "",			// All notices
     };
   static const char *Not_DB_SubQueryPublic[Usr_NUM_BELONG] =
     {
      [Usr_DONT_BELONG] = " AND Public='Y'",	// Only public notices
      [Usr_BELONG     ] = "",			// All notices
     };

   return (unsigned)
   DB_QuerySELECT (mysql_res,"can not get notices from database",
		   "SELECT NotCod,"			// row[0]
			  "UNIX_TIMESTAMP(CreatTime),"	// row[1]
			  "UsrCod,"			// row[2]
			  "Content,"			// row[3]
			  "Public,"			// row[4]
			  "Status"			// row[5]
		    " FROM not_notices"
		   " WHERE CrsCod=%ld"
		       "%s"
	               "%s"
		" ORDER BY CreatTime DESC",
		   HieCod,
		   Not_DB_SubQueryVisible[TypeNoticesListing],
		   Not_DB_SubQueryPublic[Gbl.Usrs.Me.IBelongToCurrent[Hie_CRS]]);
  }

/*****************************************************************************/
/*************************** Get number of notices ***************************/
/*****************************************************************************/

unsigned Not_DB_GetNumNotices (MYSQL_RES **mysql_res,
			       Hie_Level_t HieLvl,HidVis_HiddenOrVisible_t HiddenOrVisible)
  {
   extern const unsigned HidVis_Hidden_01[HidVis_NUM_HIDDEN_VISIBLE];

   switch (HieLvl)
     {
      case Hie_SYS:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(NumNotif)"			// row[1]
			  " FROM not_notices"
			 " WHERE Status=%u",			// 0: Visible, 1: Hidden
                         HidVis_Hidden_01[HiddenOrVisible]);
      case Hie_CTY:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_notices.NumNotif)"	// row[1]
			  " FROM ins_instits,"
			        "ctr_centers,"
			        "deg_degrees,"
			        "crs_courses,"
			        "not_notices"
			 " WHERE ins_instits.CtyCod=%ld"
			   " AND ins_instits.InsCod=ctr_centers.InsCod"
			   " AND ctr_centers.CtrCod=deg_degrees.CtrCod"
			   " AND deg_degrees.DegCod=crs_courses.DegCod"
			   " AND crs_courses.CrsCod=not_notices.CrsCod"
			   " AND not_notices.Status=%u",	// 0: Visible, 1: Hidden
                         Gbl.Hierarchy.Node[Hie_CTY].HieCod,
                         HidVis_Hidden_01[HiddenOrVisible]);
      case Hie_INS:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_notices.NumNotif)"	// row[1]
			  " FROM ctr_centers,"
			        "deg_degrees,"
			        "crs_courses,"
			        "not_notices"
			 " WHERE ctr_centers.InsCod=%ld"
			   " AND ctr_centers.CtrCod=deg_degrees.CtrCod"
			   " AND deg_degrees.DegCod=crs_courses.DegCod"
			   " AND crs_courses.CrsCod=not_notices.CrsCod"
			   " AND not_notices.Status=%u",	// 0: Visible, 1: Hidden
                         Gbl.Hierarchy.Node[Hie_INS].HieCod,
                         HidVis_Hidden_01[HiddenOrVisible]);
      case Hie_CTR:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_notices.NumNotif)"	// row[1]
			  " FROM deg_degrees,"
			        "crs_courses,"
			        "not_notices"
			 " WHERE deg_degrees.CtrCod=%ld"
			   " AND deg_degrees.DegCod=crs_courses.DegCod"
			   " AND crs_courses.CrsCod=not_notices.CrsCod"
			   " AND not_notices.Status=%u",	// 0: Visible, 1: Hidden
                         Gbl.Hierarchy.Node[Hie_CTR].HieCod,
                         HidVis_Hidden_01[HiddenOrVisible]);
      case Hie_DEG:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_notices.NumNotif)"	// row[1]
			  " FROM crs_courses,"
			        "not_notices"
			 " WHERE crs_courses.DegCod=%ld"
			   " AND crs_courses.CrsCod=not_notices.CrsCod"
			   " AND not_notices.Status=%u",	// 0: Visible, 1: Hidden
                         Gbl.Hierarchy.Node[Hie_DEG].HieCod,
                         HidVis_Hidden_01[HiddenOrVisible]);
      case Hie_CRS:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(NumNotif)"			// row[1]
			  " FROM not_notices"
			 " WHERE CrsCod=%ld"
			   " AND Status=%u",			// 0: Visible, 1: Hidden
                         Gbl.Hierarchy.Node[Hie_CRS].HieCod,
                         HidVis_Hidden_01[HiddenOrVisible]);
      default:
	 Err_WrongHierarchyLevelExit ();
	 return 0;	// Not reached
     }
  }

/*****************************************************************************/
/********************** Get number of deleted notices ************************/
/*****************************************************************************/

unsigned Not_DB_GetNumNoticesDeleted (MYSQL_RES **mysql_res,Hie_Level_t HieLvl)
  {
   switch (HieLvl)
     {
      case Hie_SYS:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of deleted notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(NumNotif)"			// row[1]
			  " FROM not_deleted");
      case Hie_CTY:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of deleted notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_deleted.NumNotif)"	// row[1]
			  " FROM ins_instits,"
			        "ctr_centers,"
			        "deg_degrees,"
			        "crs_courses,"
			        "not_deleted"
			 " WHERE ins_instits.CtyCod=%ld"
			   " AND ins_instits.InsCod=ctr_centers.InsCod"
			   " AND ctr_centers.CtrCod=deg_degrees.CtrCod"
			   " AND deg_degrees.DegCod=crs_courses.DegCod"
			   " AND crs_courses.CrsCod=not_deleted.CrsCod",
                         Gbl.Hierarchy.Node[Hie_CTY].HieCod);
      case Hie_INS:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of deleted notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_deleted.NumNotif)"	// row[1]
			  " FROM ctr_centers,"
			        "deg_degrees,"
			        "crs_courses,"
			        "not_deleted"
			 " WHERE ctr_centers.InsCod=%ld"
			   " AND ctr_centers.CtrCod=deg_degrees.CtrCod"
			   " AND deg_degrees.DegCod=crs_courses.DegCod"
			   " AND crs_courses.CrsCod=not_deleted.CrsCod",
                         Gbl.Hierarchy.Node[Hie_INS].HieCod);
      case Hie_CTR:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of deleted notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_deleted.NumNotif)"	// row[1]
			  " FROM deg_degrees,"
			        "crs_courses,"
			        "not_deleted"
			 " WHERE deg_degrees.CtrCod=%ld"
			 " AND deg_degrees.DegCod=crs_courses.DegCod"
			 " AND crs_courses.CrsCod=not_deleted.CrsCod",
                         Gbl.Hierarchy.Node[Hie_CTR].HieCod);
      case Hie_DEG:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of deleted notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(not_deleted.NumNotif)"	// row[1]
			  " FROM crs_courses,"
			        "not_deleted"
			 " WHERE crs_courses.DegCod=%ld"
			   " AND crs_courses.CrsCod=not_deleted.CrsCod",
                         Gbl.Hierarchy.Node[Hie_DEG].HieCod);
      case Hie_CRS:
	 return (unsigned)
         DB_QuerySELECT (mysql_res,"can not get number of deleted notices",
			 "SELECT COUNT(*),"			// row[0]
			        "SUM(NumNotif)"			// row[1]
			  " FROM not_deleted"
			 " WHERE CrsCod=%ld",
                         Gbl.Hierarchy.Node[Hie_CRS].HieCod);
      default:
	 Err_WrongHierarchyLevelExit ();
	 return 0;	// Not reached
     }
  }

/*****************************************************************************/
/******************************** Remove notice ******************************/
/*****************************************************************************/

void Not_DB_RemoveNotice (long NotCod)
  {
   DB_QueryDELETE ("can not remove notice",
		   "DELETE FROM not_notices"
		   " WHERE NotCod=%ld"
		     " AND CrsCod=%ld",	// Extra check
                   NotCod,
                   Gbl.Hierarchy.Node[Hie_CRS].HieCod);
  }

/*****************************************************************************/
/************************* Remove notices in a course ************************/
/*****************************************************************************/

void Not_DB_RemoveCrsNotices (long HieCod)
  {
   /***** Copy all notices from the course to table of deleted notices *****/
   DB_QueryINSERT ("can not remove notices in a course",
		   "INSERT INTO not_deleted"
		   " (NotCod,CrsCod,UsrCod,CreatTime,Content,Public,NumNotif)"
		   " SELECT NotCod,"
			   "CrsCod,"
			   "UsrCod,"
			   "CreatTime,"
			   "Content,"
			   "Public,"
			   "NumNotif"
		    " FROM not_notices"
		   " WHERE CrsCod=%ld",
		   HieCod);

   /***** Remove all notices from the course *****/
   DB_QueryDELETE ("can not remove notices in a course",
		   "DELETE FROM not_notices"
		   " WHERE CrsCod=%ld",
		   HieCod);
  }
