#!/usr/bin/env python3
"""Parse CSC Burkina Faso broadcast media list into structured CSV + SQLite"""
import csv
import sqlite3
import os
import re

base_dir = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(base_dir, "broadcast-media.db")
csv_path = os.path.join(base_dir, "broadcast-media.csv")

# All entries manually structured from the PDF text extraction
# Format: (category, subcategory, num, name, location, province, promoter, frequency)

entries = []

# === I. PRIVATE AUDIOVISUAL MEDIA ===
# A. Radio Broadcasting
# 1. Confessional Radios (40)
confessional = [
    (1, "Radio Evangile Développement (RED)", "Ouagadougou", "Kadiogo", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "93.4 MHz"),
    (2, "Radio Evangile Développement (RED)", "Ouahigouya", "Yatenga", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "104.00 MHz"),
    (3, "Radio Avé Maria", "Ouagadougou", "Kadiogo", "Diocèse de Ouagadougou/Abbé Paul OUEDRAOGO", "91.6 MHz"),
    (4, "Radio Evangile Développement", "Bobo-Dioulasso", "Houet", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "106.300 MHz"),
    (5, "Radio Fréquence Espoir du CEDICOM", "Dédougou", "Mouhoun", "Diocèse de Dédougou/Abbé Emile SIMBORO", "96.800 MHz"),
    (6, "Radio Unitas", "Diébougou", "Bougouriba", "Diocèse de Dédougou/Monsieur A. Marie Lambert POUYAN", "94.700 MHz"),
    (7, "Radio Taanba", "Fada N'Gourma", "Fada N'Gourma", "Diocèse de Fada N'Gourma/Abbé Michel THIOMBIANO", "98.8 MHz"),
    (8, "Radio Evangile du Sud-Ouest", "Gaoua", "Poni", "Eglise Protestante Evangélique (EPE)/Pasteur Daniel Narcisse KAMBOU", "99.7 MHz"),
    (9, "Radio Notre Dame de la Réconciliation", "Koudougou", "Boulkiemdé", "Diocèse de Koudougou/Abbé Augustin BASSOLE", "105.8 MHz"),
    (10, "Radio Natigm-B-Zanga", "Yako", "Passoré", "Paroisse Saint Jean-Marie VIANEY de Yako/Abbé Didier Ouindbenedo KABORE", "98.2 MHz"),
    (11, "Radio Notre Dame du Sahel", "Ouahigouya", "Yatenga", "Diocèse de Ouahigouya/Abbé Victor OUEDRAOGO", "102.6 MHz"),
    (12, "Radio Lumière Vie et Développement", "Ouagadougou", "Kadiogo", "Office de Développement des Eglises de Ouagadougou/Pasteur Yéouado Etienne BAZIE", "98.1 MHz"),
    (13, "Radio Alliance Chrétienne (RAC)", "Bobo-Dioulasso", "Houet", "Eglise de l'Alliance Chrétienne du Burkina/Dieudonné SOU", "95.900 MHz"),
    (14, "Radio Notre Dame de la Paix", "Manga", "Zoundwéogo", "Diocèse de Manga/Abbé GANGA Sibegnara Etienne", "99 MHz"),
    (15, "Radio Islamique Ahmadiyya (RIA)", "Bobo-Dioulasso", "Houet", "Jama'at Islamique Ahmadiyya/Almissi Boukari OUEDRAOGO", "103.5 MHz"),
    (16, "Radio Evangile Développement (RED)", "Houndé", "Tuy", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "95.5 MHz"),
    (17, "Radio Evangile Développement (RED)", "Koudougou", "Boulkiemdé", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "95.4 MHz"),
    (18, "Al Mafaz", "Bobo-Dioulasso", "Houet", "Comité d'Appel Islamique et de Développement Socio-Economique/Ousmane OUEDRAOGO", "93.500 MHz"),
    (19, "Al Houda", "Ouagadougou", "Kadiogo", "Fondation Abdallah Ben Mas Oud/Issouf KANAZOE", "98.5 MHz"),
    (20, "Radio Evangélique Sourou/Nayala", "Tougan", "Sourou", "Eglise de l'Alliance Chrétienne District de Tougan/Dieudonné SOU", "97.900 MHz"),
    (21, "Radio de l'Alliance Chrétienne", "Banfora", "Comoé", "Eglise de l'Alliance Chrétienne, District de Banfora/Dieudonné SOU", "105.100 MHz"),
    (22, "Radio Catholique Tériya (RCT)", "Banfora", "Comoé", "Diocèse de Banfora/Abbé Jean Baptiste TRAORE", "94.700 MHz"),
    (23, "Radio Etoile", "Bobo-Dioulasso", "Houet", "Archidiocèse de Bobo-Dioulasso/Abbé Joanny SANOU", "91.800 MHz"),
    (24, "Radio Ridwane", "Ouagadougou", "Kadiogo", "Fondation Islamique Internationale de Charité/Cheick Aboubacar DOUKOURE", "100.300 MHz"),
    (25, "Radio Islamique Ahmadiyya", "Léo", "Sissili", "Jama'at Islamique Ahmadiyya/Khalid MAHAMOOD", "91.300 MHz"),
    (26, "Radio Islamique Ahmadiyya", "Dori", "Séno", "Jama'at Islamique Ahmadiyya/Khalid MAHAMOOD", "101.00 MHz"),
    (27, "Radio Maria", "Koupéla", "Kouritenga", "Diocèse de Koupéla/Abbé Nérée ZABSONRE", "96.900 MHz"),
    (28, "Radio Notre Dame de Kaya", "Kaya", "Sanmatenga", "Diocèse de Kaya/Abbé Laurent BALMA", "102.900 MHz"),
    (29, "Radio Kantigiya", "Nouna", "Kossi", "Diocèse de Nouna/Abbé Pierre SANOU", "88.800 MHz"),
    (30, "Radio de la Solidarité", "Ouahigouya", "Yatenga", "Association Fondation de l'Ecole Islamique Moderne/Yacouba BELEM", "89.900 MHz"),
    (31, "Radio Evangile Développement", "Fada N'Gourma", "Fada N'Gourma", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "94.00 MHz"),
    (32, "Radio Alliance Chrétienne (RAC)", "Nouna", "Kossi", "Eglise de l'Alliance Chrétienne/Dieudonné SOU", "106.700 MHz"),
    (33, "Radio Islamique Ahmadiyya", "Dédougou", "Mouhoun", "Jama'at Islamique Ahmadiyya/Almissi Boukari OUEDRAOGO", "107.200 MHz"),
    (34, "Radio Catholique Némaro Zien", "Léo", "Sissili", "Commission Diocésaine des Moyens de Communication Sociale de Koudougou/Abbé KOAMA Gaston", "101.300 MHz"),
    (35, "Radio El-Bethel", "Ouagadougou", "Kadiogo", "Mission Biblique Internationale d'Intercession et d'Evangélisation (MBIIE)/Pasteur Patrice TIENDREBEOGO", "95.700 MHz"),
    (36, "Radio IQRA", "Ouagadougou", "Kadiogo", "Association IQRA Burkina Faso/OUEDRAOGO AMADE Mahamoudou", "96.100 MHz"),
    (37, "Radio Evangile Développement (RED)", "Léo", "Sissili", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "97.8 MHz"),
    (38, "Radio Evangile Développement", "Yako", "Passoré", "Association Jeunesse Pour Christ Burkina/Pasteur Moussa Etienne KIEMDE", "91.700 MHz"),
    (39, "Al Fadjr", "Ouagadougou", "Kadiogo", "Communauté Musulmane du Burkina Faso/El Hadj Hamado PITROIPA", "101.6 MHz"),
    (40, "Radio Viim Koèga", "Koudougou", "Boulkiemdé", "Conseil Régional des Assemblées de Dieu de Koudougou/Pasteur Michel KOALA", "89.9 MHz"),
]
for n, name, loc, prov, prom, freq in confessional:
    entries.append(("Private", "Radio", "Confessional", n, name, loc, prov, prom, freq))

