-- Migration 017: Global privacy and data protection frameworks
-- Frameworks: UK GDPR, HITECH, GLBA, FERPA, COPPA, PIPEDA, LGPD, POPIA,
--             PDPA Singapore, PDPA Thailand, DPDP India, Australian Privacy Act,
--             NZ Privacy Act, FADP Switzerland, EU Data Act, EU Data Governance Act,
--             ePrivacy Directive, APEC CBPR

SET @ukgdpr   = 'd4e5f6a7-0017-0001-0001-000000000001';
SET @hitech   = 'd4e5f6a7-0017-0002-0001-000000000001';
SET @glba     = 'd4e5f6a7-0017-0003-0001-000000000001';
SET @ferpa    = 'd4e5f6a7-0017-0004-0001-000000000001';
SET @coppa    = 'd4e5f6a7-0017-0005-0001-000000000001';
SET @pipeda   = 'd4e5f6a7-0017-0006-0001-000000000001';
SET @lgpd     = 'd4e5f6a7-0017-0007-0001-000000000001';
SET @popia    = 'd4e5f6a7-0017-0008-0001-000000000001';
SET @pdpasg   = 'd4e5f6a7-0017-0009-0001-000000000001';
SET @pdpath   = 'd4e5f6a7-0017-0010-0001-000000000001';
SET @dpdpin   = 'd4e5f6a7-0017-0011-0001-000000000001';
SET @auprivacy= 'd4e5f6a7-0017-0012-0001-000000000001';
SET @nzprivacy= 'd4e5f6a7-0017-0013-0001-000000000001';
SET @fadp     = 'd4e5f6a7-0017-0014-0001-000000000001';
SET @eudataact= 'd4e5f6a7-0017-0015-0001-000000000001';
SET @eudga    = 'd4e5f6a7-0017-0016-0001-000000000001';
SET @eprivacy = 'd4e5f6a7-0017-0017-0001-000000000001';
SET @apeccbpr = 'd4e5f6a7-0017-0018-0001-000000000001';

-- ─── Frameworks ──────────────────────────────────────────────────────────────

INSERT IGNORE INTO regulatory_frameworks
  (id, code, name, description, version, issuing_body, is_global, effective_date)
VALUES
  (@ukgdpr, 'UK GDPR',
   'UK General Data Protection Regulation',
   'The UK''s retained version of the EU GDPR following Brexit, providing the same data protection framework with UK-specific adaptations for domestic law.',
   '2021', 'UK Parliament / ICO', FALSE, '2021-01-01'),

  (@hitech, 'HITECH Act',
   'Health Information Technology for Economic and Clinical Health Act',
   'US federal law extending HIPAA requirements to business associates, strengthening enforcement, and creating breach notification obligations for health information.',
   '2009', 'US Congress / HHS', FALSE, '2009-02-17'),

  (@glba, 'GLBA',
   'Gramm-Leach-Bliley Act',
   'US federal law requiring financial institutions to explain information-sharing practices to customers and protect sensitive data through the Financial Privacy and Safeguards Rules.',
   '2023 Safeguards Rule', 'FTC / Federal Regulators', FALSE, '1999-11-12'),

  (@ferpa, 'FERPA',
   'Family Educational Rights and Privacy Act',
   'US federal law protecting the privacy of student education records, giving parents and eligible students rights to access, correct, and control disclosure of records.',
   '2011', 'US Department of Education', FALSE, '1974-08-21'),

  (@coppa, 'COPPA',
   'Children''s Online Privacy Protection Act',
   'US federal law restricting online collection of personal information from children under 13, requiring parental consent and providing parents with rights over their children''s data.',
   '2013 Rule', 'FTC', FALSE, '2000-04-21'),

  (@pipeda, 'PIPEDA',
   'Personal Information Protection and Electronic Documents Act (Canada)',
   'Canadian federal privacy law governing how private-sector organizations collect, use, and disclose personal information in the course of commercial activities.',
   '2019', 'Government of Canada / OPC', FALSE, '2001-01-01'),

  (@lgpd, 'LGPD',
   'Lei Geral de Proteção de Dados (Brazil)',
   'Brazil''s comprehensive data protection law governing the processing of personal data by public and private entities, modeled in part on the EU GDPR.',
   '2020', 'Brazilian Government / ANPD', FALSE, '2020-09-18'),

  (@popia, 'POPIA',
   'Protection of Personal Information Act (South Africa)',
   'South African data protection law establishing conditions for lawful processing of personal information to protect the right to privacy.',
   '2021', 'South African Government / Information Regulator', FALSE, '2021-07-01'),

  (@pdpasg, 'PDPA Singapore',
   'Personal Data Protection Act (Singapore)',
   'Singapore''s data protection law governing collection, use, disclosure, and care of personal data, with mandatory data breach notification requirements.',
   '2021 Amendment', 'PDPC Singapore', FALSE, '2014-07-02'),

  (@pdpath, 'PDPA Thailand',
   'Personal Data Protection Act (Thailand)',
   'Thailand''s data protection law providing rights to data subjects and imposing obligations on data controllers and processors for the protection of personal data.',
   '2022', 'PDPC Thailand', FALSE, '2022-06-01'),

  (@dpdpin, 'DPDP Act 2023',
   'Digital Personal Data Protection Act (India)',
   'India''s data protection law governing processing of digital personal data, establishing rights for data principals and obligations for data fiduciaries.',
   '2023', 'Government of India / Ministry of Electronics', FALSE, '2023-08-11'),

  (@auprivacy, 'Australian Privacy Act',
   'Privacy Act 1988 (Australia) — Australian Privacy Principles',
   'Australian federal law governing the handling of personal information by Australian government agencies and private sector organizations with 13 Australian Privacy Principles.',
   '2022 Amendment', 'Australian Government / OAIC', FALSE, '1988-12-21'),

  (@nzprivacy, 'NZ Privacy Act 2020',
   'Privacy Act 2020 (New Zealand)',
   'New Zealand''s privacy law establishing 13 Information Privacy Principles governing collection, use, disclosure, storage, and security of personal information.',
   '2020', 'New Zealand Government / Privacy Commissioner', FALSE, '2020-12-01'),

  (@fadp, 'FADP (nFADP)',
   'Federal Act on Data Protection (Switzerland) — revised nFADP',
   'Switzerland''s revised data protection law modernizing privacy requirements for processing personal data of individuals in Switzerland, with GDPR-aligned concepts.',
   '2023', 'Swiss Federal Council / FDPIC', FALSE, '2023-09-01'),

  (@eudataact, 'EU Data Act',
   'EU Data Act (Regulation 2023/2854)',
   'EU regulation establishing rules on fair access to and use of data generated by connected products and related services across all economic sectors.',
   '2023', 'European Parliament', FALSE, '2024-09-12'),

  (@eudga, 'EU Data Governance Act',
   'EU Data Governance Act (Regulation 2022/868)',
   'EU regulation creating a framework for data sharing intermediaries, data altruism organizations, and reuse of public sector data to build a European data space.',
   '2022', 'European Parliament', FALSE, '2023-09-24'),

  (@eprivacy, 'ePrivacy Directive',
   'EU ePrivacy Directive (2002/58/EC as amended)',
   'EU directive governing electronic communications privacy including rules on cookies, spam, location data, traffic data, and confidentiality of communications.',
   '2009 Amendment', 'European Parliament', FALSE, '2003-07-31'),

  (@apeccbpr, 'APEC CBPR',
   'APEC Cross-Border Privacy Rules System',
   'Voluntary accountability-based system enabling businesses to certify their personal information handling practices against APEC Privacy Framework principles for cross-border data transfers.',
   '2022', 'APEC / CBPR Joint Oversight Panel', FALSE, '2011-11-01');


