-- 231020
ALTER TABLE Tilaawa ADD FOREIGN KEY (SiteId) REFERENCES `Sites`(Id);

CREATE TABLE `Sites` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Url` varchar(100),
  `Description` varchar(500),
  `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 ;

-- 1017
alter table Safahaat convert to character set utf8mb3 collate utf8mb3_unicode_ci;
alter table Waqafaat convert to character set utf8mb3 collate utf8mb3_unicode_ci;

--ALTER SCHEMA `akademym_moshaf`  DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4_unicode_ci ; not needed !

ALTER TABLE Waqafaat  DROP INDEX Safha_Sah
ALTER TABLE Waqafaat ADD UNIQUE Soora_Sah(Soora, Sah(10))

-- 1014
SELECT * FROM `StatWaqafaat`

CREATE OR REPLACE VIEW `StatWaqafaat`  AS  
SELECT (select min(Soora) FROM `Waqafaat` WHERE Enabled=1 and ifnull(Safha,0)=0) Soora,
count(Id) as Waqafaat,sum(ifnull(Safha,0)=0) as toDo,sum(ifnull(Safha,0)>0) as Progress, 
sum(ifnull(Safha,0)>0 and ifnull(Hizb,0)>0 and ifnull(Tomon,0)>0 and ifnull(Aaya,0)>0) as Done,
(select max(updatedAt) FROM `Waqafaat` WHERE Enabled=1) `Updated`
FROM `Waqafaat` WHERE Enabled=1;

-- 0218  Database: akademym_moshaf »Table: Qaarie
update Qaarie set IP= '196.119.151.47' where Id=1;

-- 0120
SELECT * FROM db_a6f668_moshaf.waqafaat where Soora=7;

-- 230119
SELECT * FROM db_a6f668_moshaf.waqafaat where Safha=60;

SELECT Id,IP FROM `Qaarie` WHERE `Enabled`=1 AND IP='' ORDER BY `Id` DESC LIMIT 1;

ALTER TABLE `Qaarie` ADD COLUMN `IP` varchar(20) NOT NULL DEFAULT '' AFTER `Id`;


-- 221225
select distinct Soora, Safha from `Waqafaat` where Enabled and Aaya>0 and Min<>'1,1' order by Id

update `Waqafaat` set Min='1,1' where not Locked and Enabled;
select * from `Waqafaat` where not Locked and Enabled;

insert into `Waqafaat` (Soora,Safha,Aaya,Sah) values (1,6,0,'0006000000');

select * from `Waqafaat` where Soora=2 order by Soora, Sah LIMIT 20;

select max(Id) as Id from `Waqafaat` WHERE Hizb='1' and Id>=1 and (Hizb='' or Hizb='0' or Hizb is null) ;

select max(Id) from `Waqafaat` WHERE Tomon=1 and Id>=9;
SELECT Id,Soora,Safha,Hizb,Tomon,Sah,Min,Locked FROM `Waqafaat` WHERE `Soora`=1 OR `Safha`=0 ORDER BY `Sah` LIMIT 1000;

-- 1224
select * from `Waqafaat` where Safha=113 order by Sah;

insert into `Waqafaat` (Soora,Safha,Hizb,Tomon,Aaya,Sah,Min,Locked) values (6,113,14,7,96,'0534405359','6,80',0)

update  `Waqafaat` set Aaya=95,Safha=113,Hizb=14,Tomon=7 WHERE Id between 2039 and 2057;

select distinct Soora, Safha from `Waqafaat` where Enabled and Aaya>0 order by Id;

update  `Waqafaat` set Aaya=0 WHERE Aaya= 1;

select * from `Waqafaat` where Soora=6 and Safha=122 order by Soora, Sah;

delete from `Waqafaat` where Id>10039 ;

select * from `Waqafaat` order by Id desc;
select * from `Waqafaat` where Soora=6 order by Soora, Sah; -- 6 0042700441

select * from `Waqafaat` where Soora=6 and Safha=122 order by Soora, Sah;

-- 1220
UPDATE `Waqafaat` SET `Locked`=0 WHERE `Id`='2178';

SELECT * FROM `Waqafaat` WHERE Safha=122;
update Waqafaat set Aaya=1 where Aaya is null;
-- update Waqafaat set Locked=1 where Id>0;

-- 1219
select * from `Waqafaat` where Safha=122 order by Soora, Sah;

select * from `Waqafaat` where Soora=2 order by Soora, Sah;

select count(*) from `Waqafaat`;
select * into Waqafaat221216  from `Waqafaat`;

-- 1217 : Min top,right in put waqafaat

-- 1216
ALTER TABLE `Waqafaat` 
ADD COLUMN `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `Audio`;
ALTER TABLE `Waqafaat` 
ADD COLUMN `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `createdAt`;

ALTER TABLE `Waqafaat` ADD COLUMN `Locked` tinyint(1) NULL DEFAULT 0 AFTER `Audio`;
ALTER TABLE `Waqafaat` ADD COLUMN `Enabled` tinyint(1) NULL DEFAULT 1 AFTER `Audio`;