# 2. Community Radios (51) - but PDF has 50 numbered
community = [
    (1, "Radio Salankoloto", "Ouagadougou", "Kadiogo", "Association Galian/Roger NIKIEMA", "97.300 MHz"),
    (2, "La Voix du Paysan", "Ouahigouya", "Yatenga", "Fédération Nationale des Groupements Naam/Amidou GANAME", "97.00 MHz"),
    (3, "Radio Vive le paysan", "Saponé", "Bazèga", "Association Vive le Paysan de Saponé/Emmanuel ILBOUDO", "107.0 MHz"),
    (4, "Radio Palabre", "Koudougou", "Boulkiemdé", "Association Benebnooma/Blandine KY", "92.2 MHz"),
    (5, "Radio Munyu", "Banfora", "Comoé", "Association Munyu des femmes de la Comoé/L. Laurence HEMA/TRAORE", "95.2 MHz"),
    (6, "Radio Manegda", "Kaya", "Sanmatenga", "Association Action Communautaire de Développement du Centre Nord (ACD/CN)/Samuel BAMOGO", "99.4 MHz"),
    (7, "Radio Kakoadb Yam Vénégré", "Ziniaré", "Oubritenga", "Fédération des Groupements Wend-Yam de Kulkinda/Lamoussa Jean-Baptiste SAWADOGO", "107.7 MHz"),
    (8, "Radio Goulou", "Pô", "Nahouri", "Association pour la Radio Communautaire du Nahouri (ARC)/Djibrilou DACRO", "99.500 MHz"),
    (9, "Radio Nayinéré", "Boulsa", "Namentenga", "Association pour la promotion et l'intégration de la jeunesse du Centre Nord (AIPJN/CN)/Samuel BAMOGO", "92.00 MHz"),
    (10, "Radio Walde EJEF", "Gorom-Gorm", "Oudalan", "Association Walde Ejef/ZABRIOU AG Ahmed", "92.200 MHz"),
    (11, "Radio Gambidi", "Ouagadougou", "Kadiogo", "Association culturelle Gambidi/Claude GUINGANE", "97.700 MHz"),
    (12, "Radio des cotonniers", "Solenzo", "Banwa", "Union Nationale des producteurs de coton du Burkina (UNPC)/BIHOUN Bambou", "95.100 MHz"),
    (13, "Radio Némaro de Cassou (RNC)", "Kassou", "Ziro", "Association Cassou-Sâon/BENAO Betiou", "94.200 MHz"),
    (14, "Radio Pengdwendé", "Sabou", "Boulkiemdé", "Association Pengdwendé de Sabou/Georgette KOALA/NAZE", "97.400 MHz"),
    (15, "Radio Laafi", "Zorgho", "Ganzourgou", "Association African Solidarité (AAS)/Issouf TENDREBEOGO", "95.6 MHz"),
    (16, "Radio des Ecoles", "Ouagadougou", "Kadiogo", "Groupe Scolaire l'Académie/B. André-Eugène ILBOUDO", "106.400 MHz"),
    (17, "Radio Loudon", "Sapouy", "Ziro", "Association Synergie44/Germain Bitiou NAMA", "104.9 MHz"),
    (18, "Radio Pag-la-Yiri", "Zabré", "Boulgou", "Association des femmes de Zabré (AFZ) 'Pag-La-Yiri'/Suzanne OUARE", "94.300 MHz"),
    (19, "Radio Fada FM", "Fada N'Gourma", "Gourma", "Fondation Africa Horizon International/Kadiatou THIOMBIANO", "95.500 MHz"),
    (20, "Radio FM Femmes et Développement communautaire", "Arbollé", "Passoré", "Association pour la paix et la solidarité (APS)/Kevin H. OUEDRAOGO", "96.9 MHz"),
    (21, "Radio Djawoampo", "Bogandé", "Gnagna", "Association Inter-Etats des Jeunes du Liptako Gourma (AILG)/TABOUDOU Alassane", "98.00 MHz"),
    (22, "Radio Lutte contre la désertification (RLCD)", "Djibo", "Soum", "Association SOS Sahel International Burkina Faso/Daniel OUEDRAOGO", "98.6 MHz"),
    (23, "Radio Manivelle", "Dano", "Ioba", "Association Manivelle/Bessolè Jean-Pierre SOMDA", "92.00 MHz"),
    (24, "Radio Tin-Taani", "Kantchari", "Tapoa", "Association Tin-taani/Lydo HAMA", "100.00 MHz"),
    (25, "Radio Salaki", "Dédougou", "Mouhoun", "Association Salaki/Camille SAWADOGO", "100.100 MHz"),
    (26, "Radio Eveil", "Bogandé", "Gnagna", "Réseau Africain Jeunesse Santé et Développement au Burkina Faso (RAJS/D)/Bagnomboé BAKIONO", "101.500 MHz"),
    (27, "Radio Nerwaya de Kongoussi", "Kongoussi", "Bam", "Association des volontaires pour le Développement du Centre Nord (AVD/CN)/Samuel BAMOGO", "99.700 MHz"),
    (28, "Radio Pognéré", "Pouytenga", "Kouritenga", "Association Béo-Néré/Irissa YAMBA", "100.200 MHz"),
    (29, "Wuelo-Ho", "Bondounkuy", "Mouhoun", "Union départementale des producteurs de coton de Bondoukuy/TAMINI Lamoussa", "90.8 MHz"),
    (30, "Radio Baasnéré", "Tougouri", "Namentenga", "Association Baasnéré/Vincent KAFANDO", "98.500 MHz"),
    (31, "Radio Bama Pilé", "Bama", "Houet", "Association pour le développement de la radio communautaire Bama Pilé/Soumana SANOU", "97.100 MHz"),
    (32, "Radio Buamitandi", "Kantchari", "Tapoa", "Association Tin Tua/Bendi Benoît OUOBA", "93.500 MHz"),
    (33, "Faso FM", "Koubri", "Kadiogo", "Fondation Fadima/Constantin LOADA", "106.00 MHz"),
    (34, "Radio ZAAM", "Pissila", "Sanmatenga", "Association Manegmoogo/N. Emmanuel BAMOGO", "106.500 MHz"),
    (35, "Radio Dounia FM", "Boussé", "Kourwéogo", "Association pour le développement de Rintigué (ADR)/Sam OUEDRAOGO", "89.300 MHz"),
    (36, "Radio Liberté", "Ouagadougou", "Kadiogo", "Mouvement Burkinabè des Droits de l'Homme et des Peuples (MBDHP)/Chrysogone ZOUGMORE", "92.800 MHz"),
    (37, "Radio Manupugisa", "Piéla", "Gnagna", "Association Tin Tua/Yembuani Yves OUOBA", "105.100 MHz"),
    (38, "Radio Tin Tua", "Fada N'Gourma", "Gourma", "Association Tin Tua/Yembuani Yves OUOBA", "105.900 MHz"),
    (39, "Radio M'Ballo Anndal", "Tangangari", "Yagha", "Groupement M'Ballo Anndal/Hamadou Salmana CISSE", "92.500 MHz"),
    (40, "Radio La Voix des Hippopotames", "Bala", "Houet", "Association inter villageoise de gestion des ressources naturelles et de la faune des Hauts-Bassins (AGEREF/HB)/Zesoum MILLOGO", "89.200 MHz"),
    (41, "Radio Buayaba", "Diapaga", "Tapoa", "Association Communautaire Buayaba/Yamba Pierre TANKOANO", "96.2 MHz"),
    (42, "Intégration (Zekoula FM)", "Bittou", "Boulgou", "Association Béo-Néré/Irissa YAMBA", "88.00 MHz"),
    (43, "Dauphin FM", "Koupéla", "Kouritenga", "Association Réveille-Toi/Amédée Wend-La-Sida SILGA", "90.6 MHz"),
    (44, "Sud FM", "Nobéré", "Zoundwéogo", "Association Sud Développement Burkina Faso (Sud-Dev-BF)/Nicomède KAFANDO", "89.4 MHz"),
    (45, "Voix du Sahel", "Gorom-Gorm", "Oudalan", "Association d'aide à l'enfance délaissée (AAED)/L. Jean Baptiste SAWADOGO", "89.3 MHz"),
    (46, "Radio Argoutar", "Dissin", "Ioba", "Association Argoutar (AA)/T. Jean De Dieu SOMDA", "105.1 MHz"),
    (47, "Radio Développement Solidaire Durable (Radio DEVSO)", "Nadiagou (Pama)", "Kompienga", "Association Développement Solidaire Durable (DEVSO)/Habibata TOULA née BARRY", "88.3 MHz"),
    (48, "Radio Nôbiel", "Gaoua", "Poni", "Association pour la promotion de l'éducation de la fille dans le Poni (APEFP)/Bibata Mamoudou HIEN", "103.4 MHz"),
    (49, "Radio Sirebaï", "Niangoloko", "Comoé", "Association Sirebaï/Bissiri Joseph SIRIMA", "90.3 MHz"),
    (50, "Radio Woassa-La Voix du Sourou Nayala", "Yaba", "Nayala", "Association Konwôma pour le Développement (AKD)/Bakari Hubert PARE", "90.00 MHz"),
]
for n, name, loc, prov, prom, freq in community:
    entries.append(("Private", "Radio", "Community", n, name, loc, prov, prom, freq))