-- ═══════════════════════════════════════════════════════════════════════════════
-- UK GDPR — Key Article Groups
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ukgdpr, 'Art.5-11',   'Principles and Lawfulness of Processing',
 'Core data protection principles (Art.5), lawful bases for processing (Art.6), special category data (Art.9), and criminal convictions data (Art.10).',
 'mandatory', 'UK GDPR Chapters', 10),
(UUID(), @ukgdpr, 'Art.12-22',  'Rights of the Data Subject',
 'Data subject rights including transparency (Art.12-14), access (Art.15), rectification (Art.16), erasure (Art.17), restriction (Art.18), portability (Art.20), and objection (Art.21).',
 'mandatory', 'UK GDPR Chapters', 20),
(UUID(), @ukgdpr, 'Art.24-43',  'Controller and Processor Obligations',
 'Obligations on controllers (Art.24), data protection by design (Art.25), records of processing (Art.30), security (Art.32), DPIAs (Art.35), and DPO designation (Art.37).',
 'mandatory', 'UK GDPR Chapters', 30),
(UUID(), @ukgdpr, 'Art.44-49',  'Transfers to Third Countries',
 'Requirements for transferring personal data to third countries including adequacy decisions (Art.45), appropriate safeguards (Art.46), and derogations (Art.49).',
 'mandatory', 'UK GDPR Chapters', 40),
(UUID(), @ukgdpr, 'Art.55-76',  'Supervisory Authority (ICO)',
 'Role and powers of the Information Commissioner''s Office including competence (Art.55), tasks (Art.57), powers (Art.58), and cooperation mechanisms.',
 'mandatory', 'UK GDPR Chapters', 50),
(UUID(), @ukgdpr, 'Art.82-84',  'Remedies, Liability and Penalties',
 'Right to compensation (Art.82), liability and exemptions (Art.83 UK fines up to £17.5M or 4% global turnover), and penalties for member state rules.',
 'mandatory', 'UK GDPR Chapters', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- HITECH Act — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @hitech, 'HIT-A',  'Subtitle A: Promotion of Health Information Technology',
 'Extends HIPAA Privacy and Security Rules directly to business associates, imposes civil and criminal penalties, and strengthens enforcement with increased fines.',
 'mandatory', 'HITECH Subtitles', 10),
(UUID(), @hitech, 'HIT-B',  'Subtitle B: Incentives for Use of Health Information Technology',
 'Provides requirements for meaningful use of EHR systems and performance measures related to health information technology adoption.',
 'mandatory', 'HITECH Subtitles', 20),
