select  id, code, identity_no, en_f_name, en_m_name, en_l_name, en_certificate_name ,ar_certificate_name, gender, birth_date, mobile,  email
 , get_contacts(id ,'App\\Models\\Customer') other_contacts
,  wallet,
 (select name_en from corporates where id= c.corporate_id) corporate,
 (select name_en from nationalities where id= c.nationality_id) nationality ,
 comment, topic, last_updated_by,(select name from users where id = c.last_follow_up_by) `Owner` ,
 (select name from users where id = c.opened_by) `opened_by` ,  created_at, updated_at
 from customers c
 where active =1 and created_at > '2020-01-01'
 order by created_at asc;


 update course_sessions set `calculated_at`=now() , `calculated_by`=1 where session_date <= '2022-09-30';



select count(*)  , mobile , min(id) , max(id) from customers
group by mobile having count(*)>2;

select  id, code, identity_no, en_f_name, en_m_name, en_l_name, en_certificate_name ,ar_certificate_name, gender, birth_date, mobile,  email
 , get_contacts(id ,'App\\Models\\Customer') other_contacts
,  wallet,
 (select name_en from corporates where id= c.corporate_id) corporate,
 (select name_en from nationalities where id= c.nationality_id) nationality ,
 comment, topic, last_updated_by,(select name from users where id = c.last_follow_up_by) `Owner` ,
 (select name from users where id = c.opened_by) `opened_by` ,  created_at, updated_at
 from customers c
 where active =1 and length(mobile) >6  and exists (select 'x'  from customers where id != c.id and mobile = c.mobile and last_follow_up_by != c.last_follow_up_by and active =1 );

select count(*)  from  customers where active = 0 or active is null;
select *  from  customers where (active = 0 or active is null) and id in (select customer_id from order_details );
update customers set active =1 where (active = 0 or active is null) and id in (select customer_id from order_details );
update customers set active =1 where (active = 0 or active is null) and id in (select customer_id from course_customers );
select *  from  customers where (active = 0 or active is null) and id in (select customer_id from course_customers );

delete from  customers where (active = 0 or active is null) and id not in (select customer_id from course_customers )
and id not in (select customer_id from order_details );

delete from  corporates where active = 0 or active is null;
select *  from  corporates where (active = 0 or active is null) and id in (select account_id from orders where account_type = 'App\\Models\\Corporate');

select * from customer_files where customer_id in (select id from  customers where active = 0 or active is null)
select * from course_customers where customer_id in (select id from  customers where active = 0 or active is null)

select * from customers where ( mobile is null or length(mobile )<4 ) and corporate_id is null ;

select * from contacts;

select count(*) from customers where ( mobile is null or length(mobile )<4 ) and corporate_id is null
and id not in (select customer_id from order_details where customer_id is not null )
and id not in (select account_id from contacts where account_type ='App\\Models\\Customer');


delete   from customers where ( mobile is null or length(mobile )<4 ) and corporate_id is null
and id not in (select customer_id from order_details where customer_id is not null )
and id not in (select account_id from contacts where account_type ='App\\Models\\Customer')
order by id desc;


select * from courses where id =1084;
select * from course_sessions where course_id =1084;
select * from courses where id =1084;



SELECT * from orders o where `total_amount_with_taxes` != (select sum(`price_with_tax`) from order_details where order_id=o.id and original=1)


-- customers attend course
select id, code, identity_no, en_f_name, en_m_name, en_l_name, en_certificate_name ,ar_certificate_name, gender, birth_date, mobile, email , get_contacts(id ,'App\\Models\\Customer') other_contacts , wallet, (select name_en from corporates where id= c.corporate_id) corporate, (select name_en from nationalities where id= c.nationality_id) nationality , comment, topic, last_updated_by,(select name from users where id = c.last_follow_up_by) `Owner` , (select name from users where id = c.opened_by) `opened_by` , created_at, updated_at from customers c where id in ( select DISTINCT customer_id from course_customers where course_id in ( select id from courses where course_level_id in (71,103,189,227,2280,2329)))



ALTER TABLE `courses`
ADD COLUMN `name` VARCHAR(255) NULL AFTER `id`;



update courses c set name = (select name_en from products where id = (select product_id from course_levels where id =c.course_level_id));

ALTER TABLE `whatsapp_messages_histories` CHANGE `message` `message` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

ALTER TABLE `course_customers` ADD `certificat_token` VARCHAR(255) NULL AFTER `certificate_at`;


SELECT * FROM `courses` c where start_date !=(select min(session_date) from course_sessions where course_id = c.id);



update courses c set product_id = (select product_id from course_levels where id = c.course_level_id);

update courses c set track_id = (select track_id from track_products where product_id = c.product_id group by product_id , track_id having count(*) =1 );