# 3. Commercial Radios (40)
commercial = [
    (1, "Radio de l'Amitié (Fréquence Zoodo)", "Ouahigouya", "Yatenga", "Société de radio Amitié de Ouahigouya/Adama Tréboul OUEDRAOGO", "100.400 MHz"),
    (2, "Savane FM", "Ouagadougou", "Kadiogo", "Savane communication/Charlemagne ABISSI", "103.400 MHz"),
    (3, "Radio Pulsar", "Ouagadougou", "Kadiogo", "Société de radio Pulsar Communication/François YESSO", "94.800 MHz"),
    (4, "Radio Kourita", "Koupéla", "Kouritenga", "Société de radio Kourita SARL/Adolphe LOFO", "93.700 MHz"),
    (5, "Radio Ouaga FM", "Ouagadougou", "Kadiogo", "Société de radio Ouaga FM/Joachim BAKY", "105.200 MHz"),
    (6, "Radio Balafon", "Bobo-Dioulasso", "Houet", "Société de radio Balafon de Bobo-Dioulasso/Patrice DABLE", "102.700 MHz"),
    (7, "Radio Wiskamba", "Koudougou", "Boulkiemdé", "Société de radio Wiskamba/Eric KABORE", "98.700 MHz"),
    (8, "Radio Horizon FM Fréquence Magique", "Ouagadougou", "Kadiogo", "Société de radio Horizon FM SARL/Moustapha Laabli THIOMBIANO", "104.400 MHz"),
    (9, "Radio Cascade", "Banfora", "Comoé", "Société de radio Cascade de Banfora/Jean Paul YEKPAYE", "98.00 MHz"),
    (10, "Radio Or FM", "Kaya", "Sanmatenga", "Société de radio Or FM/Idrissa COMPAORE", "96.100 MHz"),
    (11, "Radio Frontière FM", "Tenkodogo", "Boulgou", "Société de radio Frontière FM sarl/Salifou GUIGMA", "97.600 MHz"),
    (12, "Radio Grand Nord", "Dori", "Séno", "Société de radio du Grand Nord/Michel Magloire BERE", "104.600 MHz"),
    (13, "Radio FM Boulgou", "Garango", "Boulgou", "Société de radio Boulgou SARL/Zenabo LINGANI née DARGA", "101.100 MHz"),
    (14, "Radio FM Dédougou", "Dédougou", "Mouhoun", "Société de radio FM Dédougou/Abdou PARE", "102.700 MHz"),
    (15, "Radio Nabonswendé", "Pouytenga", "Kouritenga", "Société de radio Nabonswendé SARL/Hamado CISSAOGO", "103.700 MHz"),
    (16, "Radio Liberté de Parler et de Communiquer", "Bobo-Dioulasso", "Houet", "Société de radiodiffusion sonore privée commerciale LPC/Simplice TEHE GOHI", "105.500 MHz"),
    (17, "Radio Lotamu", "Solenzo", "Banwa", "Société de radiodiffusion sonore commerciale Radio Lotamu FM/Ali SERY", "101.900 MHz"),
    (18, "Radio Légende", "Ouagadougou", "Kadiogo", "Société de radio Nostalgie (SORANO)/Victor OUEDRAOGO", "94.400 MHz"),
    (19, "Soleil FM", "Ouagadougou", "Kadiogo", "Société AFRICOM SARL/Kader CISSE", "104.800 MHz"),
    (20, "La Voix des Balés", "Boromo", "Balé", "Société LASSSELI/Rasmané ZONGO", "103.600 MHz"),
    (21, "Kaya FM", "Kaya", "Sanmatenga", "Société Communicator Service SARL/Mohamado OUEDRAOGO", "92.900 MHz"),
    (22, "Radio Tilgré", "Koudougou", "Boulkiemdé", "Société Optima SARL/Ambroise Tapsoba", "102.200 MHz"),
    (23, "Radio Savane FM", "Bobo-Dioulasso", "Houet", "Société Savane Communication/Charlemagne ABISSI", "96.100 MHz"),
    (24, "Radio Bassy FM", "Ziniaré", "Oubritenga", "Société AFRICOM SARL/Kader CISSE", "98.900 MHz"),
    (25, "Savane FM Nord", "Gourcy", "Zondoma", "Société Savane Communication/Charlemagne ABISSI", "103.100 MHz"),
    (26, "Radio Jam", "Ouagadougou", "Kadiogo", "Société Master Communication (MCC)/César Auguste Roméo dit Kiswendsida Claver YAMEOGO", "92.400 MHz"),
    (27, "Radio Fémina FM", "Ouagadougou", "Kadiogo", "Entreprise Poug-Neeré/Carine Marie Michelle OUEDRAOGO née SALEMBERE", "102.800 MHz"),
    (28, "Radio Wendpanga", "Ouahigouya", "Yatenga", "Société 'Les Editions Le Pays'/Boureima Jérémie SIGUE", "95.800 MHz"),
    (29, "Unité FM", "Gourcy", "Zondoma", "Société AFRICOM SARL/Kader CISSE", "99.100 MHz"),
    (30, "Radio Légende Bobo", "Bobo-Dioulasso", "Houet", "Société SORANO/Victor OUEDRAOGO", "103.100 MHz"),
    (31, "Radio Diva", "Ouagadougou", "Kadiogo", "Société Média 2000/Valérie KABORE", "88.100 MHz"),
    (32, "Radio Oméga", "Ouagadougou", "Kadiogo", "Société Proxima/Alpha Mamadou BARRY", "103.900 MHz"),
    (33, "Radio Oméga Bobo", "Bobo-Dioulasso", "Houet", "Société Proxima/Alpha Mamadou BARRY", "104.700 MHz"),
    (34, "Radio Optima", "Ouagadougou", "Kadiogo", "Société Optima SARL/Ambroise TAPSOBA", "106.800 MHz"),
    (35, "Radio Ouaga FM", "Bobo-Dioulasso", "Houet", "Société de radiodiffusion sonore commerciale Ouaga FM/Joachim BAKY", "101.100 MHz"),
    (36, "Watamu FM (Wat FM)", "Ouagadougou", "Kadiogo", "Société Tam-Tam Productions/Achille Mondet DABIRE", "100.8 MHz"),
    (37, "Hit Radio", "Ouagadougou", "Kadiogo", "Media Holding Burkina SARL/Kodjovi Gameli KODJOAKOU", "87.7 MHz"),
    (38, "Tampani FM", "Léo", "Sissili", "Yala Compagnie SARL (YACO)/Abdoul Touahiri NIGNAN", "88.2 MHz"),
    (39, "Radio Bam", "Kongoussi", "Bam", "Azur Conseil SA/Lamine BAYALA", "96.4 MHz"),
    (40, "Radio Liberté de Parler et de Communiquer (LPC)", "Tenkodogo", "Boulgou", "Société Radio LPC/Simplice TEHE GOHI", "104.7 MHz"),
]
for n, name, loc, prov, prom, freq in commercial:
    entries.append(("Private", "Radio", "Commercial", n, name, loc, prov, prom, freq))