(UUID(), @hitech, 'HIT-D',  'Subtitle D: Privacy',
 'Mandates breach notification to affected individuals, HHS, and media for unsecured PHI breaches; limits marketing and sale of PHI; strengthens patient rights.',
 'mandatory', 'HITECH Subtitles', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- GLBA — Three Rules
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @glba, 'GLBA-FPR',  'Financial Privacy Rule',
 'Requires financial institutions to give customers privacy notices explaining information-sharing practices and provide opt-out rights for sharing with non-affiliated third parties.',
 'mandatory', 'GLBA Rules', 10),
(UUID(), @glba, 'GLBA-SR',   'Safeguards Rule (2023)',
 'Requires financial institutions to develop and maintain a comprehensive written information security program with administrative, technical, and physical safeguards for customer information.',
 'mandatory', 'GLBA Rules', 20),
(UUID(), @glba, 'GLBA-PP',   'Pretexting Protection',
 'Prohibits using false pretenses to obtain customer financial information and requires financial institutions to have policies and procedures to detect and prevent pretexting.',
 'mandatory', 'GLBA Rules', 30);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FERPA — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @ferpa, 'FERPA-R',   'Rights of Parents and Eligible Students',
 'Parents have the right to inspect and review education records; rights transfer to the student (eligible student) at age 18 or upon attending postsecondary school.',
 'mandatory', 'FERPA Sections', 10),
(UUID(), @ferpa, 'FERPA-D',   'Disclosure Limitations',
 'Schools must have written consent before disclosing education records, with exceptions for school officials, transfers, audits, safety emergencies, and judicial orders.',
 'mandatory', 'FERPA Sections', 20),
(UUID(), @ferpa, 'FERPA-DI',  'Directory Information',
 'Schools may disclose directory information without consent if they notify parents/students of the information they have designated as directory information and allow opt-out.',
 'mandatory', 'FERPA Sections', 30),
(UUID(), @ferpa, 'FERPA-E',   'Enforcement',
 'The Family Policy Compliance Office enforces FERPA; failure to comply can result in withholding of federal education funding from institutions.',
 'mandatory', 'FERPA Sections', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- COPPA — Requirements
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @coppa, 'COPPA-N',   'Notice',
 'Post clear and prominent privacy notices on websites and online services directed to children under 13, describing collection, use, and disclosure practices.',
 'mandatory', 'COPPA Requirements', 10),
(UUID(), @coppa, 'COPPA-PC',  'Parental Consent',
 'Obtain verifiable parental consent before collecting, using, or disclosing personal information from children under 13.',
 'mandatory', 'COPPA Requirements', 20),
(UUID(), @coppa, 'COPPA-PR',  'Parental Rights',
 'Provide parents with the ability to review, correct, and delete their child''s information, and to refuse further collection or use.',
 'mandatory', 'COPPA Requirements', 30),
(UUID(), @coppa, 'COPPA-S',   'Security',
 'Establish and maintain reasonable security procedures and practices appropriate to the sensitivity of children''s personal information.',
 'mandatory', 'COPPA Requirements', 40),
(UUID(), @coppa, 'COPPA-DR',  'Data Retention and Deletion',
 'Retain children''s personal information only as long as is reasonably necessary for the purpose collected, and delete it using reasonable security measures.',
 'mandatory', 'COPPA Requirements', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PIPEDA — 10 Fair Information Principles
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @pipeda, 'PIPEDA-1',  'Principle 1: Accountability',
 'An organization is responsible for personal information under its control and shall designate one or more persons accountable for compliance.',
 'mandatory', 'PIPEDA Principles', 10),
(UUID(), @pipeda, 'PIPEDA-2',  'Principle 2: Identifying Purposes',
 'The purposes for which personal information is collected shall be identified by the organization at or before the time of collection.',
 'mandatory', 'PIPEDA Principles', 20),
(UUID(), @pipeda, 'PIPEDA-3',  'Principle 3: Consent',
 'The knowledge and consent of the individual are required for the collection, use, or disclosure of personal information, with limited exceptions.',
 'mandatory', 'PIPEDA Principles', 30),
(UUID(), @pipeda, 'PIPEDA-4',  'Principle 4: Limiting Collection',
 'The collection of personal information shall be limited to that which is necessary for the purposes identified by the organization.',
 'mandatory', 'PIPEDA Principles', 40),
(UUID(), @pipeda, 'PIPEDA-5',  'Principle 5: Limiting Use, Disclosure and Retention',
 'Personal information shall not be used or disclosed for purposes other than those for which it was collected; retained only as long as necessary.',
 'mandatory', 'PIPEDA Principles', 50),
(UUID(), @pipeda, 'PIPEDA-6',  'Principle 6: Accuracy',
 'Personal information shall be as accurate, complete, and up-to-date as is necessary for the purposes for which it is to be used.',
 'mandatory', 'PIPEDA Principles', 60),
(UUID(), @pipeda, 'PIPEDA-7',  'Principle 7: Safeguards',
 'Personal information shall be protected by security safeguards appropriate to the sensitivity of the information.',
 'mandatory', 'PIPEDA Principles', 70),
(UUID(), @pipeda, 'PIPEDA-8',  'Principle 8: Openness',
 'An organization shall make readily available to individuals specific information about its policies and practices relating to the management of personal information.',
 'mandatory', 'PIPEDA Principles', 80),
