-- =====================================================
-- MULTI-COMPANY DATABASE MIGRATION
-- Version: 1.0
-- Date: 2026-01-15
-- Backup: pre-multicompany-v1
-- =====================================================

-- =====================================================
-- 1. COMPANY TEMPLATES
-- Pre-defined templates for quick company setup
-- =====================================================
CREATE TABLE IF NOT EXISTS company_templates (
    id SERIAL PRIMARY KEY,
    template_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT REFERENCES users(id)
);

-- =====================================================
-- 2. COMPANY TEMPLATE DOCUMENT HEADS
-- Document heads included in each template
-- =====================================================
CREATE TABLE IF NOT EXISTS company_template_document_heads (
    id SERIAL PRIMARY KEY,
    template_id INT NOT NULL REFERENCES company_templates(id) ON DELETE CASCADE,
    type_id INT NOT NULL REFERENCES sub_type(id) ON DELETE CASCADE,
    UNIQUE(template_id, type_id)
);

CREATE INDEX IF NOT EXISTS idx_ctdh_template ON company_template_document_heads(template_id);
CREATE INDEX IF NOT EXISTS idx_ctdh_type ON company_template_document_heads(type_id);

-- =====================================================
-- 3. COMPANY AUTHORITIES
-- Maps which authorities are available for each company
-- is_enabled = FALSE by default (activation workflow)
-- =====================================================
CREATE TABLE IF NOT EXISTS company_authorities (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    authority_id INT NOT NULL REFERENCES authority(id) ON DELETE CASCADE,
    
    is_enabled BOOLEAN DEFAULT FALSE,
    enabled_at TIMESTAMP,
    enabled_by INT REFERENCES users(id),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(company_id, authority_id)
);

CREATE INDEX IF NOT EXISTS idx_ca_company ON company_authorities(company_id);
CREATE INDEX IF NOT EXISTS idx_ca_authority ON company_authorities(authority_id);
CREATE INDEX IF NOT EXISTS idx_ca_enabled ON company_authorities(company_id, is_enabled);

-- =====================================================
-- 4. COMPANY DOCUMENT HEADS
-- Company-specific document head settings
-- is_enabled = FALSE by default (Admin must activate)
-- =====================================================
CREATE TABLE IF NOT EXISTS company_document_heads (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    type_id INT NOT NULL REFERENCES sub_type(id) ON DELETE CASCADE,
    
    -- Status fields
    is_enabled BOOLEAN DEFAULT FALSE,
    is_reviewed BOOLEAN DEFAULT FALSE,
    
    -- Company-specific overrides
    custom_start_date DATE,
    custom_frequency_start_date VARCHAR(10),
    custom_due_in_same_next_month VARCHAR(1),
    custom_document_order TEXT,
    
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    enabled_at TIMESTAMP,
    enabled_by INT REFERENCES users(id),
    created_by INT REFERENCES users(id),
    
    UNIQUE(company_id, type_id)
);

CREATE INDEX IF NOT EXISTS idx_cdh_company ON company_document_heads(company_id);
CREATE INDEX IF NOT EXISTS idx_cdh_type ON company_document_heads(type_id);
CREATE INDEX IF NOT EXISTS idx_cdh_enabled ON company_document_heads(company_id, is_enabled);

-- =====================================================
-- 5. COMPANY MANDATORY DOCUMENTS
-- Company-specific mandatory document settings
-- =====================================================
CREATE TABLE IF NOT EXISTS company_mandatory_documents (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    type_id INT NOT NULL REFERENCES sub_type(id) ON DELETE CASCADE,
    document_id INT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    
    is_mandatory BOOLEAN DEFAULT FALSE,
    is_enabled BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(company_id, type_id, document_id)
);

CREATE INDEX IF NOT EXISTS idx_cmd_company ON company_mandatory_documents(company_id);
CREATE INDEX IF NOT EXISTS idx_cmd_type ON company_mandatory_documents(type_id);
CREATE INDEX IF NOT EXISTS idx_cmd_document ON company_mandatory_documents(document_id);