# 4. International Radios (4)
international = [
    (1, "Radio France Internationale (RFI)", "Ouagadougou", "Kadiogo", "Société Nationale de radiodiffusion RFI/Cécile MEGIE", "94.00 MHz"),
    (2, "Radio BBC", "Ouagadougou", "Kadiogo", "British Broadcasting Corporation", "99.200 MHz"),
    (3, "VOA", "Ouagadougou", "Kadiogo", "La Voix de l'Amérique/Gaines Johnson", "102.400 MHz"),
    (4, "Radio Médi1", "Ouagadougou", "Kadiogo", "La Radio Méditerranée internationale", "102.0 MHz"),
]
for n, name, loc, prov, prom, freq in international:
    entries.append(("Private", "Radio", "International", n, name, loc, prov, prom, freq))

# TV - Confessional (5)
tv_confessional = [
    (1, "Canal Viim Koèga (CVK)", "Ouagadougou", "Kadiogo", "Office de Développement des Eglises (ODE)", "Canal x"),
    (2, "TV Maria", "Ouagadougou", "Kadiogo", "Diocèse de Ouagadougou", "Canal x"),
    (3, "EL-Bethel TV", "Ouagadougou", "Kadiogo", "Mission Biblique Internationale d'Intercession et d'Evangélisation (MBIIE)", "Canal x"),
    (4, "Impact TV", "Ouagadougou", "Kadiogo", "Centre International d'Evangélisation/Mission Intérieure Africaine (CIE/MIA)", "Canal x"),
    (5, "TV Al Houda", "Ouagadougou", "Kadiogo", "Fondation Abdallah-Ben Mas Oud", "Canal x"),
]
for n, name, loc, prov, prom, freq in tv_confessional:
    entries.append(("Private", "TV", "Confessional", n, name, loc, prov, prom, freq))