(UUID(), @pipeda, 'PIPEDA-9',  'Principle 9: Individual Access',
 'Upon request, an individual shall be informed of the existence, use, and disclosure of his or her personal information and shall be given access to that information.',
 'mandatory', 'PIPEDA Principles', 90),
(UUID(), @pipeda, 'PIPEDA-10', 'Principle 10: Challenging Compliance',
 'An individual shall be able to address a challenge concerning compliance with the above principles to the designated accountable person.',
 'mandatory', 'PIPEDA Principles', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- LGPD (Brazil) — Chapters
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @lgpd, 'LGPD-I',    'General Provisions and Scope',
 'Establishes the legal basis for data protection in Brazil, defines key terms, and determines the scope of application including territorial application.',
 'mandatory', 'LGPD Chapters', 10),
(UUID(), @lgpd, 'LGPD-II',   'Data Processing Principles',
 'Sets out the ten principles governing personal data processing: purpose, adequacy, necessity, free access, quality, transparency, security, prevention, non-discrimination, and accountability.',
 'mandatory', 'LGPD Chapters', 20),
(UUID(), @lgpd, 'LGPD-III',  'Data Subject Rights',
 'Grants data subjects rights to confirmation, access, correction, anonymization/deletion, portability, information on sharing, non-consent consequences, and revocation.',
 'mandatory', 'LGPD Chapters', 30),
(UUID(), @lgpd, 'LGPD-IV',   'Processing Conditions and Lawful Bases',
 'Defines 10 lawful bases for processing personal data including consent, legitimate interest, legal obligation, contract performance, and research.',
 'mandatory', 'LGPD Chapters', 40),
(UUID(), @lgpd, 'LGPD-V',    'Data Controller and Processor',
 'Establishes responsibilities of controllers and processors, requirements for data processing agreements, and joint controller arrangements.',
 'mandatory', 'LGPD Chapters', 50),
(UUID(), @lgpd, 'LGPD-VI',   'Data Protection Officer (DPO)',
 'Requires controllers to designate a DPO (encarregado), defines DPO responsibilities, and establishes communication channels with data subjects and ANPD.',
 'mandatory', 'LGPD Chapters', 60),
(UUID(), @lgpd, 'LGPD-VII',  'Security, Good Practices and Incidents',
 'Requires technical and organizational security measures, incident response and breach notification to ANPD and affected data subjects.',
 'mandatory', 'LGPD Chapters', 70),
(UUID(), @lgpd, 'LGPD-VIII', 'National Data Protection Authority (ANPD)',
 'Establishes the ANPD, its structure, powers, and functions for overseeing compliance with LGPD and issuing guidelines.',
 'mandatory', 'LGPD Chapters', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- POPIA (South Africa) — 8 Conditions
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @popia, 'POPIA-C1',  'Condition 1: Accountability',
 'Responsible parties must ensure and demonstrate compliance with POPIA conditions for all personal information processing.',
 'mandatory', 'POPIA Conditions', 10),
(UUID(), @popia, 'POPIA-C2',  'Condition 2: Processing Limitation',
 'Personal information may only be processed if it is adequate, relevant, and not excessive given the purpose; data minimization principle.',
 'mandatory', 'POPIA Conditions', 20),
(UUID(), @popia, 'POPIA-C3',  'Condition 3: Purpose Specification',
 'Personal information must be collected for a specific, explicitly defined, and lawful purpose and not processed in a way incompatible with that purpose.',
 'mandatory', 'POPIA Conditions', 30),
(UUID(), @popia, 'POPIA-C4',  'Condition 4: Further Processing Limitation',
 'Further processing must be compatible with the purpose of collection; records may not be kept longer than necessary for the purpose.',
 'mandatory', 'POPIA Conditions', 40),
(UUID(), @popia, 'POPIA-C5',  'Condition 5: Information Quality',
 'Responsible parties must take reasonable steps to ensure personal information is complete, accurate, not misleading, and updated where necessary.',
 'mandatory', 'POPIA Conditions', 50),
(UUID(), @popia, 'POPIA-C6',  'Condition 6: Openness',
 'Data subjects must be notified when personal information about them is collected, including the identity of the responsible party and the purpose of collection.',
 'mandatory', 'POPIA Conditions', 60),
(UUID(), @popia, 'POPIA-C7',  'Condition 7: Security Safeguards',
 'Responsible parties must secure the integrity and confidentiality of personal information in their possession by implementing appropriate technical and organizational measures.',
 'mandatory', 'POPIA Conditions', 70),
(UUID(), @popia, 'POPIA-C8',  'Condition 8: Data Subject Participation',
 'Data subjects have the right to request access to, correct, or object to processing of their personal information and to request deletion.',
 'mandatory', 'POPIA Conditions', 80);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PDPA Singapore — Obligations
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @pdpasg, 'PDPA-SG-1',  'Consent Obligation',
 'Obtain consent before collecting, using, or disclosing personal data unless an exception applies; deemed consent and legitimate interests provisions apply.',
 'mandatory', 'PDPA Singapore Obligations', 10),
