-- TELSPAY EDUCATION SCHOOL ADVERTISING & MEDIA v2026.9.2
-- Apply AFTER TELSPAY_EDUCATION_V2026_9_0.sql and TELSPAY_EDUCATION_SUBSCRIPTIONS_V2026_9_1.sql.
-- Additive only: no existing table or CSS is removed/replaced.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

ALTER TABLE edu_schools
  ADD COLUMN IF NOT EXISTS public_profile_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER profile_summary,
  ADD COLUMN IF NOT EXISTS physical_address VARCHAR(300) DEFAULT NULL AFTER address,
  ADD COLUMN IF NOT EXISTS latitude DECIMAL(10,7) DEFAULT NULL AFTER physical_address,
  ADD COLUMN IF NOT EXISTS longitude DECIMAL(10,7) DEFAULT NULL AFTER latitude,
  ADD COLUMN IF NOT EXISTS map_url VARCHAR(800) DEFAULT NULL AFTER longitude,
  ADD COLUMN IF NOT EXISTS location_verification_status ENUM('MISSING','PENDING','VERIFIED','REJECTED') NOT NULL DEFAULT 'MISSING' AFTER map_url,
  ADD COLUMN IF NOT EXISTS location_verified_by INT DEFAULT NULL AFTER location_verification_status,
  ADD COLUMN IF NOT EXISTS location_verified_at DATETIME DEFAULT NULL AFTER location_verified_by,
  ADD COLUMN IF NOT EXISTS location_rejection_reason VARCHAR(500) DEFAULT NULL AFTER location_verified_at,
  ADD COLUMN IF NOT EXISTS tiktok_url VARCHAR(500) DEFAULT NULL AFTER website;