# TV - Community (1)
entries.append(("Private", "TV", "Community", 1, "Sanmatenga Television (STV)", "Kaya", "Sanmatenga", "Association Action communautaire de Développement", "Canal x"))

# TV - Commercial (12)
tv_commercial = [
    (1, "SMTV", "Ouagadougou", "Kadiogo", "SKY SARL", "Canal x"),
    (2, "TV Canal3", "Ouagadougou", "Kadiogo", "Koen Ros", "Canal x"),
    (3, "BF1", "Ouagadougou", "Kadiogo", "Global Communication SA", "Canal x"),
    (4, "TVZ Africa", "Ouagadougou", "Kadiogo", "Société TVZ SARL", "Canal x"),
    (5, "LCA TV la chaine Africaine", "Ouagadougou", "Kadiogo", "Edifice McCANN", "Canal x"),
    (6, "Savane TV", "Ouagadougou", "Kadiogo", "SAVANE Médias", "Canal x"),
    (7, "3TV", "Ouagadougou", "Kadiogo", "TAM-TAM PRODUCTIONS", "Canal x"),
    (8, "Bi TV", "Ouagadougou", "Kadiogo", "ANSAX SA", "Canal x"),
    (9, "Omega TV", "Ouagadougou", "Kadiogo", "Groupe Radio Proxima", "Canal x"),
    (10, "Télé Citoyenne", "Tenkodogo", "Boulgou", "La Société Média 2000", "Canal x"),
    (11, "Télé Citoyenne", "Ouagadougou", "Kadiogo", "La Société Média 2000", "Canal x"),
    (12, "SMTV Bobo", "Bobo-Dioulasso", "Houet", "SKY SARL", "Canal x"),
]
for n, name, loc, prov, prom, freq in tv_commercial:
    entries.append(("Private", "TV", "Commercial", n, name, loc, prov, prom, freq))

# Satellite operators (4)
sat_ops = [
    (1, "Canal+ Afrique", "Ouagadougou", "Kadiogo", "Jean Christophe RAMOS", "Satellite"),
    (2, "TV-COM", "Ouagadougou", "Kadiogo", "Nelly KWENDE", "Satellite"),
    (3, "SatVision", "Ouagadougou", "Kadiogo", "PDG M. JOE ZIADE", "Satellite"),
    (4, "NumériSat", "Ouagadougou", "Kadiogo", "Président Mathias ZONGO", "Satellite"),
]
for n, name, loc, prov, prom, freq in sat_ops:
    entries.append(("Private", "Satellite", "Operator", n, name, loc, prov, prom, freq))

# MMDS operators (4)
mmds_ops = [
    (1, "Neerwaya Multivision", "Ouagadougou", "Kadiogo", "Société Neerwaya Multivision", "2500-2660 MHz"),
    (2, "Neerwaya Multivision", "Bobo-Dioulasso", "Houet", "Société Neerwaya Multivision", "2500-2668 MHz"),
    (3, "DSK Distribution", "Ouagadougou", "Kadiogo", "Société DSK Distribution", "2564-2684 MHz"),
    (4, "DSK Distribution", "Bobo-Dioulasso", "Houet", "Société DSK Distribution", "2596-2784 MHz"),
]
for n, name, loc, prov, prom, freq in mmds_ops:
    entries.append(("Private", "MMDS", "Operator", n, name, loc, prov, prom, freq))