(UUID(), @pdpasg, 'PDPA-SG-2',  'Purpose Limitation Obligation',
 'Collect, use, or disclose personal data only for purposes that a reasonable person would consider appropriate in the circumstances.',
 'mandatory', 'PDPA Singapore Obligations', 20),
(UUID(), @pdpasg, 'PDPA-SG-3',  'Notification Obligation',
 'Notify individuals of the purposes for which their personal data is being collected, used, or disclosed on or before collection.',
 'mandatory', 'PDPA Singapore Obligations', 30),
(UUID(), @pdpasg, 'PDPA-SG-4',  'Access and Correction Obligation',
 'Provide individuals access to their personal data and correct any error or omission upon request within a reasonable timeframe.',
 'mandatory', 'PDPA Singapore Obligations', 40),
(UUID(), @pdpasg, 'PDPA-SG-5',  'Accuracy Obligation',
 'Make reasonable effort to ensure personal data collected is accurate and complete for the purpose it will be used.',
 'mandatory', 'PDPA Singapore Obligations', 50),
(UUID(), @pdpasg, 'PDPA-SG-6',  'Protection Obligation',
 'Protect personal data in possession or under control by making reasonable security arrangements to prevent unauthorized access, collection, use, or disclosure.',
 'mandatory', 'PDPA Singapore Obligations', 60),
(UUID(), @pdpasg, 'PDPA-SG-7',  'Retention Limitation Obligation',
 'Cease retaining personal data or dispose of it in a secure manner when it is no longer necessary for any business or legal purpose.',
 'mandatory', 'PDPA Singapore Obligations', 70),
(UUID(), @pdpasg, 'PDPA-SG-8',  'Transfer Limitation Obligation',
 'Transfer personal data outside Singapore only to countries with comparable data protection standards or under contractual arrangements.',
 'mandatory', 'PDPA Singapore Obligations', 80),
(UUID(), @pdpasg, 'PDPA-SG-9',  'Openness Obligation',
 'Make information about data protection policies and practices available on request and designate a Data Protection Officer.',
 'mandatory', 'PDPA Singapore Obligations', 90),
(UUID(), @pdpasg, 'PDPA-SG-10', 'Data Breach Notification Obligation',
 'Notify PDPC within 3 days and affected individuals without undue delay for breaches that result in significant harm or affect 500 or more individuals.',
 'mandatory', 'PDPA Singapore Obligations', 100);


-- ═══════════════════════════════════════════════════════════════════════════════
-- PDPA Thailand — Chapters
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @pdpath, 'PDPA-TH-1',  'General Provisions',
 'Defines the scope, definitions, and application of PDPA Thailand; establishes lawful bases for processing personal data.',
 'mandatory', 'PDPA Thailand Chapters', 10),
(UUID(), @pdpath, 'PDPA-TH-2',  'Rights of Data Subject',
 'Grants data subjects rights to access, portability, correction, deletion, restriction, and objection to processing of personal data.',
 'mandatory', 'PDPA Thailand Chapters', 20),
(UUID(), @pdpath, 'PDPA-TH-3',  'Duties of Data Controller and Processor',
 'Establishes obligations for data controllers including DPO designation, record keeping, DPIAs, breach notification, and security measures.',
 'mandatory', 'PDPA Thailand Chapters', 30),
(UUID(), @pdpath, 'PDPA-TH-4',  'International Data Transfers',
 'Restricts transfers of personal data to countries with adequate protection standards or under appropriate safeguards such as standard contractual clauses.',
 'mandatory', 'PDPA Thailand Chapters', 40),
(UUID(), @pdpath, 'PDPA-TH-5',  'Enforcement and Penalties',
 'Establishes the Personal Data Protection Committee (PDPC), its powers, and administrative fines up to THB 5M for violations.',
 'mandatory', 'PDPA Thailand Chapters', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- DPDP Act 2023 (India) — Obligations
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @dpdpin, 'DPDP-1',  'Lawful Processing and Grounds',
 'Personal data may be processed only for a lawful purpose with consent or under certain legitimate uses; special provisions for children.',
 'mandatory', 'DPDP Obligations', 10),
(UUID(), @dpdpin, 'DPDP-2',  'Notice Requirement',
 'Data fiduciaries must provide clear and plain language notice to data principals describing the personal data to be processed and the purpose.',
 'mandatory', 'DPDP Obligations', 20),
(UUID(), @dpdpin, 'DPDP-3',  'Consent Management',
 'Consent must be free, specific, informed, unconditional, and unambiguous; data principals have the right to withdraw consent at any time.',
 'mandatory', 'DPDP Obligations', 30),
(UUID(), @dpdpin, 'DPDP-4',  'Rights of Data Principals',
 'Data principals have rights to access information, correct and erase data, nominate a representative, and seek grievance redressal.',
 'mandatory', 'DPDP Obligations', 40),
(UUID(), @dpdpin, 'DPDP-5',  'Data Fiduciary Duties',
 'Data fiduciaries must ensure data quality, implement security safeguards, report breaches to DPBI and data principals, and delete data when no longer needed.',
 'mandatory', 'DPDP Obligations', 50),
(UUID(), @dpdpin, 'DPDP-6',  'Cross-Border Data Transfers',
 'Transfer of personal data outside India is permitted only to notified countries or territories as specified by the central government.',
 'mandatory', 'DPDP Obligations', 60),
