-- ============================================================================
-- REMINDER SYSTEM TABLES
-- Run this SQL in your PostgreSQL database (dmstype)
-- ============================================================================

-- ============================================================================
-- 1. REMINDER LOGS TABLE
-- Tracks all reminder notifications sent/pending/failed
-- ============================================================================
CREATE TABLE IF NOT EXISTS reminder_logs (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES company(id) ON DELETE CASCADE,
    type_id INTEGER NOT NULL REFERENCES sub_type(id) ON DELETE CASCADE,
    authority_id INTEGER,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    
    -- Reminder details
    reminder_type VARCHAR(20) NOT NULL, -- 'email' or 'whatsapp'
    recipient_email VARCHAR(255),
    recipient_phone VARCHAR(50),
    recipient_name VARCHAR(255),
    recipient_role VARCHAR(50), -- 'user', 'admin', 'super_admin'
    
    -- Document details
    document_month INTEGER NOT NULL,
    document_year INTEGER NOT NULL,
    due_date DATE NOT NULL,
    days_before_due INTEGER NOT NULL, -- 30, 15, 7, 3, 2, 0, -1 (after due)
    
    -- Status tracking
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'sent', 'failed'
    error_message TEXT,
    sent_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Prevent duplicate reminders
    UNIQUE(company_id, type_id, user_id, document_month, document_year, days_before_due, reminder_type)
);

CREATE INDEX IF NOT EXISTS idx_reminder_logs_company ON reminder_logs(company_id);
CREATE INDEX IF NOT EXISTS idx_reminder_logs_type ON reminder_logs(type_id);
CREATE INDEX IF NOT EXISTS idx_reminder_logs_status ON reminder_logs(status);
CREATE INDEX IF NOT EXISTS idx_reminder_logs_due_date ON reminder_logs(due_date);
CREATE INDEX IF NOT EXISTS idx_reminder_logs_created_at ON reminder_logs(created_at);

COMMENT ON TABLE reminder_logs IS 'Tracks all reminder notifications for document uploads';

-- NOTE: Using existing 'mobile_number' column in users table for WhatsApp
-- No additional phone number tables or columns needed

-- ============================================================================
-- 5. MODIFY FAVOURITES TABLE FOR MONTH-LEVEL FAVOURITES
-- ============================================================================
DO $$
BEGIN
    -- Add favourite_type column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'favourites' AND column_name = 'favourite_type') THEN
        ALTER TABLE favourites ADD COLUMN favourite_type VARCHAR(20) DEFAULT 'individual';
    END IF;
    
    -- Add company_id column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'favourites' AND column_name = 'company_id') THEN
        ALTER TABLE favourites ADD COLUMN company_id INTEGER REFERENCES company(id) ON DELETE CASCADE;
    END IF;
    
    -- Add authority_id column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'favourites' AND column_name = 'authority_id') THEN
        ALTER TABLE favourites ADD COLUMN authority_id INTEGER;
    END IF;
    
    -- Add type_id column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'favourites' AND column_name = 'type_id') THEN
        ALTER TABLE favourites ADD COLUMN type_id INTEGER REFERENCES sub_type(id) ON DELETE CASCADE;
    END IF;
    
    -- Add document_year column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'favourites' AND column_name = 'document_year') THEN
        ALTER TABLE favourites ADD COLUMN document_year INTEGER;
    END IF;
    
    -- Add document_month column
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'favourites' AND column_name = 'document_month') THEN
        ALTER TABLE favourites ADD COLUMN document_month INTEGER;
    END IF;
    
    -- Make upload_id nullable for month-level favourites
    ALTER TABLE favourites ALTER COLUMN upload_id DROP NOT NULL;
END $$;

-- Create index for month-level favourites
CREATE INDEX IF NOT EXISTS idx_favourites_month_level ON favourites(user_id, company_id, type_id, document_year, document_month);
CREATE INDEX IF NOT EXISTS idx_favourites_type ON favourites(favourite_type);

-- Update existing favourites to have 'individual' type
UPDATE favourites SET favourite_type = 'individual' WHERE favourite_type IS NULL;

-- ============================================================================
-- END OF MIGRATION SCRIPT
-- ============================================================================