# === II. PUBLIC AUDIOVISUAL MEDIA ===
# Municipal Radios (23)
municipal = [
    (1, "Radio Municipale de Ouagadougou (RMO)", "Ouagadougou", "Kadiogo", "Commune de Ouagadougou/Marin ILBOUDO", "98.9 MHz"),
    (2, "La Voix du Sourou", "Tougan", "Sourou", "Commune de Tougan/S. Mathias ZERBO", "105 MHz"),
    (3, "Radio de l'Unité", "Boudry", "Ganzourgou", "Commune de Boudry/Anatole CONGO", "95.2 MHz"),
    (4, "Radio La Voix du Lorum", "Titao", "Lorum", "Commune de Titao/Emmanuel YOUNGA", "90.7 MHz"),
    (5, "La Voix du Sanguié", "Réo", "Sanguié", "Commune de Réo/Louis BAZIMO", "102.8 MHz"),
    (6, "Sara FM Botou", "Botou", "Tapoa", "Commune de Botou/Pamba Ousmane LOMPO", "102.6 MHz"),
    (7, "Radio Lamogoya", "Tansila", "Banwa", "Commune de Tansila/Mikaël KIENOU", "101.100 MHz"),
    (8, "Radio Municipale de Sya", "Bobo-Dioulasso", "Houet", "Commune de Bobo-Dioulasso/Salia SANOU", "93.100 MHz"),
    (9, "Radio Zuba'ui-Jεkan", "Djibasso", "Kossi", "Commune de Djibasso/Edouard DEMBELE", "94.600 MHz"),
    (10, "Radio La Voix du Soum", "Djibo", "Soum", "Commune de Djibo/Oumarou DICKO", "92.100 MHz"),
    (11, "Radio Gassan", "Gassan", "Sourou", "Commune de Gassan", "94.6 MHz"),
    (12, "Radio Buama", "Gayéri", "Komandjari", "Commune de Gayéri/Soampa LOMPO", "91.8 MHz"),
    (13, "Radio Venèg buûdou", "Kombissiri", "Bazèga", "Commune urbaine de Kombissiri/Joseph OUEDRAOGO", "90.8 MHz"),
    (14, "Radio La Voix des Lacs", "Kongoussi", "Bam", "Commune de Kongoussi", "93.2 MHz"),
    (15, "Radio Kosen Kibaru", "Nouna", "Kossi", "Commune de Nouna", "91.9 MHz"),
    (16, "La Voix du Verger", "Orodara", "Kénédougou", "Commune de Orodara", "91.2 MHz"),
    (17, "Radio Sougr-nooma", "Ouargaye", "Koulpelogo", "Commune de Ouargaye/Ouindemi ZOMBRE", "92.8 MHz"),
    (18, "Radio Poura", "Poura", "Balé", "Commune de Poura", "98.00 MHz"),
    (19, "Radio Bassimyam", "Tanghin Dassouri", "Kadiogo", "Commune de Tanghin Dassouri/Adama ZONGO", "101.5 MHz"),
    (20, "Radio communale Diabo", "Diabo", "Diabo", "Commune de Diabo", "91.1 MHz"),
    (21, "Radio communale de Dori", "Dori", "Séno", "Commune de Dori", "87.9 MHz"),
    (22, "Radio Zamaana", "Kokologho", "Boulkiemdé", "Commune de Kokologho", "101 MHz"),
    (23, "Radio communale de Sebba", "Sebba", "Yagha", "Commune de Sebba", "94.7 MHz"),
]
for n, name, loc, prov, prom, freq in municipal:
    entries.append(("Public", "Radio", "Municipal", n, name, loc, prov, prom, freq))

# Institutional Radios (6)
institutional = [
    (1, "Radio Campus", "Ouagadougou", "Kadiogo", "Université de Ouagadougou/Gustave B. KABRE", "91.200 MHz"),
    (2, "La Voix du Parlement-Radio", "Ouagadougou", "Kadiogo", "Assemblée Nationale/Roch Marc Christian KABORE", "89.7 MHz"),
    (3, "Bagrépôle FM", "Bagré", "Boulgou", "Société d'Economie Mixte Bagrépôle/Issaka KARGOUGOU", "93.3 MHz"),
    (4, "Radio Scolaire Emettrice", "Ouagadougou", "Kadiogo", "Ministère de l'Education Nationale/Pr Stanislas OUARO", "107.4 MHz"),
    (5, "Unité Mobile de Production de la Radio (UMPR)", "Hauts-Bassins/Cascades/Boucle du Mouhoun/Sud-Ouest/Centre-Ouest", "Multi-region", "SOFITEX/M. Wilfried A. G. YAMEOGO", "107.6 MHz"),
    (6, "Radio Jeunesse Sahel", "G5 SAHEL", "Multi-region", "Organisation Internationale de la Francophonie (OIF)", "95.3 MHz"),
]
for n, name, loc, prov, prom, freq in institutional:
    entries.append(("Public", "Radio", "Institutional", n, name, loc, prov, prom, freq))

# State/Public Radios (7)
state_radio = [
    (1, "Radio Rurale", "Banfora", "Comoé", "RTB", "88.4 MHz"),
    (2, "Radio Bobo", "Bobo-Dioulasso", "Houet", "RTB", "89.8 MHz"),
    (3, "Radio RTB2 Hauts-Bassins", "Bobo-Dioulasso", "Houet", "RTB", "88.9 MHz"),
    (4, "Radio Gaoua", "Gaoua", "Poni", "RTB", "90.1 MHz"),
    (5, "Radio Rurale", "Gaoua", "Poni", "RTB", "93.2 MHz"),
    (6, "Radio Arc en ciel", "Ouagadougou", "Kadiogo", "RTB", "96.6 MHz"),
    (7, "RTB/Radio nationale", "Ouagadougou", "Kadiogo", "RTB", "99.9 MHz"),
]
for n, name, loc, prov, prom, freq in state_radio:
    entries.append(("Public", "Radio", "State", n, name, loc, prov, prom, freq))

# State/Public TV (4)
state_tv = [
    (1, "RTB2 Hauts-Bassins", "Bobo-Dioulasso", "Houet", "RTB", "175.25 MHz"),
    (2, "RTB/Télé", "Ouagadougou", "Kadiogo", "RTB", "175.25 MHz"),
    (3, "RTB/Gaoua", "Bobo-Dioulasso", "Houet", "RTB", ""),
    (4, "RTB", "Ouagadougou", "Kadiogo", "RTB", ""),
]
for n, name, loc, prov, prom, freq in state_tv:
    entries.append(("Public", "TV", "State", n, name, loc, prov, prom, freq))

# Institutional TV (1)
entries.append(("Public", "TV", "Institutional", 1, "LTP (Assemblée Nationale)", "Ouagadougou", "Kadiogo", "Assemblée Nationale", "Canal x"))

# === III. RELAY FREQUENCIES ===
# Confessional relay (1)
entries.append(("Relay", "Radio", "Confessional", 1, "Radio CEDICOM", "Tougan", "Sourou", "Diocèse de Dédougou", "101.4 MHz"))

