-- ============================================================================
-- Migration v4: Current Feature Update (Division/District/Thana, market items
-- quantity+unit). Run this ONCE on your existing database (phpMyAdmin > SQL tab).
-- Safe to run on a live database — only ADDs new tables/columns, never drops data.
-- Uses MariaDB's native "ADD COLUMN IF NOT EXISTS" (no stored-routine privileges
-- needed — many shared hosts deny those).
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. Division / District / Thana reference tables
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bd_divisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bd_districts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    division_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (division_id) REFERENCES bd_divisions(id) ON DELETE CASCADE,
    KEY idx_division (division_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Thana/Upazila list is extensible from the Support Portal (এলাকা ব্যবস্থাপনা
-- page) — seeded here with a starter set so the dropdowns aren't empty on day one.
CREATE TABLE IF NOT EXISTS bd_thanas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    district_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (district_id) REFERENCES bd_districts(id) ON DELETE CASCADE,
    KEY idx_district (district_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE mess_accounts
    ADD COLUMN IF NOT EXISTS division_id INT DEFAULT NULL AFTER district,
    ADD COLUMN IF NOT EXISTS district_id INT DEFAULT NULL AFTER division_id,
    ADD COLUMN IF NOT EXISTS thana_id INT DEFAULT NULL AFTER district_id;

-- ---------------------------------------------------------------------------
-- 2. Market items: quantity + unit type per product line
-- ---------------------------------------------------------------------------
ALTER TABLE market_items
    ADD COLUMN IF NOT EXISTS quantity DECIMAL(10,2) DEFAULT NULL AFTER product_name,
    ADD COLUMN IF NOT EXISTS unit VARCHAR(30) DEFAULT NULL AFTER quantity;

-- ---------------------------------------------------------------------------
-- 3. Seed data: 8 Divisions, 64 Districts (standard Bangladesh administrative
-- geography). INSERT IGNORE so this is safe to re-run.
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO bd_divisions (id, name) VALUES
(1,'Dhaka'), (2,'Chattogram'), (3,'Rajshahi'), (4,'Khulna'),
(5,'Barishal'), (6,'Sylhet'), (7,'Rangpur'), (8,'Mymensingh');

INSERT IGNORE INTO bd_districts (division_id, name) VALUES
-- Dhaka Division
(1,'Dhaka'), (1,'Faridpur'), (1,'Gazipur'), (1,'Gopalganj'), (1,'Kishoreganj'),
(1,'Madaripur'), (1,'Manikganj'), (1,'Munshiganj'), (1,'Narayanganj'), (1,'Narsingdi'),
(1,'Rajbari'), (1,'Shariatpur'), (1,'Tangail'),
-- Chattogram Division
(2,'Bandarban'), (2,'Brahmanbaria'), (2,'Chandpur'), (2,'Chattogram'), (2,'Cumilla'),
(2,'Cox\'s Bazar'), (2,'Feni'), (2,'Khagrachhari'), (2,'Lakshmipur'), (2,'Noakhali'), (2,'Rangamati'),
-- Rajshahi Division
(3,'Bogura'), (3,'Joypurhat'), (3,'Naogaon'), (3,'Natore'), (3,'Chapainawabganj'),
(3,'Pabna'), (3,'Rajshahi'), (3,'Sirajganj'),
-- Khulna Division
(4,'Bagerhat'), (4,'Chuadanga'), (4,'Jashore'), (4,'Jhenaidah'), (4,'Khulna'),
(4,'Kushtia'), (4,'Magura'), (4,'Meherpur'), (4,'Narail'), (4,'Satkhira'),
-- Barishal Division
(5,'Barguna'), (5,'Barishal'), (5,'Bhola'), (5,'Jhalokati'), (5,'Patuakhali'), (5,'Pirojpur'),
-- Sylhet Division
(6,'Habiganj'), (6,'Moulvibazar'), (6,'Sunamganj'), (6,'Sylhet'),
-- Rangpur Division
(7,'Dinajpur'), (7,'Gaibandha'), (7,'Kurigram'), (7,'Lalmonirhat'), (7,'Nilphamari'),
(7,'Panchagarh'), (7,'Rangpur'), (7,'Thakurgaon'),
-- Mymensingh Division
(8,'Jamalpur'), (8,'Mymensingh'), (8,'Netrokona'), (8,'Sherpur');

-- ---------------------------------------------------------------------------
-- 4. Thana/Upazila starter seed. Dhaka and Chandpur are seeded in full detail
-- (highest-traffic districts); every other district gets its Sadar upazila so
-- no dropdown is ever empty. Add more anytime from Support Portal > এলাকা ব্যবস্থাপনা.
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO bd_thanas (district_id, name)
SELECT id, CONCAT(name, ' Sadar') FROM bd_districts;

INSERT IGNORE INTO bd_thanas (district_id, name)
SELECT id, t.name FROM bd_districts d
JOIN (
    SELECT 'Dhamrai' AS name UNION ALL SELECT 'Dohar' UNION ALL SELECT 'Keraniganj' UNION ALL
    SELECT 'Nawabganj' UNION ALL SELECT 'Savar' UNION ALL SELECT 'Adabor' UNION ALL
    SELECT 'Badda' UNION ALL SELECT 'Bangshal' UNION ALL SELECT 'Bimanbandar' UNION ALL
    SELECT 'Cantonment' UNION ALL SELECT 'Chackbazar' UNION ALL SELECT 'Dakshinkhan' UNION ALL
    SELECT 'Darus Salam' UNION ALL SELECT 'Demra' UNION ALL SELECT 'Dhanmondi' UNION ALL
    SELECT 'Gendaria' UNION ALL SELECT 'Gulshan' UNION ALL SELECT 'Hazaribagh' UNION ALL
    SELECT 'Jatrabari' UNION ALL SELECT 'Kafrul' UNION ALL SELECT 'Kalabagan' UNION ALL
    SELECT 'Kamrangirchar' UNION ALL SELECT 'Khilgaon' UNION ALL SELECT 'Khilkhet' UNION ALL
    SELECT 'Kotwali' UNION ALL SELECT 'Lalbagh' UNION ALL SELECT 'Mirpur' UNION ALL
    SELECT 'Mohammadpur' UNION ALL SELECT 'Motijheel' UNION ALL SELECT 'Mugda' UNION ALL
    SELECT 'New Market' UNION ALL SELECT 'Pallabi' UNION ALL SELECT 'Paltan' UNION ALL
    SELECT 'Ramna' UNION ALL SELECT 'Rampura' UNION ALL SELECT 'Sabujbagh' UNION ALL
    SELECT 'Shah Ali' UNION ALL SELECT 'Shahbagh' UNION ALL SELECT 'Sher-e-Bangla Nagar' UNION ALL
    SELECT 'Shyampur' UNION ALL SELECT 'Sutrapur' UNION ALL SELECT 'Tejgaon' UNION ALL
    SELECT 'Turag' UNION ALL SELECT 'Uttara' UNION ALL SELECT 'Uttar Khan' UNION ALL
    SELECT 'Vatara' UNION ALL SELECT 'Wari'
) t ON 1=1
WHERE d.name = 'Dhaka' AND d.division_id = 1;

INSERT IGNORE INTO bd_thanas (district_id, name)
SELECT id, t.name FROM bd_districts d
JOIN (
    SELECT 'Faridganj' AS name UNION ALL SELECT 'Haimchar' UNION ALL SELECT 'Hajiganj' UNION ALL
    SELECT 'Kachua' UNION ALL SELECT 'Matlab Dakshin' UNION ALL SELECT 'Matlab Uttar' UNION ALL
    SELECT 'Shahrasti'
) t ON 1=1
WHERE d.name = 'Chandpur' AND d.division_id = 2;