(UUID(), @dpdpin, 'DPDP-7',  'Penalties and Enforcement',
 'The Data Protection Board of India may impose financial penalties up to INR 250 crore for breaches; Right to approach courts preserved.',
 'mandatory', 'DPDP Obligations', 70);


-- ═══════════════════════════════════════════════════════════════════════════════
-- Australian Privacy Act — 13 Australian Privacy Principles (APPs)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @auprivacy, 'APP-1',  'APP 1: Open and Transparent Management of Personal Information',
 'Organizations must manage personal information openly and transparently, maintaining a clearly expressed up-to-date APP privacy policy.',
 'mandatory', 'Australian Privacy Principles', 10),
(UUID(), @auprivacy, 'APP-2',  'APP 2: Anonymity and Pseudonymity',
 'Individuals must have the option of dealing with organizations anonymously or under a pseudonym, where lawful and practicable.',
 'mandatory', 'Australian Privacy Principles', 20),
(UUID(), @auprivacy, 'APP-3',  'APP 3: Collection of Solicited Personal Information',
 'Organizations may only collect personal information that is reasonably necessary; sensitive information requires consent or a legally recognized exception.',
 'mandatory', 'Australian Privacy Principles', 30),
(UUID(), @auprivacy, 'APP-4',  'APP 4: Dealing with Unsolicited Personal Information',
 'Organizations must determine whether they could have collected unsolicited personal information under APP 3; if not, destroy or de-identify it.',
 'mandatory', 'Australian Privacy Principles', 40),
(UUID(), @auprivacy, 'APP-5',  'APP 5: Notification of Collection',
 'At or before the time of collection, organizations must take reasonable steps to notify individuals of the matters set out in this principle.',
 'mandatory', 'Australian Privacy Principles', 50),
(UUID(), @auprivacy, 'APP-6',  'APP 6: Use or Disclosure of Personal Information',
 'Personal information may only be used or disclosed for the primary purpose it was collected, or under specified secondary purpose exceptions.',
 'mandatory', 'Australian Privacy Principles', 60),
(UUID(), @auprivacy, 'APP-7',  'APP 7: Direct Marketing',
 'Personal information may not be used for direct marketing without consent; individuals must be provided an opt-out mechanism.',
 'mandatory', 'Australian Privacy Principles', 70),
(UUID(), @auprivacy, 'APP-8',  'APP 8: Cross-Border Disclosure',
 'Before disclosing personal information overseas, organizations must take reasonable steps to ensure the recipient does not breach the APPs.',
 'mandatory', 'Australian Privacy Principles', 80),
(UUID(), @auprivacy, 'APP-9',  'APP 9: Government Related Identifiers',
 'Organizations must not adopt, use, or disclose a government related identifier of an individual unless an exception applies.',
 'mandatory', 'Australian Privacy Principles', 90),
(UUID(), @auprivacy, 'APP-10', 'APP 10: Quality of Personal Information',
 'Organizations must take reasonable steps to ensure personal information collected, used, or disclosed is accurate, up-to-date, and complete.',
 'mandatory', 'Australian Privacy Principles', 100),
(UUID(), @auprivacy, 'APP-11', 'APP 11: Security of Personal Information',
 'Organizations must take reasonable steps to protect personal information from misuse, interference, loss, and unauthorized access, modification, or disclosure.',
 'mandatory', 'Australian Privacy Principles', 110),
(UUID(), @auprivacy, 'APP-12', 'APP 12: Access to Personal Information',
 'Organizations must give individuals access to their personal information on request, unless a specific access refusal ground applies.',
 'mandatory', 'Australian Privacy Principles', 120),
(UUID(), @auprivacy, 'APP-13', 'APP 13: Correction of Personal Information',
 'Organizations must take reasonable steps to correct personal information to ensure it is accurate, up-to-date, complete, relevant, and not misleading.',
 'mandatory', 'Australian Privacy Principles', 130);


-- ═══════════════════════════════════════════════════════════════════════════════
-- NZ Privacy Act 2020 — 13 Information Privacy Principles (IPPs)
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @nzprivacy, 'IPP-1',  'IPP 1: Purpose of Collection',
 'Personal information shall not be collected unless the purpose of collection is connected with a lawful function of the agency.',
 'mandatory', 'NZ Privacy Principles', 10),
(UUID(), @nzprivacy, 'IPP-2',  'IPP 2: Source of Personal Information',
 'Personal information shall be collected directly from the individual concerned, with limited exceptions.',
 'mandatory', 'NZ Privacy Principles', 20),
(UUID(), @nzprivacy, 'IPP-3',  'IPP 3: Collection of Information from Subject',
 'Where personal information is collected from the individual, the agency must inform the individual of the purposes of collection.',
 'mandatory', 'NZ Privacy Principles', 30),
(UUID(), @nzprivacy, 'IPP-4',  'IPP 4: Manner of Collection',
 'Personal information shall not be collected by unlawful means, or by means that in the circumstances are unfair or intrude to an unreasonable extent on the personal affairs of the individual.',
 'mandatory', 'NZ Privacy Principles', 40),