ALTER TABLE `Waqafaat` ADD COLUMN `Min` VARCHAR(8) NULL DEFAULT NULL AFTER `Sah`; -- Top,Right

select * from `Waqafaat` where Safha=122 order by Soora, Sah;

CREATE TABLE `Waqafaat` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Safha` smallint,
  `Hizb` smallint,
  `Tomon` smallint,
  `Soora` smallint NOT NULL,
  `Aaya` smallint,
  `Sah` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `Min` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `Text` varchar(960) COLLATE utf8_bin DEFAULT NULL,
  `Image` mediumblob,
  `Audio` mediumblob,
  `Locked` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

  PRIMARY KEY (`Id`),
  UNIQUE KEY `Safha_Sah` (`Soora`,`Sah`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



select * from `Waqafaat` where Safha=122 order by Id;

-- 1214
-- delete from `Waqafaat` where Safh=122;

select * from `Waqafaat` where Safha=122 order by Id;

-- delete from `Waqafaat` where Id>=1;
ALTER TABLE `Waqafaat` ADD UNIQUE `Safha_Sah`(`Safha`, `Sah`);

-- 1213
DROP TABLE `Waqafaat`;

CREATE TABLE `Waqafaat` (
  `Id` int NOT NULL ,
  `Safha` smallint NOT NULL, 
  `Hizb` smallint NOT NULL, 
  `Tomon` smallint NOT NULL, 
  `Soora` smallint NOT NULL, 
  `Aaya` smallint NOT NULL, 
  `Sah` varchar(8) NULL, 
  `Text` varchar(960) NULL,  /* 64*15 */
  `Image` mediumblob NULL,
  `Audio` mediumblob NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Waqafaat` ADD PRIMARY KEY(`Id`);

ALTER TABLE `db_a6f668_moshaf`.`waqafaat` 
CHANGE COLUMN `Id` `Id` INT NOT NULL AUTO_INCREMENT ;


insert into `Waqafaat` (`Safha`,`Hizb`,`Tomon`,`Soora`,`Aaya`,`Sah`) values 
(104,13,6,6,0,'7'),(104,13,6,6,1,'21'),(104,13,6,6,2,'30'),(104,13,6,6,2,'58'),
(104,13,6,6,1,'1:14'),(104,13,6,6,1,'1:36'),(104,13,6,6,1,'2:1');

(122,15,8,6,165,'1:36'),(122,15,8,6,165,'1:40:38'),
(122,15,8,6,163,'1:38:30'),(122,15,8,6,163,'1:39:08'),(122,15,8,6,163,'1:39:30'),
(122,15,8,6,164,'1:39:57'),(122,15,8,6,164,'1:40:23'),

-- 1122
INSERT INTO `الآيات` (`Id`, `سورة`, `رقم`, `آية`, `صفحة`, `سطر`, `حزب`, `ثمن`) VALUES ('', '20', '1', 'طه', '312', '7', '32', '1');

-- 1016
SELECT Joze Id, Safha, Safahaat, Soora, Aaya FROM `Ajzaae`;
SELECT * FROM `AjzaaeId`;

/*
update `Safahaat` set Aaya=010, Soora=76 WHERE Id=600; 2022-10-16T18:36:50.073Z
update `Safahaat` set Aaya=000, Soora=76 WHERE Id=601; 2022-10-16T18:36:52.296Z
update `Safahaat` set Aaya=000, Soora=76 WHERE Id=602; 2022-10-16T18:36:53.840Z
update `Safahaat` set Aaya=000, Soora=76 WHERE Id=603; 2022-10-16T18:36:55.087Z
update `Safahaat` set Aaya=000, Soora=76 WHERE Id=604; 2022-10-16T18:36:56.647Z
*/

-- 1015
SELECT * FROM db_a6f668_moshaf.safahaat where Joze>=26; -- 519

select * from safahaat;

select * from `aayaat`;
update safahaat set soora=100 where Id>186 and soora=0;

select Id,Joze,Soora,Aaya from safahaat;

select *, (select Id from `Safahaat` where `Soora`= سورة and `Aaya`<= رقم order by Id limit 1) as Safha from الآيات order by Id asc;

select Safha(3,20); -- 52

Update FUNCTION `Safha`(sooraId int, aayaId int) RETURNS int
BEGIN
RETURN (select Id from `Safahaat` where `Soora`=sooraId and `Aaya`<=aayaId order by Id limit 1);
END;

-- 1014
select * from ajzaae;
-- update safahaat set Joze=Joze+1 where Id>=102 and Joze>=5 and Joze<28; -- enfin !

-- 1013
select Id,Joze,Soora,Aaya from safahaat;

select Aaya(3);

CREATE FUNCTION `Aaya` (jozeId int)
RETURNS INTEGER
RETURN (select Aaya from `Safahaat` where `Joze`=jozeId order by Id limit 1);

-- 1012
select * from `Ajzaae`;
select * from `AjzaaeId`;


