-- Create favourites table for DMS Dashboard
-- Run this SQL in your PostgreSQL database (dmstype)

CREATE TABLE IF NOT EXISTS favourites (
    id SERIAL PRIMARY KEY,
    upload_id INTEGER NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_favourites_upload 
        FOREIGN KEY (upload_id) 
        REFERENCES uploaded_documents(id) 
        ON DELETE CASCADE,
    CONSTRAINT unique_user_upload 
        UNIQUE (upload_id, user_id)
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_favourites_user_id ON favourites(user_id);
CREATE INDEX IF NOT EXISTS idx_favourites_upload_id ON favourites(upload_id);

-- Add comment for documentation
COMMENT ON TABLE favourites IS 'Stores user favourite documents for quick access on dashboard';