# Community relay (1)
entries.append(("Relay", "Radio", "Community", 1, "Radio YIRGOU", "Barsalogo/Yirgou/Foubé", "Sanmatenga", "Association Action Communautaire de Développement du Centre-Nord (ACD/CN)", "100.5 MHz"))

# International relays (4)
intl_relays = [
    (1, "Radio France internationale", "Bobo-Dioulasso", "Houet", "RFI/Cécile MEGIE", "94.4 MHz"),
    (2, "Radio France internationale", "Koudougou", "Boulkiemdé", "RFI/Cécile MEGIE", "93.00 MHz"),
    (3, "Radio France internationale", "Banfora", "Comoé", "RFI/Cécile MEGIE", "91.5 MHz"),
    (4, "Radio France internationale", "Ouahigouya", "Yatenga", "RFI/Cécile MEGIE", "94.3 MHz"),
]
for n, name, loc, prov, prom, freq in intl_relays:
    entries.append(("Relay", "Radio", "International", n, name, loc, prov, prom, freq))

# Commercial TV relay (1)
entries.append(("Relay", "TV", "Commercial", 1, "Canal3", "Bobo-Dioulasso", "Houet", "Canal3", "559.250 MHz"))

# State radio relays (31)
state_relays = [
    (1, "RTB/Radio", "Banfora", "Comoé", "RTB", "101.5 MHz"),
    (2, "RTB/Radio", "Batié", "Nombiel", "RTB", "96.4 MHz"),
    (3, "RTB/Radio", "Bobo-Dioulasso", "Houet", "RTB", "99.4 MHz"),
    (4, "RTB/Radio Liaison", "Bobo-Dioulasso", "Houet", "RTB", "92.6 MHz"),
    (5, "RTB/Radio", "Bogandé", "Gnagna", "RTB", "91.5 MHz"),
    (6, "RTB/Radio", "Boromo", "Balé", "RTB", "91.4 MHz"),
    (7, "RTB/Radio", "Dédougou", "Mouhoun", "RTB", "90.5 MHz"),
    (8, "RTB/Radio Liaison", "Dédougou", "Mouhoun", "RTB", "93.6 MHz"),
    (9, "RTB/Radio", "Diapaga", "Tapoa", "RTB", "92.6 MHz"),
    (10, "RTB/Radio", "Diébougou", "Bougouriba", "RTB", "88.4 MHz"),
    (11, "RTB/Radio", "Djibo", "Soum", "RTB", "89.00 MHz"),
    (12, "RTB/Radio", "Dori", "Séno", "RTB", "91.00 MHz"),
    (13, "RTB/Radio", "Dori", "Séno", "RTB", "94.2 MHz"),
    (14, "RTB/Radio", "Fada N'Gourma", "Gourma", "RTB", "89.2 MHz"),
    (15, "RTB/Radio", "Fada N'Gourma", "Gourma", "RTB", "92.3 MHz"),
    (16, "RTB/Radio", "Houndé", "Tuy", "RTB", "98.3 MHz"),
    (17, "RTB/Radio", "Kaya", "Sanmatenga", "RTB", "89.8 MHz"),
    (18, "RTB/Radio", "Koudougou", "Boulkiemdé", "RTB", "89.1 MHz"),
    (19, "RTB/Radio", "Manga", "Zoundwéogo", "RTB", "92.5 MHz"),
    (20, "RTB/Radio", "Mangodara", "Comoé", "RTB", "101.5 MHz"),
    (21, "RTB/Radio Liaison", "Ouagadougou", "Kadiogo", "RTB", "88.5 MHz"),
    (22, "RTB/Radio", "Ouahigouya", "Yatenga", "RTB", "89.5 MHz"),
    (23, "RTB/Radio", "Ouahigouya", "Yatenga", "RTB", "95.5 MHz"),
    (24, "RTB/Radio", "Tougan", "Sourou", "RTB", "88.3 MHz"),
    (25, "RTB/Radio", "Orodara", "Kénédougou", "RTB", "88.1 MHz"),
    (26, "RTB/Radio", "Boulsa", "Namentenga", "RTB", "88.9 MHz"),
    (27, "RTB/Radio", "Diapaga", "Tapoa", "RTB", "89.5 MHz"),
    (28, "RTB/Radio", "Ouahigouya", "Yatenga", "RTB", "91.1 MHz"),
    (29, "RTB/Radio", "Kompienga", "Kompienga", "RTB", "91.4 MHz"),
    (30, "RTB/Radio", "Gaoua", "Poni", "RTB", "103.2 MHz"),
    (31, "RTB/Radio", "Kaya", "Sanmatenga", "RTB", "103.2 MHz"),
]
for n, name, loc, prov, prom, freq in state_relays:
    entries.append(("Relay", "Radio", "State", n, name, loc, prov, prom, freq))