(UUID(), @nzprivacy, 'IPP-5',  'IPP 5: Storage and Security',
 'Agencies that hold personal information shall ensure the information is protected by such security safeguards as are reasonable in the circumstances.',
 'mandatory', 'NZ Privacy Principles', 50),
(UUID(), @nzprivacy, 'IPP-6',  'IPP 6: Access to Personal Information',
 'Individuals are entitled to obtain confirmation of whether an agency holds personal information about them and to have access to that information.',
 'mandatory', 'NZ Privacy Principles', 60),
(UUID(), @nzprivacy, 'IPP-7',  'IPP 7: Correction of Personal Information',
 'Individuals are entitled to request correction of personal information where they believe it to be inaccurate, incomplete, or misleading.',
 'mandatory', 'NZ Privacy Principles', 70),
(UUID(), @nzprivacy, 'IPP-8',  'IPP 8: Accuracy of Personal Information',
 'Agencies shall not use personal information without taking reasonable steps to ensure it is accurate, up-to-date, complete, and relevant.',
 'mandatory', 'NZ Privacy Principles', 80),
(UUID(), @nzprivacy, 'IPP-9',  'IPP 9: Retention',
 'Agencies shall not keep personal information for longer than is required for the purpose for which the information may lawfully be used.',
 'mandatory', 'NZ Privacy Principles', 90),
(UUID(), @nzprivacy, 'IPP-10', 'IPP 10: Limits on Use of Personal Information',
 'Agencies shall not use personal information for purposes other than those for which it was collected unless an exception applies.',
 'mandatory', 'NZ Privacy Principles', 100),
(UUID(), @nzprivacy, 'IPP-11', 'IPP 11: Limits on Disclosure of Personal Information',
 'Agencies shall not disclose personal information unless the disclosure is directly related to the purpose of collection or an exception applies.',
 'mandatory', 'NZ Privacy Principles', 110),
(UUID(), @nzprivacy, 'IPP-12', 'IPP 12: Unique Identifiers',
 'Agencies shall not assign unique identifiers to individuals except where necessary; shall not require individuals to disclose their unique identifiers.',
 'mandatory', 'NZ Privacy Principles', 120),
(UUID(), @nzprivacy, 'IPP-13', 'IPP 13: Transfers Overseas',
 'Agencies shall not transfer personal information outside New Zealand unless the receiving country has comparable privacy protections or consent is obtained.',
 'mandatory', 'NZ Privacy Principles', 130);


-- ═══════════════════════════════════════════════════════════════════════════════
-- FADP Switzerland (nFADP) — Sections
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @fadp, 'FADP-1',  'Scope and Definitions',
 'Applies to processing of personal data of natural persons in Switzerland by private persons and federal bodies; excludes household activities.',
 'mandatory', 'FADP Sections', 10),
(UUID(), @fadp, 'FADP-2',  'Data Processing Principles',
 'Data must be processed lawfully, in good faith, proportionately; purpose specification and accuracy must be maintained throughout processing.',
 'mandatory', 'FADP Sections', 20),
(UUID(), @fadp, 'FADP-3',  'Obligations of Controllers',
 'Controllers must implement data protection by design and default, maintain records of processing activities, conduct DPIAs, and designate a DSCO where required.',
 'mandatory', 'FADP Sections', 30),
(UUID(), @fadp, 'FADP-4',  'Rights of Data Subjects',
 'Data subjects have rights to information about their data, access to their data, correction, deletion, and data portability.',
 'mandatory', 'FADP Sections', 40),
(UUID(), @fadp, 'FADP-5',  'Cross-Border Data Transfers',
 'Personal data may be transferred abroad only to countries with adequate protection or under appropriate safeguards (SCCs, BCRs, or approved certifications).',
 'mandatory', 'FADP Sections', 50),
(UUID(), @fadp, 'FADP-6',  'Enforcement and Penalties',
 'The Federal Data Protection and Information Commissioner (FDPIC) oversees compliance; criminal sanctions of up to CHF 250,000 for violations.',
 'mandatory', 'FADP Sections', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- EU Data Act — Chapters
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @eudataact, 'DA-1',  'B2B and B2C Data Sharing (Connected Products)',
 'Users of connected products have the right to access data generated by use of those products; manufacturers must design products to make data accessible by default.',
 'mandatory', 'EU Data Act Chapters', 10),
(UUID(), @eudataact, 'DA-2',  'Switching Cloud Providers',
 'Cloud service providers must implement technical standards and contractual provisions enabling customers to switch providers without obstacles or excessive costs.',
 'mandatory', 'EU Data Act Chapters', 20),
(UUID(), @eudataact, 'DA-3',  'Essential Requirements for Smart Contracts',
 'Data sharing smart contracts must meet essential requirements for security, robustness, and termination mechanisms to ensure enforceable data access rights.',
 'mandatory', 'EU Data Act Chapters', 30),
(UUID(), @eudataact, 'DA-4',  'International Data Transfers',
 'Providers of data processing services must implement technical and legal safeguards to prevent unlawful government access to non-personal data.',
 'mandatory', 'EU Data Act Chapters', 40),
(UUID(), @eudataact, 'DA-5',  'Enforcement',
 'Member states designate competent authorities to enforce the Data Act; penalties must be effective, proportionate, and dissuasive.',
 'mandatory', 'EU Data Act Chapters', 50);