-- =====================================================
-- 6. COMPANY DOCUMENT HEAD REMINDERS
-- Company-specific reminder settings
-- =====================================================
CREATE TABLE IF NOT EXISTS company_document_head_reminders (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    type_id INT NOT NULL REFERENCES sub_type(id) ON DELETE CASCADE,
    
    reminder_no INT NOT NULL CHECK (reminder_no BETWEEN 1 AND 5),
    days_before INT NOT NULL,
    reminder_to_user BOOLEAN DEFAULT FALSE,
    reminder_to_admin BOOLEAN DEFAULT FALSE,
    reminder_to_super_admin BOOLEAN DEFAULT FALSE,
    
    is_enabled BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(company_id, type_id, reminder_no)
);

CREATE INDEX IF NOT EXISTS idx_cdhr_company ON company_document_head_reminders(company_id);
CREATE INDEX IF NOT EXISTS idx_cdhr_type ON company_document_head_reminders(type_id);

-- =====================================================
-- 7. COMPANY DOCUMENT GROUPS
-- Company-specific document groupings
-- =====================================================
CREATE TABLE IF NOT EXISTS company_document_groups (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    parent_document_id INT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    child_document_ids TEXT NOT NULL,
    
    is_enabled BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(company_id, parent_document_id)
);

CREATE INDEX IF NOT EXISTS idx_cdg_company ON company_document_groups(company_id);
CREATE INDEX IF NOT EXISTS idx_cdg_parent ON company_document_groups(parent_document_id);

-- =====================================================
-- 8. COMPANY DOCUMENTS
-- Maps which documents are available for each company
-- =====================================================
CREATE TABLE IF NOT EXISTS company_documents (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    document_id INT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    
    is_enabled BOOLEAN DEFAULT FALSE,
    enabled_at TIMESTAMP,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(company_id, document_id)
);

CREATE INDEX IF NOT EXISTS idx_cd_company ON company_documents(company_id);
CREATE INDEX IF NOT EXISTS idx_cd_document ON company_documents(document_id);

-- =====================================================
-- 9. USER COMPANIES
-- Assigns users to specific companies
-- =====================================================
CREATE TABLE IF NOT EXISTS user_companies (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    company_id INT NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, company_id)
);

CREATE INDEX IF NOT EXISTS idx_uc_user ON user_companies(user_id);
CREATE INDEX IF NOT EXISTS idx_uc_company ON user_companies(company_id);

-- =====================================================
-- 10. USER AUTHORITIES
-- Assigns users to specific authorities
-- =====================================================
CREATE TABLE IF NOT EXISTS user_authorities (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    authority_id INT NOT NULL REFERENCES authority(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, authority_id)
);

CREATE INDEX IF NOT EXISTS idx_ua_user ON user_authorities(user_id);
CREATE INDEX IF NOT EXISTS idx_ua_authority ON user_authorities(authority_id);

-- =====================================================
-- 11. USER DOCUMENT TYPES
-- Assigns users to specific document types
-- =====================================================
CREATE TABLE IF NOT EXISTS user_document_types (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type_id INT NOT NULL REFERENCES sub_type(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, type_id)
);

CREATE INDEX IF NOT EXISTS idx_udt_user ON user_document_types(user_id);
CREATE INDEX IF NOT EXISTS idx_udt_type ON user_document_types(type_id);

-- =====================================================
-- 12. ROLE PERMISSIONS
-- Defines what actions each role can perform
-- =====================================================
CREATE TABLE IF NOT EXISTS role_permissions (
    id SERIAL PRIMARY KEY,
    role_id INT NOT NULL REFERENCES role(id) ON DELETE CASCADE,
    permission_key VARCHAR(100) NOT NULL,
    is_allowed BOOLEAN DEFAULT FALSE,
    UNIQUE(role_id, permission_key)
);

CREATE INDEX IF NOT EXISTS idx_rp_role ON role_permissions(role_id);
CREATE INDEX IF NOT EXISTS idx_rp_key ON role_permissions(permission_key);