# State TV relays (29)
state_tv_relays = [
    (1, "RTB/Télé", "Banfora", "Comoé", "RTB", "183.25 MHz"),
    (2, "RTB/Télé", "Bogandé", "Gnagna", "RTB", "183.25 MHz"),
    (3, "RTB/Télé", "Boromo", "Balé", "RTB", "191.25 MHz"),
    (4, "RTB/Télé", "Boulsa", "Namentenga", "RTB", "183.25 MHz"),
    (5, "RTB/Télé", "Dédougou", "Mouhoun", "RTB", "207.25 MHz"),
    (6, "RTB/Télé", "Diapaga", "Tapoa", "RTB", "207.25 MHz"),
    (7, "RTB/Télé", "Diébougou", "Bougouriba", "RTB", "207.25 MHz"),
    (8, "RTB/Télé", "Djibo", "Soum", "RTB", "207.25 MHz"),
    (9, "RTB/Télé", "Dori", "Séno", "RTB", "223.25 MHz"),
    (10, "RTB/Télé", "Fada N'Gourma", "Gourma", "RTB", "191.25 MHz"),
    (11, "RTB/Télé", "Gaoua", "Poni", "RTB", "175.25 MHz"),
    (12, "RTB/Télé", "Houndé", "Tuy", "RTB", "223.25 MHz"),
    (13, "RTB/Télé", "Kaya", "Sanmatenga", "RTB", "223.25 MHz"),
    (14, "RTB/Télé", "Kompienga", "Kompienga", "RTB", "207.25 MHz"),
    (15, "RTB/Télé", "Kongoussi", "Bam", "RTB", "223.25 MHz"),
    (16, "RTB/Télé", "Koudougou", "Boulkiemdé", "RTB", "223.25 MHz"),
    (17, "RTB/Télé", "Koudougou", "Boulkiemdé", "RTB", "663.25 MHz"),
    (18, "RTB/Télé", "Léo", "Sissili", "RTB", "199.25 MHz"),
    (19, "RTB/Télé", "Manga", "Zoundwéogo", "RTB", "215.25 MHz"),
    (20, "RTB/Télé", "Niangoloko", "Comoé", "RTB", "195.25 MHz"),
    (21, "RTB/Télé", "Nouna", "Kossi", "RTB", "175.25 MHz"),
    (22, "RTB/Télé", "Orodara", "Kénédougou", "RTB", "223.25 MHz"),
    (23, "RTB/Télé", "Ouahigouya", "Yatenga", "RTB", "183.25 MHz"),
    (24, "RTB/Télé", "Pama", "Kompienga", "RTB", "207.25 MHz"),
    (25, "RTB/Télé", "Pô", "Nahouri", "RTB", "175.25 MHz"),
    (26, "RTB/Télé", "Tenkodogo", "Boulgou", "RTB", "183.25 MHz"),
    (27, "RTB/Télé", "Tougan", "Sourou", "RTB", "215.25 MHz"),
    (28, "RTB/Télé", "Yako", "Passoré", "RTB", "199.25 MHz"),
    (29, "RTB/Télé", "Koupéla", "Kouritenga", "RTB", "207.25 MHz"),
]
for n, name, loc, prov, prom, freq in state_tv_relays:
    entries.append(("Relay", "TV", "State", n, name, loc, prov, prom, freq))

# === BUILD DATABASE ===
if os.path.exists(db_path):
    os.remove(db_path)

conn = sqlite3.connect(db_path)
c = conn.cursor()

c.execute('''CREATE TABLE broadcast_media (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sector TEXT NOT NULL,
    medium TEXT NOT NULL,
    category TEXT NOT NULL,
    num INTEGER,
    name TEXT NOT NULL,
    location TEXT,
    province TEXT,
    promoter TEXT,
    frequency TEXT,
    freq_mhz REAL
)''')

c.execute('CREATE INDEX idx_bm_sector ON broadcast_media(sector)')
c.execute('CREATE INDEX idx_bm_category ON broadcast_media(category)')
c.execute('CREATE INDEX idx_bm_location ON broadcast_media(location)')
c.execute('CREATE INDEX idx_bm_province ON broadcast_media(province)')
c.execute('CREATE INDEX idx_bm_frequency ON broadcast_media(freq_mhz)')

for sector, medium, category, num, name, location, province, promoter, frequency in entries:
    # Extract numeric MHz value
    freq_mhz = None
    m = re.search(r'([\d.]+)\s*MHz', frequency, re.IGNORECASE)
    if m:
        try:
            freq_mhz = float(m.group(1))
        except:
            pass

    c.execute('''INSERT INTO broadcast_media (sector, medium, category, num, name, location, province, promoter, frequency, freq_mhz)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
              (sector, medium, category, num, name, location, province, promoter, frequency, freq_mhz))

conn.commit()

# === EXPORT CSV ===
with open(csv_path, 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['id', 'sector', 'medium', 'category', 'num', 'name', 'location', 'province', 'promoter', 'frequency', 'freq_mhz'])
    c.execute('SELECT * FROM broadcast_media ORDER BY id')
    for row in c.fetchall():
        writer.writerow(row)

# === STATS ===
print("=" * 65)
print("  BURKINA FASO BROADCAST MEDIA DATABASE")
print("=" * 65)
print(f"  Database: {db_path}")
print(f"  CSV:      {csv_path}")
print()

c.execute("SELECT COUNT(*) FROM broadcast_media")
total = c.fetchone()[0]
print(f"  Total entries: {total}")
print()

print("-" * 65)
print("  BY SECTOR & CATEGORY")
print("-" * 65)
c.execute("SELECT sector, medium, category, COUNT(*) FROM broadcast_media GROUP BY sector, medium, category ORDER BY sector, medium, category")
for row in c.fetchall():
    print(f"  {row[0]:10s} {row[1]:10s} {row[2]:20s} {row[3]:3d}")
print()

print("-" * 65)
print("  BY PROVINCE (Top 15)")
print("-" * 65)
c.execute("SELECT province, COUNT(*) as cnt FROM broadcast_media WHERE province IS NOT NULL GROUP BY province ORDER BY cnt DESC LIMIT 15")
for row in c.fetchall():
    bar = "#" * row[1]
    print(f"  {row[0]:20s} {row[1]:3d} {bar}")
print()

print("-" * 65)
print("  FM FREQUENCY RANGE")
print("-" * 65)
c.execute("SELECT MIN(freq_mhz), MAX(freq_mhz), COUNT(*) FROM broadcast_media WHERE freq_mhz BETWEEN 87 AND 108")
mn, mx, cnt = c.fetchone()
print(f"  FM Band entries: {cnt}")
print(f"  Range: {mn} - {mx} MHz")
print()

print("-" * 65)
print("  KEY MEDIA OWNERS (by station count)")
print("-" * 65)
owners = {}
c.execute("SELECT promoter FROM broadcast_media WHERE promoter IS NOT NULL AND sector != 'Relay'")
for (p,) in c.fetchall():
    # Normalize owner names
    key = p.split('/')[0].strip() if '/' in p else p.strip()
    owners[key] = owners.get(key, 0) + 1

for owner, count in sorted(owners.items(), key=lambda x: -x[1])[:15]:
    print(f"  {owner:55s} {count:2d}")

conn.close()
print()
print(f"  Database size: {os.path.getsize(db_path):,} bytes")
print(f"  CSV size:      {os.path.getsize(csv_path):,} bytes")
print("=" * 65)