-- ═══════════════════════════════════════════════════════════════════════════════
-- EU Data Governance Act — Chapters
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @eudga, 'DGA-1',  'Reuse of Public Sector Data',
 'Establishes conditions and procedures for reuse of certain categories of protected public sector data (personal, IP-protected, confidential) held by public sector bodies.',
 'mandatory', 'EU DGA Chapters', 10),
(UUID(), @eudga, 'DGA-2',  'Data Sharing Services and Intermediaries',
 'Providers of data intermediary services must notify authorities, comply with conditions ensuring neutrality, and may not use shared data for own purposes.',
 'mandatory', 'EU DGA Chapters', 20),
(UUID(), @eudga, 'DGA-3',  'Data Altruism',
 'Entities registered as recognized data altruism organizations may collect data for general interest purposes; must use a standardized consent form.',
 'recommended', 'EU DGA Chapters', 30),
(UUID(), @eudga, 'DGA-4',  'European Data Innovation Board (EDIB)',
 'Establishes the EDIB to advise the Commission on data sharing practices, standards, and cross-border data flows in the European data space.',
 'mandatory', 'EU DGA Chapters', 40);


-- ═══════════════════════════════════════════════════════════════════════════════
-- ePrivacy Directive — Article Groups
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @eprivacy, 'ePD-5',   'Confidentiality of Communications',
 'Member states shall prohibit interception and surveillance of electronic communications and related traffic data without user consent.',
 'mandatory', 'ePrivacy Articles', 10),
(UUID(), @eprivacy, 'ePD-6',   'Traffic Data',
 'Traffic data must be erased or made anonymous when no longer needed for transmission; processing for billing requires user consent.',
 'mandatory', 'ePrivacy Articles', 20),
(UUID(), @eprivacy, 'ePD-9',   'Location Data',
 'Location data other than traffic data may only be processed when anonymized or with users'' prior consent; users must be given opt-out right.',
 'mandatory', 'ePrivacy Articles', 30),
(UUID(), @eprivacy, 'ePD-5.3', 'Cookies and Similar Technologies (Consent)',
 'Storage of or access to information on a user''s device requires the user''s prior informed consent (the "cookie law"); legitimate interest exceptions are limited.',
 'mandatory', 'ePrivacy Articles', 40),
(UUID(), @eprivacy, 'ePD-13',  'Unsolicited Communications (Spam)',
 'Unsolicited commercial electronic communications (spam) are prohibited without prior consent; soft opt-in exception applies to existing customers.',
 'mandatory', 'ePrivacy Articles', 50),
(UUID(), @eprivacy, 'ePD-4',   'Security of Services',
 'Providers of publicly available electronic communications services must take appropriate technical and organizational measures to safeguard service security.',
 'mandatory', 'ePrivacy Articles', 60);


-- ═══════════════════════════════════════════════════════════════════════════════
-- APEC CBPR — 9 Principles
-- ═══════════════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO framework_requirements
  (id, framework_id, reference_code, title, description, requirement_type, category, sort_order)
VALUES
(UUID(), @apeccbpr, 'CBPR-1',  'Preventing Harm',
 'Personal information should be used in ways that prevent misuse; organizations should give special consideration to harms that could result from collection and use.',
 'mandatory', 'CBPR Principles', 10),
(UUID(), @apeccbpr, 'CBPR-2',  'Notice',
 'Individuals should be notified about the collection, use, and disclosure of their personal information in clear and easily accessible statements.',
 'mandatory', 'CBPR Principles', 20),
(UUID(), @apeccbpr, 'CBPR-3',  'Collection Limitation',
 'Collection of personal information should be limited to information that is relevant to the purposes of collection and obtained by lawful and fair means.',
 'mandatory', 'CBPR Principles', 30),
(UUID(), @apeccbpr, 'CBPR-4',  'Uses of Personal Information',
 'Personal information collected should be used only to fulfill the purposes of collection and other compatible or related purposes.',
 'mandatory', 'CBPR Principles', 40),
(UUID(), @apeccbpr, 'CBPR-5',  'Choice',
 'Individuals should be given the ability to choose whether and how their personal information may be collected, used, and disclosed.',
 'mandatory', 'CBPR Principles', 50),
(UUID(), @apeccbpr, 'CBPR-6',  'Integrity of Personal Information',
 'Personal information should be accurate, complete, and kept up-to-date to the extent necessary for the purposes of use.',
 'mandatory', 'CBPR Principles', 60),
(UUID(), @apeccbpr, 'CBPR-7',  'Security Safeguards',
 'Appropriate technical, physical, and organizational safeguards should be applied to protect personal information against risks of loss, unauthorized access, or disclosure.',
 'mandatory', 'CBPR Principles', 70),
(UUID(), @apeccbpr, 'CBPR-8',  'Access and Correction',
 'Individuals should be able to obtain access to their personal information from data controllers and to correct inaccurate information.',
 'mandatory', 'CBPR Principles', 80),
(UUID(), @apeccbpr, 'CBPR-9',  'Accountability',
 'Organizations must be accountable for complying with the CBPR privacy principles and provide mechanisms for individuals to address complaints.',
 'mandatory', 'CBPR Principles', 90);
