USE e_notary;

INSERT INTO notary_applications (name, region, district, category, registration_type, issued_date, expired_date, applicant_name, applicant_email, applicant_phone, license_file, issued_username, status) VALUES
('Mogadishu Central Notary', 'Banaadir', 'Hodan', 'A', 'new', '2026-04-24', '2027-04-24', 'Ahmed Owner', 'owner@notary.so', '252612345678', 'uploads/license-1.pdf', 'owner@notary.so', 'active'),
('Garowe Legal Office', 'Nugaal', 'Garowe', 'B', 'new', '2026-04-24', '2027-04-24', 'Asha Garowe', 'garowe@notary.so', '252612345679', 'uploads/license-2.pdf', NULL, 'pending'),
('Bosaso Coast Notary', 'Bari', 'Bosaso', 'A', 'new', '2026-04-24', '2027-04-24', 'Bosaso Owner', 'bosaso@notary.so', '252612345680', 'uploads/license-3.pdf', NULL, 'active');

INSERT INTO notaries (id, name, region, district, category, registration_type, issued_date, expired_date, owner_user_id, status) VALUES
(1, 'Mogadishu Central Notary', 'Banaadir', 'Hodan', 'A', 'new', '2026-04-24', '2027-04-24', NULL, 'active'),
(2, 'Bosaso Coast Notary', 'Bari', 'Bosaso', 'A', 'new', '2026-04-24', '2027-04-24', NULL, 'active');

INSERT INTO users (id, notary_id, staff_id_code, name, email, password_hash, password_must_change, role) VALUES
(1, NULL, 'NESOS-2026-0001', 'Super Admin', 'admin@enotary.gov', '$2a$10$7srnDUJSy1OMlG.vjPC9zeQ6j/DuJbD2xe6azTlr63P/KAGLMyu/q', 0, 'super_admin'),
(2, NULL, 'NESOS-2026-0002', 'Justice Officer', 'justice@enotary.gov', '$2a$10$BBiWNtQl9Wp7iJaHAwLuWuGFhHL1PWX4W1p3D4F/M9Hb32ujztXbm', 0, 'ministry_justice'),
(3, NULL, 'NESOS-2026-0003', 'Finance Officer', 'finance@enotary.gov', '$2a$10$HvjTAdo0K0dEJLLFbnF4MugaWC5lvauQPaTwRU.Hte0b7xIX7vM4u', 0, 'ministry_finance'),
(4, 1, 'NESOS-2026-0004', 'Ahmed Owner', 'owner@notary.so', '$2a$10$pk7KI5PdpcQRSpeHmhIlpOT.qDHHvS4dVWtT6Heht/6kFn.CbPA3C', 0, 'owner'),
(5, 1, 'NESOS-2026-0005', 'Teller One', 'teller@notary.so', '$2a$10$yKcR63nM9EXrbQBRgtmF/u.KgNNn00JjtPO5NqZcd4UJhW.tUW8za', 0, 'teller'),
(6, 1, 'NESOS-2026-0006', 'Payment One', 'payment@notary.so', '$2a$10$p.sjF3bX/CQKPyPglV4p9ulpj8WBTCOhtf8FzaZrKCUH.gwdDkV/O', 0, 'payment'),
(7, 1, 'NESOS-2026-0007', 'Executive One', 'executive@notary.so', '$2a$10$R3vzCpUXoVFroHRTmb6CfuXNd/oaNaCdZza75na65dSgwGk8PGr/i', 0, 'executive');

UPDATE notaries SET owner_user_id = 4 WHERE id = 1;

INSERT INTO tax_rules (service_type, asset_type, min_amount, max_amount, tax_type, tax_value) VALUES
('sale', 'land', 1, 9999, 'percentage', 3),
('sale', 'land', 10000, 99999, 'percentage', 2),
('sale', 'land', 100000, 999999999.99, 'percentage', 1),
('gift', 'house', 1, 9999, 'percentage', 3),
('gift', 'house', 10000, 99999, 'percentage', 2),
('gift', 'house', 100000, 999999999.99, 'percentage', 1),
('sale', 'vehicle_small', 0, 999999999.99, 'fixed', 15),
('sale', 'vehicle_large', 0, 999999999.99, 'fixed', 20),
('sale', 'motorcycle', 0, 999999999.99, 'fixed', 10),
('sale', 'shares', 0, 999999999.99, 'percentage', 5),
('agreement', 'general', 0, 999999999.99, 'percentage', 1),
('guarantee', 'asset', 0, 999999999.99, 'percentage', 1),
('guarantee', 'other', 0, 999999999.99, 'fixed', 1),
('evidence', 'general', 0, 999999999.99, 'fixed', 2),
('power_of_attorney', 'general', 0, 999999999.99, 'fixed', 30),
('will', 'general', 0, 999999999.99, 'fixed', 1),
('boat', 'ownership', 0, 999999999.99, 'fixed', 100),
('boat', 'sale', 0, 999999999.99, 'fixed', 25),
('investment', 'general', 0, 999999999.99, 'percentage', 8);

INSERT INTO transactions (
  id, notary_id, type, asset_type, client_name, amount, tax_amount, total_amount, details, status, created_by, approved_by, created_at
) VALUES
(1001, 1, 'sale', 'land', 'Khadra Hussein', 7515.00, 225.45, 7740.45, JSON_OBJECT('parcel_no', 'BN-320', 'notes', 'Urban land sale'), 'completed', 5, 7, '2026-04-23 08:30:00'),
(1002, 1, 'investment', 'general', 'Bashir Ventures', 54000.00, 4320.00, 58320.00, JSON_OBJECT('sector', 'Port services'), 'completed', 5, 7, '2026-04-23 10:10:00'),
(1003, 1, 'evidence', 'general', 'Said Farah', 0.00, 2.00, 2.00, JSON_OBJECT('subject', 'Identity attestation'), 'completed', 5, 7, '2026-04-23 11:00:00'),
(1004, 1, 'agreement', 'general', 'Ayaan Ali', 1200.00, 12.00, 1212.00, JSON_OBJECT('subject', 'Service agreement'), 'paid', 5, NULL, '2026-04-24 07:45:00'),
(1005, 1, 'power_of_attorney', 'general', 'Abdi Noor', 0.00, 30.00, 30.00, JSON_OBJECT('represented_by', 'Hodan Osman'), 'pending', 5, NULL, '2026-04-24 08:15:00');

INSERT INTO payments (transaction_id, total, reference_number, phone, paid_at) VALUES
(1001, 7740.45, 'REF-1001', '252612345678', '2026-04-23 08:35:00'),
(1002, 58320.00, 'REF-1002', '252612345678', '2026-04-23 10:20:00'),
(1003, 2.00, 'REF-1003', '252612345678', '2026-04-23 11:05:00'),
(1004, 1212.00, 'REF-1004', '252612345678', '2026-04-24 07:50:00');

INSERT INTO audit_logs (user_id, action, description, ip_address, created_at) VALUES
(2, 'approve_application', 'Activated Mogadishu Central Notary', '127.0.0.1', '2026-04-21 09:05:00'),
(3, 'view_finance_dashboard', 'Generated finance overview', '127.0.0.1', '2026-04-24 07:15:00');