CREATE OR REPLACE VIEW `Ajzaae`  AS  
select `J`.*, `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 ;

select * from `AjzaaeId`;

CREATE  OR REPLACE VIEW `AjzaaeId` AS
SELECT Joze, min(Id) Safha, count(Id) Safahaat, min(Soora) SooraId, 
Aaya(Joze) as AayaId
FROM `Safahaat` group by Joze;

CREATE  OR REPLACE VIEW `Aayaat` AS
    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`.`رقم`

SELECT Joze, min(Id) Safha, min(Soora) SooraId, min(Aaya) as AayaId, count(Id) Safahaat FROM `Safahaat` group by Joze;
update Safahaat set Soora=2 where Id>0;

CREATE TABLE `Qaarie` (
  `Id` int NOT NULL,
  `AccountId` int NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Email` varchar(50) NULL,
  `CountryId` int NOT NULL DEFAULT '1',
  `Phone` varchar(50) NULL,
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Qaarie`  ADD PRIMARY KEY(`Id`);
ALTER TABLE `Qaarie` CHANGE `Id` `Id` INT NOT NULL AUTO_INCREMENT;

INSERT INTO `db_a6f668_moshaf`.`qaarie` (`AccountId`, `Name`, `Email`, `CountryId`, `Phone`) VALUES ('1', 'محمد', 'mohamed@errais.net', '1', '661359645');
INSERT INTO `db_a6f668_moshaf`.`qaarie` (`AccountId`, `Name`, `Email`, `CountryId`, `Phone`) VALUES ('2', 'حنان', 'hanan@echchatt.net', '1', '661351000');
INSERT INTO `db_a6f668_moshaf`.`qaarie` (`AccountId`, `Name`, `Email`,`CountryId`) VALUES ('3', 'هبة',  'hiba@errais.net','1');
INSERT INTO `db_a6f668_moshaf`.`qaarie` (`AccountId`, `Name`, `Email`,`CountryId`) VALUES ('4', 'منار',  'manan@errais.net','1');
INSERT INTO `db_a6f668_moshaf`.`qaarie` (`AccountId`, `Name`, `CountryId`) VALUES ('5', 'زائر', '1');


-- 221010

CREATE TABLE `Tilaawa` (
  `Id` int NOT NULL,
  `qaarie` int NOT NULL,
  `safha` int NOT NULL,
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Tilaawa`  ADD PRIMARY KEY(`Id`);
ALTER TABLE `Tilaawa` CHANGE `Id` `Id` INT NOT NULL AUTO_INCREMENT;

ALTER TABLE Tilaawa ADD FOREIGN KEY (qaarie) REFERENCES `Qaarie`(Id);
ALTER TABLE Tilaawa ADD FOREIGN KEY (safha) REFERENCES `Safahaat`(Id);

ALTER TABLE `db_a6f668_moshaf`.`safahaat` CHANGE COLUMN `Id` `Id` INT NOT NULL ;

-- 

SELECT * FROM `الآيات` WHERE ( `صفحة`=0 )  order by `سورة`,`رقم` LIMIT 50;
ALTER TABLE `db_a6f668_moshaf`.`safahaat` ADD UNIQUE INDEX `Id_UNIQUE` (`Id` ASC);
ALTER TABLE `db_a6f668_moshaf`.`safahaat` ADD PRIMARY KEY (`Id`);

SELECT * FROM db_a6f668_moshaf.safahaat;
update safahaat set Hizb=Joze*2-1 where Hizb is null and Id>0;

ALTER TABLE `db_a6f668_moshaf`.`صفحات` 
DROP COLUMN `Iqrae`,
ALTER TABLE `db_a6f668_moshaf`.`safahaat` ADD COLUMN `Hizb` TINYINT NULL AFTER `Joze` ;
ALTER TABLE `db_a6f668_moshaf`.`safahaat` ADD COLUMN `Soora` TINYINT NULL AFTER `Thomon`;

SELECT Joze, min(Id) Safha, max(Id) ila, count(Id) Pages FROM `Safahaat` group by Joze;
SELECT count(Id) Pages FROM `Safahaat`;

CREATE TABLE `Safahaat` (
  `Id` int NOT NULL,
  `Joze` tinyint NOT NULL,
  `Hizb` tinyint DEFAULT NULL,
  `Thomon` tinyint NOT NULL,
  `Aaya` smallint NOT NULL,
  `Aayaat` tinyint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `Safahaat`  ADD PRIMARY KEY(`Id`);

SELECT * FROM `الآيات` WHERE ( `صفحة`=0 )  order by `سورة`,`رقم` LIMIT 50;



-- 221009
SELECT * FROM `الآيات` WHERE `آية` like '%يِ%' ORDER BY `الآيات`.`سورة` ASC

/*
(6061, 88, 9, 'لِّسَعۡيِہَا رَاضِيَةٌ۬', 592, 7, 0, 1);
*/

-- 0528
SELECT * FROM `تلاوة` order by id desc LIMIT 100


--
-- Structure de la table `تلاوة`
--

CREATE TABLE `تلاوة` (
  `id` int(11) NOT NULL,
  `ِقارئ` int(11) NOT NULL,
  `صفحة` smallint(6) NOT NULL,
  `ساعة` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;