-- ============================================================
-- Koha Subscription Management System - Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS koha_clients_db;
USE koha_clients_db;

-- Main clients table
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_key VARCHAR(255) UNIQUE NOT NULL,   -- staff domain (used to identify library from Koha)
    opac_domain VARCHAR(255),                   -- public OPAC domain (reference only)
    library_name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) DEFAULT 0.00,           -- amount client paid
    currency VARCHAR(10) DEFAULT 'INR',
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active','expired','suspended') DEFAULT 'active',
    notes VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- Insert all clients from your list
-- Dates set as placeholder 2027-01-01 / 2027-01-01 — update via admin panel
-- Price set as 0 — update via admin panel
-- ============================================================

INSERT INTO clients (client_key, opac_domain, library_name, start_date, end_date, price) VALUES
('naavustaff.bestbookbuddies.com', 'naavu.bestbookbuddies.com', 'Naavu School Bengaluru, Karnataka 560066', '2027-01-01', '2027-01-01', 0),
('vcbstaff.bestbookbuddies.com', 'vcb.bestbookbuddies.com', 'Vaish College Bhiwani', '2027-01-01', '2027-01-01', 0),
('aitkstaff.bestbookbuddies.com', 'aitk.bestbookbuddies.com', 'Allenhouse Institute Of Technology Kanpur', '2027-01-01', '2027-01-01', 0),
('scestaff.bestbookbuddies.com', 'sce.bestbookbuddies.com', 'Satyam College Of Education – Noida', '2027-01-01', '2027-01-01', 0),
('amsustaff.bestbookbuddies.com', 'amsu.bestbookbuddies.com', 'Aqaba Medical Sciences University (Amsu)', '2027-01-01', '2027-01-01', 0),
('sbscstaff.bestbookbuddies.com', 'sbsc.bestbookbuddies.com', 'Shaheed Bhagat Singh College Delhi', '2027-01-01', '2027-01-01', 0),
('kfistaff.bestbookbuddies.com', 'kfi.bestbookbuddies.com', 'The School, Kfi (Chennai)', '2027-01-01', '2027-01-01', 0),
('dpsrajstaff.bestbookbuddies.com', 'dpsraj.bestbookbuddies.com', 'Department Of Political Science, University Of Rajasthan, Jaipur', '2027-01-01', '2027-01-01', 0),
('bisstaff.bestbookbuddies.com', 'bis.bestbookbuddies.com', 'Bloomingdale International School, Vijayawada', '2027-01-01', '2027-01-01', 0),
('cmdastaff.bestbookbuddies.com', 'cmda.bestbookbuddies.com', 'Chennai Metropolitan Development Authority', '2027-01-01', '2027-01-01', 0),
('vmaconstaff.bestbookbuddies.com', 'vmacon.bestbookbuddies.com', 'Vinayaka Missions Annapoorana College Of Nursing, Salem', '2027-01-01', '2027-01-01', 0),
('vmmckstaff.bestbookbuddies.com', 'vmmck.bestbookbuddies.com', 'Vinayaka Mission Medical College Karaikal', '2027-01-01', '2027-01-01', 0),
('vmsdcstaff.bestbookbuddies.com', 'vmsdc.bestbookbuddies.com', 'Vinayaka Missions Sankarachariyar Dental College, Salem', '2027-01-01', '2027-01-01', 0),
('vnrvjitstaff.bestbookbuddies.com', 'vnrvjit.bestbookbuddies.com', 'Vallurupalli Nageswara Rao Vignana Jyothi Institute', '2027-01-01', '2027-01-01', 0),
('rdjmstaff.bestbookbuddies.com', 'rdjm.bestbookbuddies.com', 'R.D.J.M. Medical College & Hospital, Bihar', '2027-01-01', '2027-01-01', 0),
('dragonflystaff.bestbookbuddies.com', 'dragonfly.bestbookbuddies.com', 'Dragonfly Library, Myanmar', '2027-01-01', '2027-01-01', 0),
('nirtstaff.bestbookbuddies.com', 'nirt.bestbookbuddies.com', 'National Institute For Research In Tuberculosis', '2027-01-01', '2027-01-01', 0),
('chfsrstaff.bestbookbuddies.com', 'chfsr.bestbookbuddies.com', 'College Of Horticulture & Fsr Under Aau', '2027-01-01', '2027-01-01', 0),
('ritesstaff.bestbookbuddies.com', 'rites.bestbookbuddies.com', 'Rites Ltd. Gurugram', '2027-01-01', '2027-01-01', 0),
('uotstaff.bestbookbuddies.com', 'uot.bestbookbuddies.com', 'University Of Technology Jaipur', '2027-01-01', '2027-01-01', 0),
('mpsstaff.bestbookbuddies.com', 'mps.bestbookbuddies.com', 'Maheshwari International School Jaipur', '2027-01-01', '2027-01-01', 0),
('mahgustaff.bestbookbuddies.com', 'mahgu.bestbookbuddies.com', 'Maharaja Agrasen Himalayan Garhwal University', '2027-01-01', '2027-01-01', 0),
('klmdcstaff.bestbookbuddies.com', 'klmdc.bestbookbuddies.com', 'K.L Mehta Dayanand College For Women Faridabad', '2027-01-01', '2027-01-01', 0),
('mdbedstaff.bestbookbuddies.com', 'mdbed.bestbookbuddies.com', 'Maharshi Dayanand B.Ed College Jaipur', '2027-01-01', '2027-01-01', 0),
('losmstaff.bestbookbuddies.com', 'losm.bestbookbuddies.com', 'Lodha Oakwood School Mumbai', '2027-01-01', '2027-01-01', 0),
('rkgitpstaff.bestbookbuddies.com', 'rkgitp.bestbookbuddies.com', 'Raj Kumar Goel Institute Of Technology (Pharmacy)', '2027-01-01', '2027-01-01', 0),
('sisstaff.bestbookbuddies.com', 'sis.bestbookbuddies.com', 'Sreenidhi International School, Hyderabad', '2027-01-01', '2027-01-01', 0),
('pcjstaff.bestbookbuddies.com', 'pcj.bestbookbuddies.com', 'Prince College, Chomu, Jaipur', '2027-01-01', '2027-01-01', 0),
('rksstaff.bestbookbuddies.com', 'rkss.bestbookbuddies.com', 'Ramakrishna Shiksha Samiti Raghunathpura, Jaipur', '2027-01-01', '2027-01-01', 0),
('mgpostaff.bestbookbuddies.com', 'mgpo.bestbookbuddies.com', 'Gpo Mumbai India Post', '2027-01-01', '2027-01-01', 0),
('hdfsstaff.bestbookbuddies.com', 'hdfs.bestbookbuddies.com', 'H.D.Foundation School, Haldwani Uttarakhand', '2027-01-01', '2027-01-01', 0),
('pmchstaff.bestbookbuddies.com', 'pmch.bestbookbuddies.com', 'Prince Medical College & Hospital (Pmch) Sikar', '2027-01-01', '2027-01-01', 0),
('hpmsstaff.bestbookbuddies.com', 'hpms.bestbookbuddies.com', 'Hp Modi School In Bikarner Rajasthan', '2027-01-01', '2027-01-01', 0),
('fisstaff.bestbookbuddies.com', 'fis.bestbookbuddies.com', 'Finland International School, Race Course', '2027-01-01', '2027-01-01', 0),
('lwsstaff.bestbookbuddies.com', 'lws.bestbookbuddies.com', 'Lodha World School Premier', '2027-01-01', '2027-01-01', 0),
('titsstaff.bestbookbuddies.com', 'tits.bestbookbuddies.com', 'The Technological Institute Of Textile & Sciences Bhiwani', '2027-01-01', '2027-01-01', 0),
('dpsdwarkastaff.bestbookbuddies.com', 'dpsdwarka.bestbookbuddies.com', 'DPS Dwarka Delhi', '2027-01-01', '2027-01-01', 0),
('spniwcdstaff.bestbookbuddies.com', 'spniwcd.bestbookbuddies.com', 'Savitribai Phule National Institute of Women and Child Development, Delhi', '2027-01-01', '2027-01-01', 0),
('snisstaff.bestbookbuddies.com', 'snis.bestbookbuddies.com', 'Shantiniketan International School, Hyderabad', '2027-01-01', '2027-01-01', 0),
('drabedstaff.bestbookbuddies.com', 'drabed.bestbookbuddies.com', 'Dr. Ambedkar B. Ed. College, Tonk Rajasthan', '2027-01-01', '2027-01-01', 0),
('achsstaff.bestbookbuddies.com', 'achs.bestbookbuddies.com', 'Almoosa College of Health Science, Saudi Arabia', '2027-01-01', '2027-01-01', 0),
('aivstaff.bestbookbuddies.com', 'aiv.bestbookbuddies.com', 'Amrita International Vidyalayam Bengaluru', '2027-01-01', '2027-01-01', 0);
