-- 240107
select * from WaqafaatView

-- 0104
UPDATE Waqafaat set Course=6 where Soora>=17 and Soora<=35 and Course<>6

UPDATE `Waqafaat` SET Coord=CONCAT(`Min`,',20,6') where length(Coord)<7 and length(Min)>2;

insert into `Waqafaat` (Soora,Safha,Hizb,Tomon,Aaya,Sah,Min,Locked) 
                values (12,196,25,2, 69,'0381803845','50,50',0)

SELECT * FROM `Waqafaat` where strlen(Coord)=0;
-- 10061 total, Query took 0.0003 seconds.


-- 240103
select CourseId, Course, Joze, Hizb, Tomon, SooraId, Soora, Safha, Min, Ila, Sahs 
from WaqafaatView where IFNULL(Safha,0)>0 order by Course,Joze,Soora,Safha


CREATE View WaqafaatView AS -- created 17:04
select `W`.`Course` AS `CourseId`,`C`.`Name` AS `Course`,floor(((`W`.`Hizb` + 1) / 2)) AS `Joze`,`W`.`Hizb` AS `Hizb`
,`W`.`Tomon` AS `Tomon`,`W`.`Soora` AS `SooraId`,`S`.`سورة` AS `Soora`,`W`.`Safha` AS `Safha`,min(`W`.`Aaya`) AS `Min`
,max(`W`.`Aaya`) AS `Ila`,count(`W`.`Id`) AS `Sahs` 
from ((`akademym_moshaf`.`Waqafaat` `W` join `akademym_moshaf`.`السور` `S`) join `akademym_moshaf`.`Courses` `C`) 
where ((`W`.`Enabled` = 1) and (`W`.`Soora` = `S`.`رقم`) and (`W`.`Course` = `C`.`Id`)) 
group by `W`.`Course`,`C`.`Name`,`W`.`Hizb`,`W`.`Soora`,`S`.`سورة`,`W`.`Safha`,`W`.`Tomon`


INSERT Courses (Id,Name,Description) 
VALUES (1, 'القران الكريم', 'بِسۡمِ ٱللَّهِ ٱلرَّحۡمَٰنِ ٱلرَّحِيمِ'),
(2, ' حفظ جزء تبارك و جزء عم', 'حفظ جزء تبارك و جزء عم'),
(3, ' حفظ سورة البقرة سورة ال عمران', 'حفظ سورة البقرة سورة ال عمران'),
(4, ' حفظ سور النّساء والمائدة والأنعام والأعراف', 'حفظ النّساء والمائدة والأنعام والأعراف'),
(5, ' حفظ السور من الأنفال إلى النّحل', '9 سور من الحزب 18 ثمن 4 صفحة 142 الى الحزب 28 ثمن 8 صفحة 226'),
(6, 'حفظ السور من الإسراء إلى فاطر', 'حفظ السور من الإسراء إلى فاطر'),
(7, 'حفظ السور من يسين إلى التحريم', 'حفظ السور من يسين إلى التحريم'),
(8, 'دورة أصول ورش', 'دورة أصول ورش 1444')



CREATE TABLE `Courses` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(250) NOT NULL,
  `Description` varchar(900),
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;


create View WaqafaatView AS -- created 17:04
select Course, FLOOR((Hizb+1)/2) as Joze, Hizb, concat(W.Soora,'-',S.`سورة`) AS Soora, Safha
, min(Aaya) as `Min`, max(Aaya) as `Ila`, count(Id) as `Sahs`  
from `Waqafaat` as W join `السور` as S
where (W.Soora = S.`رقم` and W.`Enabled` = 1)
group by Course,Hizb,concat(W.Soora,'-',S.`سورة`),Safha

create View StatWaqafaat
select (select min(`Waqafaat`.`Soora`) from `Waqafaat` where ((`Waqafaat`.`Enabled` = 1) and (ifnull(`Waqafaat`.`Safha`,0) = 0))) AS `Soora`,count(`Waqafaat`.`Id`) AS `Waqafaat`,sum((ifnull(`Waqafaat`.`Safha`,0) = 0)) AS `toDo`,sum((ifnull(`Waqafaat`.`Safha`,0) > 0)) AS `Progress`,sum(((ifnull(`Waqafaat`.`Safha`,0) > 0) and (ifnull(`Waqafaat`.`Hizb`,0) > 0) and (ifnull(`Waqafaat`.`Tomon`,0) > 0) and (ifnull(`Waqafaat`.`Aaya`,0) > 0))) AS `Done`,(select max(`Waqafaat`.`updatedAt`) from `Waqafaat` where (`Waqafaat`.`Enabled` = 1)) AS `Updated` 
from `Waqafaat` where (`Waqafaat`.`Enabled` = 1)

create View AjzaaeId
select `Safahaat`.`Joze` AS `Joze`,min(`Safahaat`.`Id`) AS `Safha`,count(`Safahaat`.`Id`) AS `Safahaat`,min(`Safahaat`.`Soora`) AS `SooraId`,`Aaya`(`Safahaat`.`Joze`) AS `AayaId` 
from `Safahaat` group by `Safahaat`.`Joze`


create View Ajzaae
select `J`.`Joze` AS `Joze`,`J`.`Safha` AS `Safha`,`J`.`SooraId` AS `SooraId`,`J`.`AayaId` AS `AayaId`,`J`.`Safahaat` AS `Safahaat`,`S`.`سورة` AS `Soora`,`A`.`آية` AS `Aaya` 
from ((`AjzaaeId` `J` join `الآيات` `A`) join `السور` `S`) where ((`J`.`SooraId` = `S`.`رقم`) and (`J`.`AayaId` = `A`.`رقم`) and (`A`.`سورة` = `S`.`رقم`)) 
order by `J`.`Joze`,`J`.`Safha`

create View Aayaat
select `s`.`رقم` AS `rs`,`s`.`سورة` AS `ss`,`a`.`رقم` AS `r`,`a`.`آية` AS `A`,`Safha`(`s`.`رقم`,`a`.`رقم`) AS `s` 
from (`الآيات` `a` join `السور` `s`) where (`a`.`سورة` = `s`.`رقم`) order by `s`.`رقم`,`a`.`رقم`