CREATE TABLE IF NOT EXISTS edu_school_media (
  media_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  media_ref VARCHAR(60) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  media_type ENUM('BADGE','PHOTO','VIDEO_UPLOAD','VIDEO_YOUTUBE') NOT NULL,
  title VARCHAR(180) DEFAULT NULL,
  description VARCHAR(1000) DEFAULT NULL,
  storage_path VARCHAR(700) DEFAULT NULL,
  external_url VARCHAR(1000) DEFAULT NULL,
  external_video_id VARCHAR(80) DEFAULT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  file_size BIGINT UNSIGNED DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  approval_status ENUM('PENDING','APPROVED','REJECTED','DISABLED') NOT NULL DEFAULT 'PENDING',
  rejection_reason VARCHAR(500) DEFAULT NULL,
  uploaded_by_school_user BIGINT UNSIGNED DEFAULT NULL,
  approved_by_admin INT DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (media_id),
  UNIQUE KEY uq_edu_school_media_ref (media_ref),
  KEY idx_edu_school_media_public (school_id,media_type,approval_status,sort_order),
  CONSTRAINT fk_edu_school_media_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_school_media_user FOREIGN KEY (uploaded_by_school_user) REFERENCES edu_school_users(school_user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_profile_events (
  event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  source VARCHAR(50) DEFAULT NULL,
  idempotency_key CHAR(64) DEFAULT NULL,
  ip_hash CHAR(64) NOT NULL,
  user_agent_hash CHAR(64) NOT NULL,
  metadata_json LONGTEXT DEFAULT NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (event_id),
  UNIQUE KEY uq_edu_school_profile_event_idempotency (idempotency_key),
  KEY idx_edu_school_profile_event_school (school_id,event_type,occurred_at),
  CONSTRAINT fk_edu_school_profile_event_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO edu_subscription_features(feature_code,feature_name,feature_type,module_code,description,sort_order) VALUES
('PUBLIC_PROFILE','Public School Advertising Profile','TOGGLE','marketing','Advertise the school publicly on TELSPAY Education with a SACCO-verified physical location map.',130),
('ABOUT_PROFILE','Extended School About Information','TOGGLE','marketing','Publish the school About section and marketing description on the public profile.',140),
('SCHOOL_BADGE','School Badge Branding','TOGGLE','marketing','Upload and display an approved school badge on listings and the public school profile.',150),
('PHOTO_GALLERY','School Photo Gallery','TOGGLE','marketing','Upload approved sample school photos for public advertising.',160),
('YOUTUBE_VIDEO','YouTube School Demo Video','TOGGLE','marketing','Embed an approved school demo video from YouTube.',170),
('DIRECT_VIDEO_UPLOAD','Direct School Demo Video Upload','TOGGLE','marketing','Upload an approved school demo video directly to TELSPAY Education.',180),
('SOCIAL_SHARING','Public Social Sharing','TOGGLE','marketing','Allow visitors to share the public school profile using supported device/share channels.',190),
('TIKTOK_SHARING','TikTok Sharing','TOGGLE','marketing','Enable TikTok-oriented native sharing/copy-link actions and school TikTok profile linking.',195),
('PROFILE_ANALYTICS','School Advertising Analytics','TOGGLE','marketing','View TELSPAY profile views and outbound sharing/action events.',198),
('PHOTO_GALLERY_LIMIT','Maximum School Gallery Photos','LIMIT','marketing','Maximum active/pending gallery photos allowed under the package.',240),
('VIDEO_COUNT_LIMIT','Maximum School Demo Videos','LIMIT','marketing','Maximum active/pending YouTube/direct demo videos allowed under the package.',250),
('VIDEO_UPLOAD_MB_LIMIT','Maximum Direct Video Size (MB)','LIMIT','marketing','Maximum size in megabytes for each directly uploaded school video.',260);

INSERT IGNORE INTO edu_school_permissions(permission_code,permission_name,module_code,description) VALUES
('school.marketing.view','View school advertising profile','marketing','View paid public-profile settings, media and advertising analytics.'),
('school.marketing.manage','Manage school advertising profile','marketing','Submit location, About information, badge, gallery photos, videos and social links for approval.');

INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN ('school.marketing.view','school.marketing.manage')
WHERE r.role_code IN ('SCHOOL_SUPER_ADMIN','SCHOOL_HEAD_TEACHER');
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code='school.marketing.view'
WHERE r.role_code IN ('SCHOOL_REGISTRAR','SCHOOL_ADMISSIONS_OFFICER','SCHOOL_AUDITOR_READ_ONLY');

INSERT IGNORE INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.school_profiles.view','View school advertising profiles','education','MEDIUM','View paid school public-profile, location and marketing media status.',UTC_TIMESTAMP()),
('education.school_profiles.manage','Manage school advertising profiles','education','HIGH','Review and manage public-profile publishing state and submitted school marketing information.',UTC_TIMESTAMP()),
('education.school_location.verify','Verify school public location','education','HIGH','Verify or reject a submitted physical address/map before public advertisement.',UTC_TIMESTAMP()),
('education.school_media.approve','Approve school advertising media','education','HIGH','Approve or reject school badges, photos and demo videos before public display.',UTC_TIMESTAMP());

INSERT IGNORE INTO admin_cc_role_permissions(role_id,permission_id,granted_at)
SELECT r.role_id,p.permission_id,UTC_TIMESTAMP() FROM admin_cc_roles r CROSS JOIN admin_cc_permissions p
WHERE r.role_code IN ('SUPER_ADMIN','EDUCATION_ADMIN') AND p.permission_code IN ('education.school_profiles.view','education.school_profiles.manage','education.school_location.verify','education.school_media.approve');
INSERT IGNORE INTO admin_cc_role_permissions(role_id,permission_id,granted_at)
SELECT r.role_id,p.permission_id,UTC_TIMESTAMP() FROM admin_cc_roles r CROSS JOIN admin_cc_permissions p
WHERE r.role_code='EDUCATION_PARTNER_MANAGER' AND p.permission_code IN ('education.school_profiles.view','education.school_profiles.manage','education.school_location.verify','education.school_media.approve');

INSERT IGNORE INTO admin_cc_schema_versions(version,description,installed_at)
VALUES('2026.9.2','Paid public school advertising, verified map location, About, badge, photo gallery, YouTube/direct video and social/TikTok sharing',UTC_TIMESTAMP());