-- Professional Database Schema Update for Go Legal
-- Adds service categorization, shareholder management, and verification checkpoints

-- 1. Update services table with categorization
ALTER TABLE services ADD COLUMN service_type TEXT CHECK(service_type IN ('business_name','company','ngo','tax','scuml','trademark','post_incorporation','package','plan'));
ALTER TABLE services ADD COLUMN category TEXT DEFAULT 'service';
ALTER TABLE services ADD COLUMN is_package BOOLEAN DEFAULT 0;
ALTER TABLE services ADD COLUMN description TEXT;

-- 2. Create shareholders/directors table
CREATE TABLE IF NOT EXISTS shareholders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    full_name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    nationality TEXT,
    id_type TEXT,
    id_number TEXT,
    share_percentage REAL DEFAULT 0,
    is_director BOOLEAN DEFAULT 0,
    is_trustee BOOLEAN DEFAULT 0,
    date_of_birth TEXT,
    residential_address TEXT,
    passport_path TEXT,
    id_document_path TEXT,
    created_at TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
);

-- 3. Create verification checkpoints table
CREATE TABLE IF NOT EXISTS verification_checkpoints (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    checkpoint_type TEXT NOT NULL CHECK(checkpoint_type IN ('name_screening','document_verification','share_structure','compliance_check','risk_assessment')),
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','passed','failed','requires_attention')),
    notes TEXT,
    verified_by INTEGER,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE),
    FOREIGN KEY(verified_by) REFERENCES users(id)
);

-- 4. Create internal notes table
CREATE TABLE IF NOT EXISTS internal_notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    admin_id INTEGER NOT NULL,
    note_type TEXT NOT NULL CHECK(note_type IN ('verification','risk','compliance','client_communication')),
    content TEXT NOT NULL,
    is_internal BOOLEAN DEFAULT 1,
    created_at TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE),
    FOREIGN KEY(admin_id) REFERENCES users(id)
);

-- 5. Update applications table
ALTER TABLE applications ADD COLUMN service_category TEXT;
ALTER TABLE applications ADD COLUMN priority_level TEXT DEFAULT 'normal' CHECK(priority_level IN ('low','normal','high','urgent'));
ALTER TABLE applications ADD COLUMN risk_score INTEGER DEFAULT 0;
ALTER TABLE applications ADD COLUMN internal_status TEXT DEFAULT 'new' CHECK(internal_status IN ('new','under_review','verified','flagged','approved_internal'));

-- 6. Create compliance tracking table
CREATE TABLE IF NOT EXISTS compliance_tracking (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    compliance_type TEXT NOT NULL,
    due_date TEXT,
    completed_date TEXT,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','completed','overdue','not_applicable')),
    notes TEXT,
    created_at TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
);

-- 7. Indexes for performance
CREATE INDEX IF NOT EXISTS idx_shareholders_application ON shareholders(application_id);
CREATE INDEX IF NOT EXISTS idx_verification_application ON verification_checkpoints(application_id);
CREATE INDEX IF NOT EXISTS idx_notes_application ON internal_notes(application_id);
CREATE INDEX IF NOT EXISTS idx_compliance_application ON compliance_tracking(application_id);
CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status, internal_status);
